Page 1 of 1

PHP/MySQL Dropdown List

Posted: Tue Jun 01, 2010 12:23 pm
by Luke Warm
Hi,

I've created a dynamic drop down lit in PHP that draws information from a MySQL database of books. I've created the drop down for users to search the DB using the "catagory" fieldname.

Here's the code:

Code: Select all

<?php
 mysql_connect("localhost", "root", "scallywag") or die(mysql_error()); 
 mysql_select_db("books") or die(mysql_error());

  $result = mysql_query("SELECT category FROM books ORDER BY category")
      or die("Invalid query: " . mysql_query());
  echo '<label>Category: </label>';
  echo '<select id="category" name="category">';
  echo '<option value="">Select</option>';
  while ($row = mysql_fetch_assoc($result)) {
     $ca = $row['category'];
     echo "<option value='$ca'>$ca</option>";
  }
  echo '</select>';
?>
What's happening is the drop down gets populated with the entire number of categories. The list shows multiple numbers of items on each categories like:
Adult
Adult
Adult
Arts & Crafts
Arts & Crafts
Biography
Business
Business
etc...

How can I have the drop down show only one category item for each category?

Thanks for your help.

Re: PHP/MySQL Dropdown List

Posted: Tue Jun 01, 2010 12:35 pm
by hutch10
This is a farily easy response. You need to add the distinct keyword to your select statement. What distinct does is select only those unique items for the field(s) that are in the section between the distinct and from clause. In your case, you only have one field (category), so you will have only the unique categories sorted through the use of your ORDER BY clause.

Code: Select all

SELECT DISTINCT category FROM books ORDER BY category
Hope this helps.