Group query result rows by one column and present rows as HTML unordered lists in respective divs

Question

My query outputs this in phpMyAdmin:

Code:

<?php
if ($stmt = $mysqli->prepare("SELECT service_names.id, service_names.name as service, 
service_titles.name as title, service_titles.id as service_title_id FROM `service_names` INNER 
JOIN service_titles ON title_id = service_titles.id WHERE service_titles.user_id = ? AND 
service_titles.slide_id = ?")) {

$stmt->bind_param('ii', $user_id, $slide_id);
$stmt->execute();
$stmt->bind_result($service_id, $service_name, $service_title, $service_title_id);
$stmt->store_result();

$result_array = array();

while ($stmt->fetch()) {
    if (!isset($result_array[$service_title])){
        $result_array[$service_title] = array();
    }
    $result_array[$service_title][] = array('service_name'=>$service_name,'service_id'=>$service_id);
}

$html = "";
foreach($result_array as $key => $value){
    $html .= "
    <div class=\"list\">
        <h3 class=\"secondary\"><span id={ GOES HERE }>$key</span></h3>
            <ul>";
        foreach($result_array[$key] as $service){
            $html .= "<li><span id=\"".$service['service_id']."\">".$service['service_name']."</span></li>\n"; 
        }
    $html .= "</ul></div>";
}

echo $html;
$stmt->close();
}

?>

What this code is producing is:

What I need is to grab the service_title_id and place it in the code:

<h3 class=\"secondary\"><span id={ GOES HERE }>$key</span></h3>

I am using a jQuery inline edit script, and it needs a unique ID assigned to it. I have the value in a variable $service_title_id it's just a matter of integrating it into the array, which I am getting a little confused with all the different arrays and values.

Answer 1

The first way, that requires minimum changes in your script, is to add service_title_id to every service and get it from first element of an array:

while ($stmt->fetch()) {
    if (!isset($result_array[$service_title])){
        $result_array[$service_title] = array();
    }
    $result_array[$service_title][] = array(
        'service_name'=>$service_name,
        'service_id'=>$service_id,
        'service_title_id'=>$service_title_id
    );
}

<h3 class=\"secondary\"><span id={$result_array[$key][0]['service_title_id']}>$key</span></h3>

other and cleaner way will be to change structure of array holding all services to look like this:

while ($stmt->fetch()) {
    if (!isset($result_array[$service_title])){
        $result_array[$service_title_id] = array(
            'service_title' => $service_title
            'services' => array(),
        );
    }
    $result_array[$service_title_id]['services'][] = array(
        'service_name'=>$service_name,
        'service_id'=>$service_id
    );
}


$html = "";
foreach($result_array as $service_title_id => $service_details){
    $html .= "
    <div class=\"list\">
        <h3 class=\"secondary\"><span id=\"{$service_title_id}\">{$service_details['title']}</span></h3>
            <ul>";
        foreach($service_details['services'] as $service){
            $html .= "<li><span id=\"{$service['service_id']}\">{$service['service_name']}</span></li>\n"; 
        }
    $html .= "</ul></div>";
}

Answer 2

i think the commenters about having unique html id's are on to something, but here's some improved PHP for you. I renamed your variables to make things clearer

$html = "";
foreach($result_array as $service_title => $services){
    $html .= "
    <div class=\"list\">
        <h3 class=\"secondary\"><span id={$services[0]['service_id']}>$service_title</span></h3>
            <ul>";
        foreach($services as $service){
            $html .= "<li><span id=\"".$service['service_id']."\">".$service['service_name']."</span></li>\n"; 
        }
    $html .= "</ul></div>";
}

Answer 3

Quick and dirty but will get the job done:

<?php
if ($stmt = $mysqli->prepare("SELECT service_names.id, service_names.name as service, 
service_titles.name as title, service_titles.id as service_title_id FROM `service_names` INNER 
JOIN service_titles ON title_id = service_titles.id WHERE service_titles.user_id = ? AND 
service_titles.slide_id = ?")) {

$stmt->bind_param('ii', $user_id, $slide_id);
$stmt->execute();
$stmt->bind_result($service_id, $service_name, $service_title, $service_title_id);
$stmt->store_result();

$result_array = array();

while ($stmt->fetch()) {
    if (!isset($result_array[$service_title])){
        $result_array[$service_title] = array();
    }
    $result_array[$service_title . ':' . $service_id][] = $service_name;
}

$html = "";
foreach($result_array as $key => $value){
        list ($title, $id) = explode(':', $key);
    $html .= "
    <div class=\"list\">
        <h3 class=\"secondary\"><span id=\"$id\">$title</span></h3>
            <ul>";
        foreach($value as $service){
            $html .= "<li><span>$service</span></li>\n"; 
        }
    $html .= "</ul></div>";
}

echo $html;

Answer 4

Change the loop to this:

foreach($result_array as $key => $value){
    $html .= "
    <div class=\"list\">
        <h3 class=\"secondary\"><span id=\"service_title_id_{$value[0]['service_title_id']}\">$key</span></h3>
            <ul>";
        foreach($value as $service){
            $html .= "<li><span id=\"".$service['service_id']."\">".$service['service_name']."</span></li>\n"; 
        }
    $html .= "</ul></div>";
}

Answer 5

Change this line to:

 $result_array[$service_title][] = array('service_name'=>$service_name,'service_id'=>$service_id, 'service_title_id' => $service_title_id);

And this line to:

<h3 class=\"secondary\"><span id=\"".$."\">$result_array[$key]['service_title_id']</span></h3>

It's a little bit redundant, but basically you are putting the service_title_id in the second dimension of the array and using it from there.

Answer 6

Build your grouped array from your query results by pushing whole rows into a new array using the title (or title_id) as the first level key.

$sql = <<<SQL
SELECT service_titles.id   AS title_id,
       service_titles.name AS title,
       service_names.id    AS service_id,
       service_names.name  AS service,
FROM service_names
JOIN service_titles ON title_id = service_titles.id
WHERE service_titles.user_id = ?
      AND service_titles.slide_id = ?
SQL;
$resultSet = $mysqli->execute_query($sql, [$user_id, $slide_id]);
$grouped = [];
foreach ($resultSet as $row) {
    $grouped[$row['title']][] = $row;
}

Then traverse the populated array. An empty group will not exist, so you can safely access [0] in the first row of a group to access the title_id value.

foreach ($grouped as $title => $rows) {
    ?>
    <div class="list">
        <h3 class="secondary">
            <span id="<?php echo $rows[0]['title_id']; ?>">
                <?php echo $title; ?>
            </span>
        </h3>
        <ul>
            <?php
            foreach ($rows as $row) {
                printf(
                    '<li><span id="%d">%s</span></li>' . "\n\t\t\t",
                    $row['service_id'],
                    $row['service']
                ); 
            }
            ?>
        </ul>
    </div>
    <?php
}