How does hph + mysql sort the time by the current time? The library contains the following content: + ----- + ------- + ----------------- + | gid | text | time | + ----- + ------- + --------------------- + | 1 | content 1 | 2012-03-0914: 30: 28 | how does hph + mysql sort the time by the current time?
The library contains the following content:
+ ----- + ------- + --------------------- +
| Gid | text | time |
+ ----- + ------- + --------------------- +
| 1 | content 1 | 14:30:28 |
| 2 | content 2 | 13:40:36 |
| 3 | content 3 | 14:50:43 |
| 4 | content 4 | 09:10:15 |
| 5 | content 5 | 15:21:22 |
| 6 | content 6 | 15:25:07 |
| 7 | content 7 | 16:21:43 |
| 8 | content 8 | 14:21:13 |
| 9 | content 9 | 12:21:55 |
+ ----- + ------- + --------------------- +
Assume that the current time is 14:10:05, which is the current Internet time.
I want to sort and display it in this order
The top is the closest to the current time. if the insertion order at the same time is
The next step is more than the current time, that is, the time that is not yet reached is also in the insert order
At the bottom of the page, the time is smaller than the current time, but the time is arranged in a small order.
+ ----- + ------- + --------------------- +
| Gid | text | time |
+ ----- + ------- + --------------------- +
| 8 | content 8 | 14:21:13 |
| 1 | content 1 | 14:30:28 |
| 3 | content 3 | 14:50:43 |
| 5 | content 5 | 15:21:22 |
| 6 | content 6 | 15:25:07 |
| 7 | content 7 | 16:21:43 |
| 2 | content 2 | 13:40:36 |
| 9 | content 9 | 12:21:55 |
| 4 | content 4 | 09:10:15 |
+ ----- + ------- + --------------------- +
How to write $ result = mysql_query () in PHP ()
------ Solution --------------------
Actually
Set @ tt = '2017-03-09 14:10:05 ';
SELECT * FROM 'Times 'order by t> @ tt desc, if (t> @ tt, [email protected], @ tt-t)
Yes.
Although if (t> @ tt, [email protected], @ tt-t) is equivalent to abs ([email protected]), abs ([email protected]) is used. and cannot obtain the correct results. It can be seen that the datetime type cannot be directly involved in mathematical operations.
Ying writing
Set @ tt = '2017-03-09 14:10:05 ';
SELECT * FROM 'Times 'order by t> @ tt desc, abs (UNIX_TIMESTAMP (t)-UNIX_TIMESTAMP (@ tt ))
For your application
$ Tt = date ('Y-m-d H: I: s ');
$ Result = mysql_query ("SELECT * FROM mysf order by kfsj> '$ TT' desc, abs (UNIX_TIMESTAMP (kfsj)-UNIX_TIMESTAMP (' $ TT '))");
Time constants must be enclosed in quotation marks.
If $ tt always takes the current time value, $ tt can be replaced by now ().