Geek Answers Handbook

GROUP BY mysql shows only one row. But they want a repetition

My table structure

id order_id location time date 1 951 x 2/03/2013 2 951 x 13:00 2/03/2013 3 951 y 3/03/2013

I want to show them in php with a group by date something like

 Date 2/03/2013 Orderid 951 location x Orderid 951 location x Date 3/03/2013 Orderid 951 location y

Mysql query execution

 select * from table where order_id=951 GROUP BY date ORDER BY id ASC

But this query returns only the first row of the same data. How can I show all repetitions in a category by date. how to use it in php to display data, taking the date as a separate variable.

I am editing the question so that you know that the date can be anything, I just want the order identifier to be displayed by the date in the group, including repetition.

+4
source share
5 answers

If you want to show all the rows in a table, ORDER BY date, id better, then do the grouping in PHP.

 $result = mysql_query("select * from table ORDER BY data ASC,id ASC"); $last_date = ""; while(($row=mysql_fetch_assoc($result))) { if($last_date != $row['date']) { echo "Date " . $row['date'] . "\n"; } echo "Orderid ". $row['order_id']." location " . $row['location']. "\n"; }
+1
source

Use the STR_TO_DATE() function to sort by date.

 $result = mysql_query("select * from table ORDER BY STR_TO_DATE(date, '%d/%m/%Y') ASC, id ASC"); $last_date = ""; while(($row=mysql_fetch_assoc($result))) { if($last_date != $row['date']) { echo "Date " . $row['date'] . "\n"; } echo "Orderid ". $row['order_id']." location " . $row['location']. "\n"; }
+1
source 
 select * from table where order_id=951 ORDER BY date DESC, id ASC

And then manipulate with PHP

Order By will not return rows within the grouping. If you really wanted to use sql, then you are trying GROUP_CONCAT, which returns the grouped row values ​​as a concatenated string and then shares it with PHP.

+1
source

Since your table had a time column, perhaps you should consider inside your query, by default ascending, so you can skip asc for the record.

 select * from table where order_id=951 order by date,time,id;

You can create a global php variable to hold the current read date, compare each time in the while loop, and if it is different from the global, print something to separate from the last group.

+1
source

Another solution is to use GROUP_CONCAT :

select date,group_concat("Location " , location, "\n") from table where order_id=951 GROUP BY date ORDER BY id ASC

0
source

More articles:


All Articles