Grouping result's output? PHP, SQL

gisvpn
gisvpn used Ask the Experts™
on
Hi All,

I have a simple list of results extracted and outputted to the page like this:

$sql_qry_tsk = "SELECT * FROM main WHERE tsk_meeting='$qry_e24' ORDER BY tsk_date DESC ;";
$result_qry_tsk = $conn_show_actions->query($sql_qry);


    if ($result_qry_tsk ->num_rows > 0) {

      while($row = $result_qry_tsk->fetch_assoc()) {

        $qry_tsk_nme = $row["tsk_nme"];
				$qry_tsk_date = $row["tsk_date"];
       
        
        echo "$qry_tsk_date - $qry_tsk_nme<br>";
        }
        
        } else {
        
        echo "No results.";
        
        }

Open in new window



I would like to output these results but group by the tsk_date so for example if I had these results for tsk_date instead of outputting them in the list (as below, as they currently do):

2018/08/25 - a result's name
2018/08/25 - a result's name
2018/08/25 - a result's name
2018/08/24 - a result's name
2018/08/24 - a result's name
2018/08/23 - a result's name
2018/08/23 - a result's name
2018/08/23 - a result's name
2018/08/20 - a result's name
2018/08/20 - a result's name

I instead would like:

Saturday - 8/25
2018/08/25 - a result's name
2018/08/25 - a result's name
2018/08/25 - a result's name

Friday - 8/24
2018/08/24 - a result's name
2018/08/24 - a result's name

Thursday - 8/23
2018/08/23 - a result's name
2018/08/23 - a result's name
2018/08/23 - a result's name

Monday - 8/20
2018/08/20 - a result's name
2018/08/20 - a result's name

How would this be approached?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you try the code below? I would better structure it inside a table, for better visibility.

<table>
      <thead>
        <tr><th>Date</th> <th>Result Name</th></tr>
      </thead>
      <tbody>
<?php
$sql_qry_tsk = "SELECT * FROM main WHERE tsk_meeting='$qry_e24' ORDER BY tsk_date DESC ;";
$result_qry_tsk = $conn_show_actions->query($sql_qry);


    if ($result_qry_tsk ->num_rows > 0) {
        $currentDate = false;
                while($row2 = $result_qry_tsk->fetch_assoc()) {
                    $i++;
                    if ($row2['tsk_date'] != $currentDate){
        ?>
       <tr>
          <td colspan='2'> <?php echo $row2['tsk_date']; ?></td></tr>

        <?php $currentDate = $row2['date'];
            }
        ?>
        <tr>
            <td><?php echo $row2['tsk_name']; ?> </td><td> <?php echo $row2['tsk_date']; ?></td>
            
    
        <?php
              }
            } else { ?>
        <tr>
        <td colspan="2"><?php echo "No results."; ?></td></tr>
        
        } ?>
              </tbody>
</table>
 

Open in new window

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
There are a few ways you can do what you need. If you choose to use the PDO libraries for your DB  work, it has a built in method for doing it. Something like this:

$query = $db->query("SELECT tsk_date, tsk_nme FROM main WHERE tsk_meeting='$qry_e24' ORDER BY tsk_date DESC");
$records = $query->fetchAll(PDO::FETCH_GROUP);

foreach ($records as $date => $values):
    echo $date;

    foreach ($values as $value):
        echo $value->tsk_nme;
    endforeach;  
endforeach;

Open in new window

If you want to stick with mysqli, I would suggest you loop through your records and build a grouped array. Something like this:

$query = $db->query("SELECT tsk_date, tsk_nme FROM main WHERE tsk_meeting='$qry_e24' ORDER BY tsk_date DESC");

while ($record = $query->fetch_object()):
    $records[$record->tsk_date][] = $record;
endwhile;

Open in new window

Now you've built a grouped array, you can loop through in a similar fashion to the PDO way:

foreach ($records as $date => $values):
    echo $date;

    foreach ($values as $value):
        echo $value->tsk_nme;
    endforeach;  
endforeach;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial