[SOLVED] While loop issue

[phpdragon]

I am getting data from the following 2 tables users and countries

 

relevant from users

country = country code

state = state user is in

status = current user status

stockist = set as a stockist

then required user details

 

relevant from countries

ccode = country code

name = country name

 

I want to output in the following way

 

COUNTRY NAME(if user exist in country)

    STATENAME(if user exist in state)

        relevant users

    STATENAME(if user exist in state)

        relevant users

    STATENAME(if user exist in state)

        relevant users

COUNTRY NAME(if user exist in country)

    STATENAME(if user exist in state)

        relevant users

    STATENAME(if user exist in state)

        relevant users

    STATENAME(if user exist in state)

        relevant users

 

etc etc where the countries and states and users all only show if relevant usres exist in those locations

 

here is my current code which is a bit dodgey

 

<?php $qrysql=mysql_query("SELECT * FROM users WHERE status='active' && stockist='yes' ORDER BY country ASC");
if (mysql_num_rows($qrysql)>0) {
	while ($cres=mysql_fetch_assoc($qrysql)) {
	$identifier=$cres['country'];
	$countsql=mysql_query("SELECT * FROM countries WHERE ccode='$identifier'");
	$rowpick=mysql_fetch_array($countsql);
	$country=$rowpick['name'];
	echo "<tr><td class='termshead' height='30' valign='middle' colspan='2' width='375px'><b><font size='+1'>$country</font></b></td></tr>";
		$qry=mysql_query("SELECT * FROM users WHERE status='active' && stockist='yes' ORDER BY country");
	while ($sres=mysql_fetch_assoc($qry)) {
		if ($sres['state']!="") {echo "<tr><td class='presstext' colspan='2' width='375px'><b><font size='3px'>".$sres['state']."</font></b></td></tr>";
		}
	$usersql=mysql_query("SELECT * FROM users WHERE state='".$sres['state']."' && status='active' && stockist='yes' ORDER BY pcode");
			while ($reseller=mysql_fetch_assoc($usersql)) {
				if ($reseller['biz']!="") { echo "<tr><td class='presstext' width='225px'><ul><li><font size='2px'><b>".$reseller['biz']."</b></font></li>
				<li>".$reseller['street']."</li>
				<li>".$reseller['suburb']."   ".$reseller['pcode']."</li>
				<li>".$reseller['phone']."</li></ul></td>
				<td valign='middle' align='center' width='150px'>";
				if (!empty($reseller['logo'])) { echo "<img src='biz_logos/".$reseller['logo']."'></td></tr>"; } else { echo " </td></tr>"; }
				} 
			}
		}
	}
} 
?>

 

and this is the result im getting

 

http://www.kiikfashion.com.au/stockist.php

 

where i get this wierd repeating loop, could I have some help rewriting this to be more efficient and work porperly tku

 

[phpdragon]

I neglected to use GROUP BY and it was returning multiple entries all fixed now works a treat

 

<?php $qrysql=mysql_query("SELECT * FROM users WHERE status='active' && stockist='yes' GROUP BY country ORDER BY country");
	while ($cres=mysql_fetch_assoc($qrysql)) {
	$identifier=$cres['country'];
	$countsql=mysql_query("SELECT * FROM countries WHERE ccode='$identifier'");
		$rowpick=mysql_fetch_assoc($countsql);
		$country=$rowpick['name'];
		echo "<tr><td class='termshead' height='40' valign='middle' colspan='2' width='375px'><b><font size='+1'>$country</font></b></td></tr>";
	$qry=mysql_query("SELECT * FROM users WHERE status='active' && stockist='yes' && country='$identifier' GROUP BY state ORDER BY state");
		while ($sres=mysql_fetch_assoc($qry)) {
		if ($sres['state']!="") {echo "<tr><td class='presstext' colspan='2' valign='middle' height='20px' width='375px'><b><font size='3px'>".$sres['state']."</font></b></td></tr>";
		}
	$usersql=mysql_query("SELECT * FROM users WHERE state='".$sres['state']."' && status='active' && stockist='yes' ORDER BY pcode");
		while ($reseller=mysql_fetch_assoc($usersql)) {
			if ($reseller['biz']!="") { echo "<tr><td class='presstext' width='225px'><ul><li><font size='2px'><b>".$reseller['biz']."</b></font></li>
				<li>".$reseller['street']."</li>
				<li>".$reseller['suburb']."   ".$reseller['pcode']."</li>
				<li>".$reseller['phone']."</li></ul></td>
				<td valign='middle' align='center' width='150px'>";
			if (!empty($reseller['logo'])) { echo "<img src='biz_logos/".$reseller['logo']."'></td></tr>";
			} else { echo " </td></tr>"; }
			} 
		}
		}
		}
?>