mysql query show only 1 result
i want to output the result based on todays date. the problem is, the output only show 1 result?
database report table:
id | r_amount | id_therapist | date | time | t_tanning | t_deep
// this query works fine echoing all the result if i use while loop
$today = date('Y-m-d');
(1) $q = $db->query("SELECT * FROM report WHERE date='$today' ORDER BY date ASC")
// this query only show 1 output result?
(2) $q = $db->query("SELECT *, SUM(IF(t_tanning LIKE 'Pro Tan%', r_amount, 0)) AS totalProTan FROM report WHERE date='$today' ORDER BY date ASC")
while($r = $q->fetch_array(MYSQLI_ASSOC)) :
// (1) echoing all result from database
echo $r['r_amou开发者_运维技巧nt'].'<br>';
// (2) echoing only 1 result????
echo $r['totalProTan'].'<br>';
endwhile;
If the date
field is of type datetime
, you'll have to do something like
SELECT ... WHERE DATE(date
)=CURDATE()
Notice that I'm using curdate()
in the query. There's no need to generate the date value in PHP. MySQL is perfectly capable of doing that itself.
Try adding a GROUP BY
statement to the second SQL statement.
- you should group by the key of the elemnts you want to be shown in the end result
The use of the aggregate function SUM will result in a single result. You are asking the database to get all the rows then sum up a value and give you the value.
To see the result for many groups of values you have to add a group by clause.
精彩评论