PHP Set up the date order and fetch the data

Question

I am working on my PHP to fetch the dates from the mysql database. I want to set up the date in the correct order that start from today date to the previous date then fetch the data I want.

Here is the PHP:

 try {
    $stmt = $link->prepare("SELECT * FROM inbox LIMIT 10");
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

    foreach($stmt->fetchAll() as $k=>$v) {
        $received_date = $v['received_date'];
        echo $received_date;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

It give me the output like this:

Fri, 31 Aug 2018 14:52:16 -0600
Mon, 13 Aug 2018 00:29:38 +0000 (UTC)
Mon, 13 Aug 2018 01:28:25 +0100
Mon, 3 Sep 2018 16:46:06 -0600
Mon, 3 Sep 2018 17:50:33 -0600
Fri, 7 Sep 2018 16:47:46 -0600
Fri, 7 Sep 2018 13:41:08 -0600
Tue, 4 Sep 2018 15:11:51 -0600
Tue, 11 Sep 2018 02:30:57 +0100
Tue, 11 Sep 2018 02:30:22 +0100

Here is what I want to achieve:

Tue, 11 Sep 2018 02:30:57 +0100
Tue, 11 Sep 2018 02:30:22 +0100
Fri, 7 Sep 2018 16:47:46 -0600
Fri, 7 Sep 2018 13:41:08 -0600
Tue, 4 Sep 2018 15:11:51 -0600
Mon, 3 Sep 2018 17:50:33 -0600
Mon, 3 Sep 2018 16:46:06 -0600
Fri, 31 Aug 2018 14:52:16 -0600
Mon, 13 Aug 2018 01:28:25 +0100
Mon, 13 Aug 2018 00:29:38 +0000 (UTC)

I have got no idea how I could set up the date in the correct order that start from today date then to the previous date and fetch the data I want. The column received_date I am using are a text so I have stored them as a string in the database.

I want to set up the date in the correct order as I want to fetch the data from the today date to the previous date so I could output them in my page where I could use it to check for my emails.

Can you please show me an example how I could set up the date in the correct order that start today date to the previous dates that I could fetch the data at the same time?

EDIT: When I tried this:

$stmt = $link->prepare("SELECT * FROM inbox ORDER BY received_date DESC LIMIT 10");

It will give me this:

Wed, 31 Oct 2018 11:14:38 -0400
Wed, 29 Aug 2018 16:44:17 -0400
Wed, 27 Mar 2019 21:37:05 +0000
Wed, 27 Mar 2019 03:37:05 +0000
Wed, 26 Sep 2018 18:45:40 -0400
Wed, 24 Oct 2018 23:11:11 -0400
Wed, 24 Apr 2019 19:43:06 +0100
Wed, 24 Apr 2019 01:43:07 +0100
Wed, 22 Aug 2018 16:11:48 -0400
Wed, 19 Sep 2018 19:35:45 -0400

It should be:

Wed, 24 Apr 2019 19:43:06 +0100
Wed, 24 Apr 2019 01:43:07 +0100    
Wed, 27 Mar 2019 21:37:05 +0000
Wed, 27 Mar 2019 03:37:05 +0000
Wed, 31 Oct 2018 11:14:38 -0400
Wed, 24 Oct 2018 23:11:11 -0400
Wed, 26 Sep 2018 18:45:40 -0400
Wed, 19 Sep 2018 19:35:45 -0400
Wed, 29 Aug 2018 16:44:17 -0400
Wed, 22 Aug 2018 16:11:48 -0400

Here is what I stored the strings in the database:

Wed, 31 Oct 2018 11:14:38 -0400
Wed, 29 Aug 2018 16:44:17 -0400
Wed, 27 Mar 2019 21:37:05 +0000
Wed, 27 Mar 2019 03:37:05 +0000
Wed, 26 Sep 2018 18:45:40 -0400
Wed, 24 Oct 2018 23:11:11 -0400
Wed, 24 Apr 2019 19:43:06 +0100
Wed, 24 Apr 2019 01:43:07 +0100
Wed, 22 Aug 2018 16:11:48 -0400
Wed, 19 Sep 2018 19:35:45 -0400

[`ORDER BY`](https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html) — Script47, Sep 17 '19 at 15:43
@NigelRen yes, sort of but the date don't show what I want to achieve as it will show like `Wed, 31 Oct 2018 11:14:38 -0400 Wed, 29 Aug 2018 16:44:17 -0400` when it should be `Wed, 24 Apr 2019 19:43:06 +0100 Wed, 24 Apr 2019 01:43:07 +0100`. — Robert Jones, Sep 17 '19 at 15:44
@Script47 I have tried it but it will give me the date that are not in the correct order. Please see my updated question — Robert Jones, Sep 17 '19 at 15:47
Have a look here: [strtotime()](https://stackoverflow.com/questions/6401714/php-order-array-by-date) — eraenderer, Sep 17 '19 at 15:47
You're going to want to store your dates as dates in mysql so you can utilize `order by` as others are suggesting. You can do it after the fact but the result won't be correct unless you query all records instead of 10. — cOle2, Sep 17 '19 at 15:48
@cOle2 It didn't make any difference because when I stored the strings in the database and when I use `DESC` without the limit, the date show `Wed, 31 Oct 2018 11:14:38 -0400 Wed, 29 Aug 2018 16:44:17 -0400 Wed, 27 Mar 2019 21:37:05 +0000` which it is not in the correct order. Any idea? — Robert Jones, Sep 17 '19 at 15:51
@cOle2 Do I need to change from the strings to dates to store the dates in the database? — Robert Jones, Sep 17 '19 at 15:52
What @cOle2 meant is to store them as `DATETIME` in your database, not `TEXT` or `VARCHAR`, I think — Pepper, Sep 17 '19 at 15:52
Save dates as a unix timestamp (which only consists of numbers), order by the timestamp in descending order, then when printing it out, convert the unix timestamp to an actual date *after* ordering. That way, it will truly descend by date rather than messing with manually ordering with consideration to days/months/years — pattyd, Sep 17 '19 at 15:53
@pattyd why `unix`? `DATETIME` will be sufficient, if not better. OP, there is also `STR_TO_DATE`. — Script47, Sep 17 '19 at 15:54
@RobertJones Yes I think you should change from string to dates in database. If the timzeone is important to keep you will probably want that in a separate column. — cOle2, Sep 17 '19 at 15:54
@cOle2 I have changed from strings to datetime in a database but I have trouble with storing the dates in a database when I am using `$inbox_dates = new DateTime($inbox_date); $inbox_dates = $inbox_dates->format('d-m-Y H:i:s');` — Robert Jones, Sep 17 '19 at 16:01
@cOle2 So how do you store the dates in the datetime column as it is showing `0000-00-00 00:00:00`? — Robert Jones, Sep 17 '19 at 16:02
@RobertJones Best to do a new question, how to convert date strings to dates in mysql (or along those lines). — cOle2, Sep 17 '19 at 16:03

Matt Korostoff · Accepted Answer · 2019-09-17T16:47:59.627

It seems to me the basic problem of this question is that you need to construct a mySQL query ordering by date, but you have two problems: 1) your dates are stored in an exotic format, not real mySQL datetime objects and 2) your dates are not timezone normalized. Luckily, mysql supplies native functions to solve both problems:

You're timezone indicators are particularly troublesome because they are in the format +HHMM as opposed to the global standard +HH:MM (with a colon delimiting hours from minutes. So we'll have to use a combination of SUBSTRING() and CONCAT() to convert them into a usable format.

I agree with the other commenters in this thread who suggested that this problem would be easier if this data were stored in a more meaningful data/time format, but I also know sometimes you get stuck with sloppy data, and learning how to clean/manipulate imperfect data sets is a part of programming. With that in mind, I propose the following algorithm:

Split the date string into two chunks: the last 5 characters (timezone) and everything else (datetime).
Split the timezone into two additional chunks, delimited by a colon between character 3 and 4, so +1200 becomes +12:00.
Pass the datetime chunk through the STR_TO_DATE function, mapping each portion to its appropriate token. The format here seems to be:

weekday_abbrev, 2_digit_month month_abrev 4_digit_year 24_base_2_digit_hour:2_digit_minute:2_digit_second

converted to STR_TO_DATE tokens this format is:

'%a, %d %b %Y %H:%s:%i'

Now we have a real datetime object and a meaningful timezone indicator. We'll pass both of these to CONVERT_TZ to get the final result.
Now that we have a real datetime object normalized for timezone, order by that field, descending.

Putting it all together, as a mySQL query, this looks like:

select 
    CONVERT_TZ(
        STR_TO_DATE(
            received_date, 
            '%a, %d %b %Y %H:%s:%i'
        ),
        '+00:00',
        CONCAT(SUBSTRING(received_date, -5, 3), ':', SUBSTRING(received_date, -2, 2))
    ) as dateTime
from inbox 
order by dateTime desc
limit 10

Raw data:

Query result:

score -2 · Answer 2 · answered Sep 17 '19 at 15:58

-2

Create mysql tables in this way (UTC time, and sort with this value):

CREATE TABLE test_timestamp (
  t1  TIMESTAMP
);
INSERT INTO test_timestamp(t1) VALUES('2019-01-01 23:11:01');

http://www.mysqltutorial.org/mysql-timestamp.aspx

answered Sep 17 '19 at 15:58

AnyHill

10
1

PHP Set up the date order and fetch the data

2 Answers2