multi-column query -> but how to display? - Webmaster Forums - Webmaster forum for HTML, PHP, ASP, CSS and more

TylerNZ · 03-13-2007, 11:16 PM

Hi ... brand new newbie here ... I have a mysql query:

Code:

SELECT regions.regionID, regions.name AS state, categories.categoryID, categories.name FROM regions, categories WHERE regions.regionID=categories.regionID

Which returns the values I want:

+++++++++++++++++++++++++++++++++++++++
| regions | state | categoryID | name |
----------------------------------------------------
| NA | National | 1 | Some Category |
| NA | National | 2 | Another Cat. |
| WA | Washing.| 3 | Blahblah |
| WA | Washing.| 7 | Hmmmmm |
| LA | Lou. | 8 | Lahdedah |
---------------------------------------------------

Ok, so NOW I'm confused on how I would display this ... I started with:

Code:

$regionName = $row['state'];
		while($row = mysql_fetch_array($query)){ 
			do{
			$code.='<li>'.$regionName.'</li>
			 <ul>';
			 $regionName=$row['state'];
			 } while ($regionName!=$row['state'])
			
			$code.='<li><a href="./members/?cat='.$row['categoryID'].'&region='.$row['regionID'].'">'.$row['name'].'</a></li>';
		}

Obviously that isn't finished ... but can you see what I'm trying to accomplish?
[HTML]<ul>
<li>National</li>
<ul>
<li>Some Category</li>
<li>Another Cat.</li>
</ul>
<li>Washing.</li>
<ul>
<li>Blahblah</li>
<li>Hmmmmm</li>
</ul>
<li>Lou.</li>
<ul>
<li>Ladedah</li>
</ul>
</ul>[/HTML]

Can anyone help me???

TylerNZ · 03-14-2007, 08:18 AM

Ok, I think I solved it ... altho I bet this isn't the best coding in the world!!! Still open to some criticing:

PHP Code:


		
			
$i=0;

        $previousRegion="";

        while($row = mysql_fetch_array($query)){ 

            $regionName = $row['state']; 

            $i++;

            

            if($regionName!=$previousRegion){

                if($i>1){

                    $code.="\n\t</ul>";

                }

                $code.="<li><strong>".$regionName."</strong></li>\n\t<ul>";

            }

            

            $code.='<li><a href="./members/?cat='.$row['categoryID'].'&region='.$row['regionID'].'">'.$row['name'].'</a></li>'."\n\t";

            

            $previousRegion = $regionName;

        }

        $code.="\n\t</ul>";