Help eliminating duplicate entries in a list box for selection 1

[Evil8]

The client wants users to select and return "events" data (venue.name, event.eventdate, event.eventtime, venue.address, etc.) from a single city. I set up a list box and pulled the cities from my venue table. But since I have multiple venues in some cities I get every instance of that city listed.

 <?php 
        ...

	mysql_connect($host,$username,$password);
	@mysql_select_db($database) or die("Unable to select database");
		
	$query = "SELECT venue.venueid, venue.city FROM venue ORDER BY city";
 	$result = mysql_query($query);
	
	$options="";

	while ($row = mysql_fetch_array($result)) {
	
		$id = $row["venueid"];
		$city = $row["city"];
		$options.="<OPTION VALUE=\"$id\">".$city.'</OPTION>';
	}
?>
      Find a meeting in my city: 
      <select name=city>
        <option value = 0>Choose 
        <?=$options?>
      </select> </td>
    <td colspan="3">&nbsp;</td>
  </tr>
  <tr align="center" valign="middle"> 
    <td height="2" colspan="6"> </td>
  </tr>
</table>

I'm not sure if I need to remove venueid from the code or if I need to reconfigure my database structure with a city table.

I'm confused so any help would be appreciated.

Thanks!

 

[r937]

you said "from a single city"

but your code looks like it returns multiple cities

which is it?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Evil8]

Sorry for the confusion. The code populates a list box of all city names I have in the venue table. There are multiple venues in some cities.

The venue table has:
venueid
name
address
city
state
zip

So the result of my code list Duluth, for example, three times as it is in the table 3 times (3 different venues), but Ashland only once because there is only one venue in Ashland.

The client wants the user to be able to pick a city from the list box and then see all the events listed in that city on the web page.

I hope that is clearer.

 

[r937]

SELECT city 
  FROM venue 
GROUP
    BY city

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Evil8]

Excelent! Again thank you for your help.