Member Avatar for fiona.mcburney

Hi there. I'm trying to wrap my brain around something that is probably really simple - once you know how.
I want to print the name of each park, only once, along with a list of its features.

I have the following SQL query...

$query = 'SELECT id, name, feat_name
FROM allparks
LEFT JOIN prkcombine ON allparks.id = prkcombine.pk_id
LEFT JOIN prkfeature ON prkcombine.pkfeat_id = prkfeature.pkfeat_id
WHERE allparks.id =$id';

This yields the following result:

id  name        feat_name
2   Municiple Park  Cricket pitch
2   Municiple Park  Cycle/walking track
2   Municiple Park  Picnic benches

How do I using this SQL result to print something like this:

        <h3>Municiple Park</h3>
        <ul>
            <li>Cricket pitch</li>
            <li>Cycle/walking track</li>
            <li>Picnic benches</li>
        </ul>

?

Tyr this :

$result1 = mysql_query("SELECT id, name  FROM  allparks");

   while ($row1 = mysql_fetch_array($result1)) {
       echo '<h3>'.$row1['name'].'</h3><ul>';
       $result2 = mysql_query("SELECT feat_name
                                FROM allparks
                                LEFT JOIN prkcombine ON allparks.id = prkcombine.pk_id
                                LEFT JOIN prkfeature ON prkcombine.pkfeat_id = prkfeature.pkfeat_id
                                WHERE allparks.id =".$row1['id']);

         while ($row2 = mysql_fetch_array($result2)) { 
            echo '<li>'.$row2['feat_name'].'</li>';
         }

         echo '</ul>';
   }
Member Avatar for diafol

Maybe something like this - so you only run the ONE query. Not tested.

$query = 'SELECT id, name, feat_name FROM allparks LEFT JOIN prkcombine ON allparks.id = prkcombine.pk_id LEFT JOIN prkfeature ON prkcombine.pkfeat_id = prkfeature.pkfeat_id WHERE allparks.id =$id';

$name = '';
$output = '';
$q = mysql_query($query);
if(mysql_num_rows($q)){
    while($data = mysql_fetch_assoc($q)){
        if($name != $data['name']){
            if($output != '')$output .= "\n</ul>";
            $output .= "\n<h3>{$data['name']}</h3>\n<ul>";
        }
        $output .= "\n\t<li>{$data['feat_name']}</li>";
        $name = $data['name'];
    }
    $output .= "\n</ul>";
}

//...
echo $output;

Hi, @diafole .Good one.

Only One suggestion

I think, checking with Id is better than checking with name ...

Name Can be repeate ... Is it ?

Member Avatar for diafol

Good point - from the example, difficult to know if names are repeated - so yes - check against ID. +1

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.