php

mysql

date

row

sql-order-by

Display MySQL results by date

Viewed 58 times

I have this mysql Table:

+--------------------+---------+-------+
|      date          | query   | count |
|--------------------+---------+-------|
|2012-11-18 09:52:00 | Michael |   1   |
|2012-11-18 10:47:10 |  Tom    |   2   |
|2012-11-17 15:02:12 |  John   |   1   |
|2012-11-17 22:52:10 |  Erik   |   3   |
|2012-11-16 09:42:01 |  Larry  |   1   |
|2012-11-16 07:41:33 |  Kate   |   1   |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

and so on. I can simply take results and order them by date in one row via this code:

$queries = mysql_query("SELECT * FROM my_tables ORDER BY date DESC LIMIT 20"); 
while($row = mysql_fetch_array($queries)){
    echo "Name ".$row['query']."";
}

But how to display elements from table ordered by specific date like this:

In 2012-11-18:
Michael
Tom

In 2012-11-17:
John
Erik

In 2012-11-16:
Larry
Kate

and so on. Thanks!

Answers

Here is teh PHP code:

$query = mysql_query("SELECT date, query FROM table6 ORDER BY date DESC LIMIT 20");
$group_date = null;
while ($row = mysql_fetch_assoc($query)) {
    if ($group_date !== substr($row["date"], 0, 10)) {
        $group_date = substr($row["date"], 0, 10);
        echo "<h1>$group_date</h1>n";
    }
    echo "${row['query']}<br>n";
}

Output:

2012-11-18

Tom

Michael

2012-11-17

Erik

John

2012-11-16

Larry

Kate

Note that while this code "groups" rows by one column, it can easily be extended to group rows by multiple columns. Left as an exercise.

Tuesday, December 13, 2022

jjeeper-a6f2bb6b7be5

(Explaining the loss of ORDER BY)

The SQL standard essentially says that a subquery is an unordered set of rows. This implies that the Optimizer is free to ignore the ORDER BY in the 'derived' table: FROM ( SELECT ... ORDER BY ). In "recent" versions of MySQL and MariaDB, such ORDER BYs are being dropped. There are other cases where ORDER BY is ignored.

In some situations (not sure about this one), adding a LIMIT 99999999 (big number) after the ORDER BY tricks the Optimizer into doing the ORDER BY. However, it is still free to ignore the "order" later.

A general rule for MySQL: Avoid subqueries. (There are cases where subqueries are faster, but not yours.)

A strong rule: You must have an ORDER BY on the outermost if you want the results to be sorted.

If you had added LIMIT 3 to the derived table in your first query, you would get only CHARLES, DAVID, JAMES, but not necessarily in that order. That is, you would need two ORDER BYs - one in the derived table, one at the very end.

Wednesday, October 5, 2022

jasbner

You'd need to have a table that contains all the dates you're going to query over, and do something along the lines of...

SELECT DATE(D.`thedate`), COUNT(`id`)
FROM `datetable` D
LEFT JOIN `blogs` B
ON B.`posted` = D.`thedate`
WHERE `status` = 'active'     
&& D.`thedate` BETWEEN `2011-01-01` AND `2011-05-01`     
GROUP BY DATE(D.`thedate`)

Wednesday, August 31, 2022

naymesh_mistry

I doubled your given sample data to show how it works with multiple userIds.

See it working live in an sqlfiddle here.

select
userid, groupnum,
min(ping) as start_date,
max(ping) as end_date,
max(ping) - min(ping) as duration
from (
select
*,
@groupnum := if(@prevUser != userId, @groupnum + 1, @groupnum),
@groupnum := if(ping - @prevTS > 60, @groupnum + 1, @groupnum) as groupnum,
@prevUser := userid,
@prevTS := ping
from
Table1 t
, (select @groupnum:=1, @prevTS:=NULL, @prevUser:=NULL) vars
order by userid, ping
) sq
group by userid, groupnum

Friday, December 23, 2022

antonin_gavrel

You can use a different alias for the formatted column:

SELECT to_char(foo,'dd/MM/yyyy') as formatted_foo, 
       bar 
FROM baz 
ORDER BY foo;

As an alternative if you need to keep the foo alias:

select foo,
       bar
from (
  SELECT to_char(foo,'dd/MM/yyyy') as foo,
         foo as foo_date 
         bar 
  FROM baz 
) t
order by foo_date

Wednesday, December 14, 2022

nonagon

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

date

row

sql-order-by

Display MySQL results by date

Answers

2012-11-18

2012-11-17

2012-11-16

php,mysql,date,row,sql-order-by