Drop Down Menu From Multiple Columns

usernot

Hello,

I have a database that has several columns...one being BUSINESS NAME, another CATEGORY and a third of CATEGORY2- along with some others.

I would like to have a dropdown menu that would list all the CATEGORY and CATEGORY2 entries together without any repeats of categories.

Here is an example of what the database would look like:

BUSINESS CATEGORY CATEGORY2
Joe's Shoes Clothing
Ace Photography Photography Graphic Design
Sue's Designs Graphic Design

So the dropdown (in alphabetical order) would look like this:
Clothing
Graphic Design
Photography

Now if someone selected Graphic Design they would get a search result that listed both businesses in the database and their contact information.

Right now the script I have just works for a single CATEGORY column and looks like this (only part of the script):

$sql = "SELECT DISTINCT category FROM table ORDER BY category";
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';

Any ideas how I can add the CATEGORY2 column to the scripting so that it does what I mentioned above??

Appreciate your help in advance!!!

Kudose

$sql = "SELECT DISTINCT category, category2 FROM table ORDER BY category";

usernot

Kudose,

Thanks for the reply, but that didn't do it. It still didn't show the CATEGORY2 entires in the drop down.

Here is a better example of the code I am using:

<?
$sql = "SELECT DISTINCT category FROM table ORDER BY category";
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());

echo '<form method="POST" action="results.php">';
echo '<select name="category_search">';

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';
}
echo '</select>';
echo ' <input type="Submit" name="Submit" value="Submit"></form></table>';
?>

I am thinking it has to do with the line:

echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';

If I change the above to:

echo '<option value="' . $row['category'] . '">' . $row['category2'] . '</option>';

it shows my entries in the drop down but only those from the CATEGORY2 column...not any from CATEGORY. Not sure how to code this particular line so that it would pull from both CATEGORY and CATEGORY2 in the database.

Any ideas?

jayant

<? 
$sql = "SELECT DISTINCT category, DISTINCT category2 FROM table ORDER BY category"; 
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error()); 

echo '<form method="POST" action="results.php">'; 
echo '<select name="category_search">'; 

while ($row = mysql_fetch_array($result)) { 
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>'; 
echo '<option value="' . $row['category2'] . '">' . $row['category2'] . '</option>'; 
} 
echo '</select>'; 
echo ' <input type="Submit" name="Submit" value="Submit"></form></table>'; 
?>

note if there is any common category in both category and category2, it wiull still show up.
for that u will have to store the value in a common array and remove the duplicates

usernot

OK...tried that and got this error:

You have an error in your SQL syntax near 'DISTINCT category2 FROM table ORDER BY category' at line 1

Any ideas?

Also, how would you "store the value in a common array and remove the duplicates?" But perhaps I should come back to this question at a later time.