CODEWITHSUNDEEP
phpmysqlsqlgroup-byrows
Seb
Seb

Reputation: 525

How to group rows with the same value from mySQL into php?

So im trying to sort the different rows per day and just output some "testdate" info of the different unique rows, but the problem i have is that only the first id get the correct outputted "testdate" not all the others. Please look at my example of what i have now and what i'm looking for.

This is my table with the name "1702":

╔════╦══════════╦═════════════════════╗
║ id ║ testdate ║     datecreated     ║
╠════╬══════════╬═════════════════════╣
║ 1  ║   1702   ║ 2019-02-16 14:48:28 ║
║ 2  ║   1702   ║ 2019-02-17 14:48:58 ║
║ 8  ║   1802   ║ 2019-02-16 14:50:07 ║
║ 4  ║   1702   ║ 2019-02-17 14:48:51 ║
║ 7  ║   1802   ║ 2019-02-17 14:50:34 ║
║ 6  ║   1702   ║ 2019-02-17 14:48:54 ║
║ 9  ║   1802   ║ 2019-02-16 14:50:09 ║
║ 10 ║   1802   ║ 2019-02-17 14:50:12 ║
║ 11 ║   1602   ║ 2019-02-14 14:50:55 ║
║ 12 ║   1602   ║ 2019-02-14 14:51:11 ║
╚════╩══════════╩═════════════════════╝

My current result is this:

2019-02-17
2,4,7,6,10 1702

2019-02-16
1,8,9 1702

2019-02-14
11,12 1602

But i want it to be like this:

2019-02-17
2 1702
4 1702
7 1802
6 1702
10 1802

2019-02-16
1 1702
8 1802
9 1802

2019-02-14
11 1602
12 1602

This is the code i have:

$sql = 'SELECT  DATE(datecreated), GROUP_CONCAT(id) as grouped_name, testdate FROM `1702` GROUP BY DATE(datecreated) DESC';
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['DATE(datecreated)'].'<br>';
    echo '
        <div class="box-bettype">
            <p class="box-bettype-text">'. $row['grouped_name']. ' ' . $row ['testdate'] . '</p>
        </div>
        ';
}

Upvotes: 0

Views: 38

Answers (1)

Andrii Filenko
Andrii Filenko

Reputation: 984

Try this:

$sql = 'SELECT DATE(datecreated) as datecreated, id as grouped_name, testdate FROM `1702` ORDER BY DATE(datecreated) DESC';
$result = mysqli_query($conn,$sql);
$prevDate = null;
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['datecreated'] !== $prevDate ? $row['datecreated'].'<br>' : '';
    $prevDate = $row['datecreated'];
    echo '
        <div class="box-bettype">
            <p class="box-bettype-text">'. $row['grouped_name']. ' ' . $row ['testdate'] . '</p>
        </div>
        ';
}

I removed GROUP_CONCAT and just comparing current date with previous date.

Upvotes: 1

Related Questions