tricky question...mysql?

[deep]

Hello 🙂

Im trying to create a report but im stuck 😃...
The report needs to display impressions and clicks between two dates (Y-m-d).

My MySQL table will have several rows like this:

id : date : impression : click
1 2004-05-21 500 10
2 2004-05-23 502 12
3 2004-05-23 503 10
4 2004-05-29 504 14

Now, in my report I dont want to display 2004-05-23 twice, only once! And its impressions and clicks need to be added.
My current code does not solve the problem where I only want to display 2004-05-23 once 🙁 (dont know how to do that) and the SQL basically needs to look like it does below with the date BETWEEN '$fromDate' AND '$toDate' or the whole script that I have written will not work...

$report_query = mysql_query("SELECT * FROM report WHERE id='$id' AND date BETWEEN '$fromDate' AND '$toDate'");

while($report_r = mysql_fetch_array($report_query)){
     $report_imp[] = $report_r['rep_imp'];
     $report_click[] = $report_r['rep_clicks'];
}

My code will add together the impressions and clicks by putting them in a list and then adding them in a for loop but this makes it very difficult with the dates...

Plz, help!

Thanks!

[Weedpacket]

SELECT sum(rep_int) AS total_impressions, date FROM report WHERE id='$id' AND date BETWEEN '$fromDate' AND '$toDate'" GROUP BY date

[deep]

cool, what does that do?

[deep]

oh you mean sum(rep_imp)? And will total_impressions become a variable?

[Weedpacket]

Originally posted by deep
oh you mean sum(rep_imp)? And will total_impressions become a variable?

Yes, and no (it becomes a virtual table column).