dongwei3336
2013-10-26 05:14
浏览 677
已采纳

Mysql sum函数结果显示在每一行

I've following db structure.

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

so i want to show all record which email id is 33 but it's must be show the balance in every row for example.

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 

so my php code is look like this but can't get correct balance:

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>";
}

I believe it can be done using mysql sum function. Can you plz give me solutions or suggestions. Thank You.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douyan6742 2013-10-26 05:37
    已采纳

    The MySQL sum function won't do what you want it to - but it doesn't have to - there's a much easier way to acomplish your task using the result you already fetched.

    Since you already have the $point for the row you're operating on, simply add that to a counter and proceed from there. As it is, you're making a superfluous db call every row.

    Use:

    $sum = 0;
    
    while ( $res = mysql_fetch_array($park_history) ) {
    
        /* yada yada */
        $point =  $res['point'];
        $sum += $point;
    
        echo /* your table here */
    
    }
    

    You can completely drop the lines:

    $sum = mysql_query( ... );
    $sum_res = mysql_fetch_array($sum);
    $sum = $sum_res['points'];
    

    $total will hold the running points tally as you described and won't query your db every loop.

    已采纳该答案
    打赏 评论
  • duanguanye4124 2013-10-26 05:41

    Here's the 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>";
    }
    

    I remove this code :

    $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'];
    

    and redefine $park_history :

    $park_history = mysql_query("
    SELECT * 
    FROM balance 
    WHERE email ='".$_SESSION['SESS_ID']."'");
    

    just check it out! and tell me (comment) if you find some errors.. may it help :D

    打赏 评论

相关推荐 更多相似问题