There is a table where posts are assigned a creation date in unix for example (I will not write in unix, sorry)

id name create_at 1 one 1462885556 (09.04.2016 13-00) 2 two 1462971956 (10.05.2016 13-00) 3 three 1462881956 (09.04.2016 12-00) 4 four 1462968356 (10.05.2016 12-00) 5 five 1462878356 (09.04.2016 11-00) 6 six 1462964756 (10.05.2016 11-00)

need to get so

 10.05.2016 2 two (10.05.2016) 3 four (10.05.2016) 6 six (10.05.2016) 09.04.2016 1 one (09.04.2016) 3 three (09.04.2016) 5 five (09.04.2016)

or php or mysql. What is the problem, how to choose so that it would turn out this way, my last post with the date of unix corresponds to 05.10.2016 13-00 then select all posts from 05/10/2015 the next day with posts from 04/09/2016. need to do the same.

  • order by create_at desc, id asc . You won't get such a conclusion from mysql, but the blank will be good - splash58
  • @ splash58 I explained again what the problem is - Sergalas
  • What is the type of field in the database? - splash58
  • Well, I got everything from the last day and the first post, And then? I need to understand what day and select all the posts of this day - Sergalas
  • What is the field type in the database? - splash58

2 answers 2

It would be more correct, of course, to make the field create_at of type datetime. But since it is an int, you can use the from_unixtime function as follows:

 select id, name, date(from_unixtime(create_at)) create_at from t order by create_at desc, id asc

demo

    if there is UNIX_TIMESTAMP, then

     $result = $mysqli->query("SELECT FLOOR(create_at/60/60/24) as created, id, name FROM table"); $store = array(); while($row = $result->fetch_assoc()){ $store[$row['created']] = array("id"=>$row['id'],"name"=>$row['name']); }