JSHINER Posted April 5, 2007 Share Posted April 5, 2007 I have the following function: function zips($db, $state = false) { $zip = $db->escape($zip); $arr = $db->getArray("SELECT towns_zipcodes.town_id AS town_id, towns_zipcodes.zip AS zip, towns.name AS name, towns.id AS id, towns_zipcodes.state AS state FROM towns, towns_zipcodes WHERE towns_zipcodes.town_id = towns.id AND towns_zipcodes.state = '$state' ORDER BY name ASC"); return $arr; } Which I have displayed as: <?php if($page['zips']) { foreach ($page['zips'] as $z) { echo ''; echo '<option value="show.php?zip=', $z['zip'], '">', $z['name'], '</option>'; } } ?> Problem is it's calling Towns & Zips - some towns have multiple zips, so it will show Town 00001, Town 00002, Town 2 00003, Town 2 00004 .... etc. I want it to limit it to ONE town name. How can I do this? Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 Try using this for output: <?php if($page['zips']) : $last = ''; foreach ($page['zips'] as $z) : if ($last != $z['name']) : if (!empty($last)) echo '</optgroup>'; $last = $z['name']; ?> <optgroup label=<?=$z['name'] ?>> <?php else : ?> <option value="show.php?zip=<?=$z['zip'] ?>"><?=$z['zip'] ?></option> <?php endif; endforeach; endif; ?> Quote Link to comment Share on other sites More sharing options...
JSHINER Posted April 5, 2007 Author Share Posted April 5, 2007 Thank you - but - I only want ONE TOWN and ONE ZIP - the second, third, etc. zip for each town is not necessary for this function. I simply Want Town A - 00001, Town B - 00003 (Omitting Town A - 00002) and so on. Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 My apologies. You should probably just use this query: SELECT z.town_id AS town_id, z.zip AS zip, t.name AS name, t.id AS id, z.state AS state FROM towns t JOIN towns_zipcodes z ON z.town_id = t.id WHERE z.state = '$state' GROUP BY z.zip ORDER BY name ASC Quote Link to comment Share on other sites More sharing options...
JSHINER Posted April 5, 2007 Author Share Posted April 5, 2007 Still displaying the same town more than once... ? Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 SELECT z.town_id AS town_id, z.zip AS zip, t.name AS name, t.id AS id, z.state AS state FROM towns t JOIN towns_zipcodes z ON z.town_id = t.id WHERE z.state = '$state' GROUP BY t.id ORDER BY t.name ASC Sorry, I used the wrong GROUP BY field. You want to have each city once, and ZIP codes won't overlap, right? So we need to group the results by the city ID. My earlier post told the server to group by ZIP. Quote Link to comment Share on other sites More sharing options...
JSHINER Posted April 5, 2007 Author Share Posted April 5, 2007 Perfect - Thank you! Quote Link to comment Share on other sites More sharing options...
Fergusfer Posted April 5, 2007 Share Posted April 5, 2007 Glad to help. Please mark this thread solved. Thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.