PDO select and show data array with same date

I've made this code. To show the data based on same date (day)

        $group = array();
        $stmt = $conn->prepare("
            SELECT *,date(date_added) as dateadded
            FROM rr
            ORDER BY dateadded DESC
        ");
        $stmt->execute();
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        while ($res = $stmt->fetch()){
            $rtype = array();
                $rtype['type'] = "date";
                $rtype['date'] = date_format (new DateTime($res['dateadded']), 'M jS, Y');
                $rtype[] = $res;
            $result[] = $rtype;
        }

What I got as result from query sql

array
    (
        [0] => Array
            (
                [type] => date
                [date] => Dec 18th, 2019
                [0] => Array
                    (
                        [id] => 1
                        [user_id] => 1
                        [status] => 1
                        [date_added] => 2019-12-18 13:44:30
                        [dateadded] => 2019-12-18
                    )

            )

        [1] => Array
            (
                [type] => date
                [date] => Dec 20th, 2019
                [0] => Array
                    (
                        [id] => 3
                        [user_id] => 2
                        [status] => 2
                        [date_added] => 2019-12-20 14:53:04
                        [dateadded] => 2019-12-20
                    )

            )

        [2] => Array
            (
                [type] => date
                [date] => Dec 29th, 2019
                [0] => Array
                    (
                        [id] => 5
                        [user_id] => 3
                        [status] => 0
                        [date_added] => 2019-12-29 00:39:21
                        [dateadded] => 2019-12-29
                    )

            )

    )

There is some data that is not show as result in the same date.

What I want to show is like this.

Array
(
    [0] => Array
        (
            [type] => date
            [date] => Dec 18th, 2019
        )

    [1] => Array
        (
            [id] => 2
            [user_id] => 1
            [status] => 1
            [date_added] => 2019-12-18 13:44:30
        )

    [2] => Array
        (
            [type] => date
            [date] => Dec 20th, 2019
        )

    [3] => Array
        (
            [id] => 1
            [user_id] => 1
            [status] => 0
            [date_added] => 2019-12-20 14:53:04
        )

    [4] => Array
        (
            [id] => 3
            [user_id] => 1
            [status] => 0
            [date_added] => 2019-12-20 14:53:04
        )

)

The result is become an array like that with group by date (day).

How to get this result? Thanks for helping me.

1 answer

  • answered 2020-01-14 02:15 Barmar

    You need to set $group_date to the value of $res['dateadded'] so you can tell when the date changes. There's no need to use substr(), since dateadded just contains the date without a time.

    You should push $res onto $result, not $rtype.

    $group_date = "";
    $group = array();
    $stmt = $conn->prepare("
                SELECT *,date(date_added) as dateadded
                FROM rr
                ORDER BY dateadded 
            ");
    $stmt->execute();
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    while ($res = $stmt->fetch()){
        $rtype = array();
        if ($group_date !== $res["dateadded"]) {
            $rtype['type'] = "date";
            $rtype['date'] = date_format (new DateTime($res['dateadded']), 'M jS, Y');
            $result[] = $rtype;
            $group_date = $res["dateadded"];
        }
        $result[] = $res;
    }