[SOLVED] LIMIT Question

[JSHINER]

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?

[Fergusfer]

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;
?>

[JSHINER]

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.

[Fergusfer]

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

[JSHINER]

Still displaying the same town more than once... ?

[Fergusfer]

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.

[JSHINER]

Perfect - Thank you!

[Fergusfer]

Glad to help.  Please mark this thread solved.  Thanks.