Difficult Problem

[freakonaleash]

I have written the following to display dealers from a specific area, I now have to add advanced functionality, this is where i am very confused...

// select the information
$sql2 = "SELECT id, DEALER, ADDRESS2, ADDRESS3, CITY, COUNTY, ZIP, PHONE, EMAIL, WEB FROM trek WHERE County = '$place'";
$result9 = mysql_db_query ($DBName, $sql2, $Link);
// count total rows
$tot = mysql_num_rows($result9);

// define amount of table divisions before new row is started
$tds = 3;
echo "<br><table width=\"100%\" bgcolor=\"#336699\">";
echo "<tr>";
for ($i = 1; $i <= $tot; $i++) {
$row9 = mysql_fetch_array ($result9);
$id = $row9[0];
if ($row9[8] == "") {
$emailcheck = "";
} else {
$emailcheck = "<a href=\"mailto:$row9[8]\">Email Dealer</a>";
}
if ($row9[9] == "") {
$urlcheck = "";
} else {
$urlcheck = "<a href=\"$row9[9]\" target=_blank>Visit Web Site</a>";
}
$img = "<td nowrap valign=\"top\" align=left class=\"dealerbox\" width=\"175\"><div class=\"address1\"><strong>$row9[1]</strong><br>$row9[2]<br>$row9[3]<br><strong>$row9[4]</strong><br>$row9[5]<br>$row9[6]<br>Tel: $row9[7]<br>$emailcheck<br>$urlcheck</div></td><td>&nbsp;</td>\n";
echo "$img";

if ($i % $tds == '0' && $i < $tot) {
	echo "</tr><tr><td>&nbsp;</td></tr>\n<tr>\n";
} 

}
echo "</tr></table>";
if ($tot == "1") {
echo "<br>$tot Dealer Found";
} else {
echo "<br>$tot Dealers Found";
}

Basically I now need it to search and display dealers by their surrounding counties as well as the county they belong in, the table structure follows this (there are a total of 8 neighbour fields in the counties table and there are more fields in the trek table but I didnt include them because u can see the structure);

table: trek
+----+----------+----------+------+--------+--------+-----+
| id | Company | Address | City | County | Region | Zip |
+---------------------------------------------------------+
| | | | | | | |
+---------------------------------------------------------+

table: counties
+--------+------------+------------+------------+------------+
| County | Neighbour1 | Neighbour2 | Neighbour3 | Neighbour4 |
+--------+------------+------------+------------+------------+
| | | | | |
+------------------------------------------------------------+

I need to query the counties table to retrieve its neighbours and then execute the above query for each neighbour and print them out in the td's three in a row as above;
The only suggestions I have had are table joins which I dont understand 🙁

Can someone help please!

Thanks In Advance

Nick

[Anon]

Probably the reason why suggestions so far have been table joins is because MySQL apparently still doesn't do subselects. With subselects it would be easy.

I'd redesign your counties table - does it really matter which is the second, third, ... neighbour? What if there are more than four?

My design would go:
table: counties

county | neighbour

Wairoa | Gisborne
Wairoa | Whakatane
Wairoa | Taupo
Wairoa | Hastings
etc.

and have as many records for each county as necessary.

SELECT id,dealer,... FROM trek WHERE county='$whatever' OR county IN (select neighbour FROM counties WHERE county='$whatever')

But MySQL not doing subselects this would have to be written differently. Something like:

SELECT trek.id,trek.dealer,... FROM trek,counties WHERE trek.county='$whatever' OR (trek.county=counties.neighbour AND counties.county='$whatever')

I'm sure a join could be thrown in there to make things interesting...