我遵循数据库结构。
id email lat long point balance date
1 33 1.00 2.00 0 empty date
2 34 8.00 3.00 -1 empty date
3 33 7.00 4.00 2 empty date
4 33 6.00 5.00 0 empty date
5 33 6.33 5.43 -1 empty date
所以我想显示所有记录哪个电子邮件ID是33,但它必须在每一行中显示余额。
In first row it's balance is 0
second row it's balance is 2
third row it's balance is 2
four row it's balance is 1
所以我的PHP代码看起来像这样,但无法获得正确的平衡:
echo "<table width='100%' cellpadding='5' cellspacing='0' border='1'>";
echo "<tr>";
echo "<td class='tdhead' valign='top' width='100'><b>Date</b></td>";
echo "<td class='tdhead' valign='top' width='100'><b>Lattitude</b></td>";
echo "<td class='tdhead' valign='top' width='50'><b>Longitude</b>
</td>";
echo "<td class='tdhead' valign='top' width='50'><b>Point</b>
</td>";
echo "<td class='tdhead' valign='top' width='50'><b>Balance</b>
</td>";
echo "</tr>";
while($res = mysql_fetch_array($park_history))
{
$lat = $res['lat'];
$long = $res['long'];
$point = $res['point'];
$date = $res['date'];
$balance = 0;
$sum = mysql_query("SELECT SUM(point) AS points FROM balance WHERE email =
'".$_SESSION['SESS_ID']."'");
$sum_res = mysql_fetch_array($sum);
$sum = $sum_res['points'];
echo "<tr>";
echo "<td class='tdhead2' valign='top'>$date</td>";
echo "<td class='tdhead2' valign='top'>$lat</td>";
echo "<td class='tdhead2' valign='top'>$long</td>";
echo "<td class='tdhead2' valign='top'>$point</td>";
echo "<td class='tdhead2'
valign='top'>$sum</td>";
echo "</tr>";
}
我相信可以使用mysql和函数来完成。你能给我解决方案或建议吗?谢谢。
MySQL sum
函数不会做你想要它做的事情 - 但它不必这样做 - 有一种更简单的方法可以使用你已经获取的结果来完成你的任务。
由于您已经拥有正在操作的行的$point
,因此只需将其添加到计数器并从那里继续。实际上,您每行都在进行多余的数据库调用。
用:
$sum = 0;
while ( $res = mysql_fetch_array($park_history) ) {
/* yada yada */
$point = $res['point'];
$sum += $point;
echo /* your table here */
}
您可以完全删除这些行:
$sum = mysql_query( ... );
$sum_res = mysql_fetch_array($sum);
$sum = $sum_res['points'];
$total
将按照您的描述保存运行点数,并且不会每次循环都查询您的数据库。
这是 php :
$park_history = mysql_query("
SELECT *
FROM balance
WHERE email ='".$_SESSION['SESS_ID']."'");
$i = 0;
$balance = 0; // the first sum of balance will 0 + first point
while($res = mysql_fetch_array($park_history))
{
$i++;
$lat = $res['lat'];
$long = $res['long'];
$point = $res['point'];
$date = $res['date'];
$balance= $balance + $point; // balance will be updated in every loops
echo "<tr>";
echo "<td class='tdhead2' valign='top'>$date</td>";
echo "<td class='tdhead2' valign='top'>$lat</td>";
echo "<td class='tdhead2' valign='top'>$long</td>";
echo "<td class='tdhead2' valign='top'>$point</td>";
echo "<td class='tdhead2' valign='top'>$balance</td>";
echo "</tr>";
}
我删除了这个代码:
$sum = mysql_query("SELECT SUM(point) AS points FROM balance WHERE email =
'".$_SESSION['SESS_ID']."'");
$sum_res = mysql_fetch_array($sum);
$sum = $sum_res['points'];
并重新定义$park_history
:
$park_history = mysql_query("
SELECT *
FROM balance
WHERE email ='".$_SESSION['SESS_ID']."'");
看看吧!如果你发现一些错误,告诉我(评论)......也许它对:D有所帮助