sql query help

[roseplant]

I have a database of, let's say, houses, and each house is, obviously, in a particular location. some locations contain more than one house. what i want to do is make a select box with all the locations in it as options.

here is the code i am using for the function. it returns the html code for the select box:

	


db_connect();
$data = db_query("SELECT * FROM houses");

$column_count = mysql_num_fields($data);
$code = '';
$code = $code . "<select name='location'>";

while ($row = mysql_fetch_row($data))
	{
	$code = $code . "<option>$row[2]</option>";
	}
$code = $code . "</select>";

return $code;

$row[2] contains the location. the problem here is i get duplicates when there is more than 1 house in a particular location.

could anyone help me sort this out?

[rbblue8]

perhaps it's with your table structure.

how is your table layed out...

i would personally recomand a two tables in this kind of setup.
1) one being only filled with locations
3) the second being filled with the auctal info.

then do something like this

$name='horsy';
$query=("SELECT * from horse where horse_name='$name'"):
$query1=("SELECT * from location");
$location_current=mysql_query=($query1);
$location=mysql_query=($query2);
$horse=mysql_fetch_assoc($location);

print  "<select name=\"location\">";
	  while ($location_answer = mysql_fetch_assoc($location_result)) {    

       if ($location_answer['rank']==$location_current) {
          $sel=" selected=\"true\"";
    	  } else {
        	  $sel="";
    		  }
    	  print "<option value=\"".$horse['location']."\"".$sel.">".$horse['location]."</option>";
		  }
	  print "</select>";
	  ?>

that will give you a drop down box.... and have the horse's current location selected so you can view all the horses on one page.. and not to worry about chaning all options for every horse every time you view the page.

does that help?

I hope that code works for ya... I did not test it.

[bradgrafelman]

Uh... I believe he said "house" as in the structure you live in, not "horse", an animal. :p

Anyway, two things you need to do with your query:

1. It looks like you're only using the location field in the table. "SELECT *" is about the most unoptimized thing you can do here. Only select the field's you need, like so:

$data = db_query("SELECT location FROM houses");

assuming 'location' is the column name in MySQL.

2. You only want distinct locations, not dupliates? Try using the DISTINCT keyword, like so:

$data = db_query("SELECT DISTINCT location FROM houses");

EDIT: Fore more info on the DISTINCT keyword, here's a link to the MySQL manual entry: link

[roseplant]

Thanks a lot guys. I used the DISTINCT keyword and its working fine now. Later on I do plan to put locations in a seperate table, as it will give me the opportunity to write a fixed soundbyte about that particular location wherever it applies. But thats another days work.

[bradgrafelman]

Don't forget to mark the thread resolved!