Bravis
Bravis

Reputation: 91

How can i group by date output from sql?

How can i group by date all values ?

My sql table

id |date        | price | description 
1  | 2019-01-01 | 30      price 1  
2  | 2019-01-04 | 50      price 2
3  | 2019-01-01 | 60      price 3
4  | 2019-01-04 | 20      price 4
5  | 2019-01-01 | 10      price 5

My query :

$result = mysql_query("SELECT * FROM newcosts  ORDER BY id DESC ");

If i will do GROUP BY date , I will loose all my prices and descriptions.

while( $row = mysql_fetch_array($result)){
    echo $row['date']."<br>";
    echo $row['price']."<br>";
    echo $row['description']."<br>";
}

My output : https://ibb.co/zN7zRTR.

How can i do something like this ?

2019-01-01
price 1      30
price 3      60

2019-01-04
price 2      50
price 4      20

Upvotes: 0

Views: 53

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I think you just need to sort by the date. Something like this should work:

$query = "SELECT * FROM newcosts ORDER BY date";
$date = NULL;

if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $curr_date = $row['date'];
        if ($date != $curr_date) {
            $date = $curr_date;
            echo $date . "<br>";
        }
        echo $row['description']. "  " . $row['price'] . "<br>";
    }
}

The idea behind the above presentation logic is that we only print the date once, per group of dates. But for each record, we always print a description and price.

Note: You are mysql_query which is a deprecated API.

Upvotes: 1

Related Questions