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

enter image description here

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!

Ben D · Accepted Answer · 2011-12-19 21:03:13Z

4

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.

answered Dec 19, 2011 at 21:03

Ben D

14.6k3 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Drew Over a year ago

Hey Ben, sorry to come back to this but just a quick question. How can I modify this to include the $service_id. each service in the <li> has an ID associated to it, and I am using a jQuery inline edit script so I need a unique ID to each one. so I have 3 variables $stmt->bind_result($service_id, $service_name, $service_title); just need to figure out where to insert the service_id

Drew Over a year ago

needs to go in the array somewhere, if I just put it in the $html .= "<li id=\"".$service_id."\">$service</li>"; then it shows "12" for all of them which is the last ID of the list.

Drew Over a year ago

same for the service_title_id so I can have a delete button near <h3 class=\"secondary\"><span>$key</span></h3>

Ben D Over a year ago

Does the unique ID have to be the same as the mysql id for your jquery function? I'd recommend just setting an itterator ($i=0) at the top of your outermost foreach loop and then $i++ after each list item generated. HOWEVER, html element IDs cannot start with a number, so you'd need to modify it to: <li id=\"list_item_".$i."\">$service</li>. If you absolutely need the ids to match the mysql ids, you'll want to modify the line that creates the inital array: $result_array[$r['title']][] = array('title'=>$r['service'],'id'=>$r['id']); and then use $service['id'] and $service['title']

Drew Over a year ago

Thanks for your response. But yeah it needs the ids to match the mysql ids.

|

jeroen · Accepted Answer · 2011-12-19 20:56:30Z

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

answered Dec 19, 2011 at 20:56

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Comments

Pradeep · Accepted Answer · 2011-12-19 20:58:28Z

1

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"];
    }
}

answered Dec 19, 2011 at 20:58

Pradeep

1,2641 gold badge22 silver badges41 bronze badges

1 Comment

Pradeep Over a year ago

Didn't added div tag, but that can be easily added.

Collectives™ on Stack Overflow

PHP / MySQL Displaying List of Results

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related