CODEWITHSUNDEEP
phpmysql
Omerimuni
Omerimuni

Reputation: 65

MySQL/PHP GROUP BY day

I have query that groups all records by day. 

$query = mysql_query("SELECT DATE_FORMAT(date, '%d/%m/%Y') AS day, COUNT(id) AS total FROM invoices GROUP BY day");

How i do that it shows me all days, even if there is no records, until today

Omerimuni

Upvotes: 3

Views: 839

Answers (4)

Omerimuni
Omerimuni

Reputation: 65

Maybe this is not best solution, but i did it myself :D

$result = mysql_query("SELECT date_format(date, '%Y-%m-%d') AS date FROM invoices ORDER BY id ASC LIMIT 1");
    $first = mysql_fetch_array($result);

    $year = date('Y', strtotime($first['date']));
    $month = date('m', strtotime($first['date']));
    $day =  date('d', strtotime($first['date']));


    for ($i = strtotime($year.'-'.$month.'-'.$day); $i < strtotime('NOW'); $i=$i+24*60*60){
        $r = date('d/m/Y',$i);
        $res = mysql_query("SELECT DATE_FORMAT(date, '%d/%m/%Y') AS day, COUNT(id) AS total FROM invoices WHERE DATE_FORMAT(date, '%d/%m/%Y') LIKE '%$r%' GROUP BY day");
        $arr = mysql_fetch_array($res);

        if(mysql_num_rows($res)){
            echo "".$arr['day']."&nbsp;=>&nbsp;".$arr['total']."<br>";
        }else{
            echo date('d/m/Y',$i)."=>&nbsp; 0<br>";
        }
    }

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116200

I think it is best to just get all the records in the table, ordered by date and fill in the gaps using PHP.

It is difficult to generate a sequence of dates in MySQL. You often see solutions with stored procedures and temp tables, like here.

In PHP, create a loop that goes through each date until today. In the loop you can check for each record if a date exists in the results of the query. If not, you can just render an empty row or whatever you want.

This check is not so expensive, because you sort the results in the query by date as well, so you will only need to check if the current record has the same date as the current date in the loop. If the date of the record is greater, you should not process this record yet. If the date is the same, process the record and fetch the next record.

Upvotes: 1

beetree
beetree

Reputation: 941

In principle you could create a new table containing all days. Then you left join this table into "invoices" on days.

However, my suggestion for you is to instead handle this logic in PHP.

Upvotes: 0

varela
varela

Reputation: 1331

There is no way to do this, unless you will have table with days. Than you can left join on it

Upvotes: 0

Related Questions