PHP / MySQL Displaying List of Results

Question

I need some help displaying my results in PHP. Below is what displays for my query:

SELECT service_names.id, service_names.name as service, service_titles.name 
as title,  user_id  FROM `service_names` INNER JOIN service_titles ON title_id 
= service_titles.id

The way the design is, I need to display the results like so:

<div class="list">
    <h3 class="secondary">Maintenance</h3>
        <ul>
            <li>Wiper Blades</li>
            <li>Air Filter</li>
            <li>Cabin Filter</li>
            <li>Fuel Filter</li>
            <li>Cooling System</li>
            <li>Brake System</li>
            <li>Fuel System</li>
            <li>Trans Fluid Exchange</li>
        </ul>
</div>
<div class="list">
    <h3 class="secondary">Tire Services</h3>
       <ul>
            <li>Tire Rotation</li>
            <li>Flat Repair</li>
            <li>Wheel Balance</li>
            <li>Wheel Alignment</li>
        </ul>
</div>

Here is my thread that I posted earlier which explains my database design and shows an image of what it needs to look like. So basically I have the database design in place but now I am not too sure how to create the query to loop through all the results and keep it in that HTML format posted above.

If anyone could help me I would really appreciate it.

Thanks!

Answer 1

I think something like this ought to do the trick, where you create an array for each 'title' which contains all the 'service' elements under that title... then you just loop through each of the 'title' arrays to generate the bullets:

$query=mysql_query('',[DB_CONNECTION]);
if (!$query){
    //do something  
}
if (!mysql_num_rows($query)){
    //do something for no results   
}

$result_array = array();

while($r=mysql_fetch_array($query)){
    if (!isset($result_array[$r['title']])){
        $result_array[$r['title']] = array();
    }
    $result_array[$r['title']][] = $r['service'];
}

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

echo $html;

This is structured so it will work for as many "title" types as you have. (Obviously you'd need to put in the sql and connection in the mysql_query() at the top.

Answer 2

Personally I would design the database differently and put the title in a separate table, but with what you have now, you can easily sort by title and keep track of the current one. On a change of title, you need to start a new list.

Something like (psuedo code):

sql:

SELECT service_names.id, service_names.name as service, service_titles.name as title,  user_id  FROM `service_names` INNER JOIN service_titles ON title_id = service_titles.id ORDER BY `service_names`.`name`

php

$old_title = '';
$title = '';
// start list
foreach ($row = mysql....)
{
  $title = $row['title'];
  if ($title != $old_title)
  {
    // end list
    // start list
    $old_title = $title;
  }
  // add list item
}
// end list

Answer 3

This can help.

You just need to store old category to compare with new one. (Here is is assummed you have ordered records according to category which can easily be done by ORDER BY)

$oldCat ="";
while($newRow = mysql_fetch_row()){
    if($oldCat!=$newRow["title"]) {
        echo '<h3 class="secondary">'. $newRow["title"] . '</h3>';
        echo "<ul>";
    }
    echo '<li>'.$newRow["service"].'</li>';

    if($oldCat!=$newRow["title"]) {
        echo "</ul>";
        $oldCat= $newRow["title"];
    }
}