mysql求和以flot图表输出

Question

Am正在尝试输出flot/jquery图表上两个表条目与日期的运行总数。

到目前为止，代码可以在给定日期输出两个值的折线图，但我试图在同一图表上显示每列的运行总数。不确定是否可以使用单个mysql查询使用SUM（值）来实现这一点？？-就是不能让它发挥作用。

<?php
$query=("SELECT hours, credits, entryDate FROM entry WHERE uid='1'");
$result = mysql_query($query);        
while($row = mysql_fetch_assoc($result))
{
    $credits[] = array ( strtotime ($row['entryDate'])*1000,($row['credits']));
    $hours[] = array ( strtotime ($row['entryDate'])*1000,($row['hours']));
} ?>
</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>

Answer 1

最简单的方法是在php中保持一个运行的总数，并为每个日期发布：-

<?php
$query=("SELECT hours, credits, entryDate FROM entry WHERE uid='1' ORDER BY entryDate");
$result = mysql_query($query);        
while($row = mysql_fetch_assoc($result))
{
    $runningtotalcredits += $row['credits'];
    $runningtotalhours += $row['hours'];
    $credits[] = array ( strtotime ($row['entryDate'])*1000,$row['credits']);
    $creditssofar[] = array ( strtotime ($row['entryDate'])*1000,$runningtotalcredits);
    $hours[] = array ( strtotime ($row['entryDate'])*1000,$row['hours']);
    $hourssofar[] = array ( strtotime ($row['entryDate'])*1000,$runningtotalhours);
} ?>
</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>
var creditssofar = <?php echo json_encode($creditssofar);?>
var hourssofar = <?php echo json_encode($hourssofar);?>

在SQL中使用相关的子查询也应该是可能的，但效率可能不高：-

<?php
$query=("SELECT hours, 
                credits, 
                entryDate, 
                (SELECT SUM(hours) AS hourssofar FROM entry b WHERE uid='1' AND b.entryDate <= a.entryDate),
                (SELECT SUM(credits) AS creditssofar FROM entry c WHERE uid='1' AND c.entryDate <= a.entryDate)
        FROM entry a
        WHERE uid='1' 
        ORDER BY entryDate");
$result = mysql_query($query);        
while($row = mysql_fetch_assoc($result))
{
    $credits[] = array ( strtotime ($row['entryDate'])*1000,$row['credits']);
    $creditssofar[] = array ( strtotime ($row['entryDate'])*1000,$row['creditssofar']);
    $hours[] = array ( strtotime ($row['entryDate'])*1000,$row['hours']);
    $hourssofar[] = array ( strtotime ($row['entryDate'])*1000,$row['hourssofar']);
} ?>
</script>
var credits = <?php echo json_encode($credits);?>
var hours = <?php echo json_encode($hours);?>
var creditssofar = <?php echo json_encode($creditssofar);?>
var hourssofar = <?php echo json_encode($hourssofar);?>

（均未测试）