[Resolved] 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 of the CATEGORY and CATEGORY2 entries together without any repeats of categories.

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

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. Here is what I have so far but am getting an error: "You have an error in your SQL syntax near 'DISTINCT category2 FROM table ORDER BY category' at line 1"

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

Any ideas on how I can resolve this? Thanks in advance!

Drakla

OOoh, nice question. First of all I though you could just run a union of the table like

SELECT DISTINCT category FROM table UNION SELECT category2 FROM table;

But was told you can't reopen the same table... so it's 2 queries

create temporary table cat1s as select distinct category from table;

(select * from cat1s) UNION (select category2 FROM table) order by category;

Then change your outputting to:

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';
}

usernot

Thanks for the reply. I am a newbie, though, and I am not sure I completely understand what you have suggested.

I changed this line:
$sql = "SELECT DISTINCT category, DISTINCT category2 FROM table ORDER BY category";

To this line:
$sql = "(select * from cat1s) UNION (select category2 FROM table) order by category";

But I get an error. I guess I am not sure what the 'cat1s' is and what to do with it.

Thanks in advance!

Drakla

Hi. Like I said, you've got to run two queries. The first query creates a temporary table called cat1s of all the categories from the first column, then the second query selects all the columns from cat1s and then tacks on the ones from the second column with the UNION query. Union by default skips duplicate rows. So in your code you have to

$sql = "CREATE TEMPORARY TABLE cat1s AS SELECT DISTINCT category FROM table;";
mysql_query($sql) or die($sql . '<br />' . mysql_error()); 

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

Before the output

usernot

Drakla,

Okay...I replaced my single line of code with the exact two lines you posted (and the output line from your earlier post) but am still getting an error and this is what is says on the page where the dropdown should be:

(SELECT * FROM cat1s) UNION (SELECT category2 FROM table) ORDER BY category

You have an error in your SQL syntax near '(SELECT * FROM cat1s) UNION (SELECT category2 FROM table) ORDER BY category' at line 1

Any ideas?

Thanks much!!!

Drakla

Well the table you're getting the categories from isn't actually called 'table' is it?

And union is supported from MySQL 4, do you know what version of mysql you're running?

You can run the query SELECT VERSION() and read back the results if you're unsure.

If you're running an earlier version I'll have to rejig the query.

You could run these three

create temporary table cats as select distinct category from table;
insert into cats select distinct category2 from table;
select distinct * from cats;

usernot

Drakla,

You are correct that the table is not actually called table in my database...but obviously I change that word to the actual table name when I upload the php to my website.

I went into phpMyAdmin and it says "MySQL 3.23.58 running."

Thanks in advance!

Drakla

It'll have to be the three queries at the bottom of my previous post then, I'm afraid. Hopefully you've got permissions to create temporary tables. Don't worry about cleaning them up, they're gone as soon as the script ends, and won't clash even if you're running the query at the same time.

usernot

Okay...here is what I have now:

$sql = "CREATE TEMPORARY TABLE cat1 AS SELECT DISTINCT category FROM table";
$sql = "INSERT INTO cat1 SELECT DISTINCT category2 FROM table";
$sql = "SELECT DISTINCT * FROM cat1";

Now I get the error:

SELECT DISTINCT * FROM cat1
Table 'table.cat1' doesn't exist

Does this mean I can't create temporary tables?

Drakla

You've got to run each query seperately, so:


$sql = "CREATE TEMPORARY TABLE cat1 AS SELECT DISTINCT category FROM table"; 
mysql_query($sql) or die($sql . '<br />' . mysql_error());

$sql = "INSERT INTO cat1 SELECT DISTINCT category2 FROM table"; 
mysql_query($sql) or die($sql . '<br />' . mysql_error());

$sql = "SELECT DISTINCT * FROM cat1"; 
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());

You don't need to take the results for the first 2 queries as they don't have result sets.

usernot

I added what you suggested but also changed this:

$sql = "SELECT DISTINCT * FROM cat1";

to this:

$sql = "SELECT DISTINCT * FROM cat1 ORDER BY category";

So it does pull all the category and category2 entries into the dropdown. I can select one of the category listings and it will show me the contact information from the database about the particular business(s). However, when I do that for one of the category2 listings, it does not pull any information from the database about the business(s).

Any ideas? I really appreciate your help.

Drakla

Can you post the query you're actually using to select the companies. You will have to check the selected values against category one and two, so something like

SELECT BUSINESS FROM table WHERE category = 'selectedcategory' OR category2 = 'selectedcategory';

usernot

I changed a line in the results page (like you mentioned) to:

$result = mysql_query("SELECT * FROM table WHERE category LIKE '%$search%' OR category2 LIKE '%$search%' ORDER BY business");

and it worked!!! Thanks so much Drakla!!!

Now...say I had a third column titled 'category3' in the database. Could I just keep adding to the temp database and do something like this?

$sql = "CREATE TEMPORARY TABLE cat1 AS SELECT DISTINCT category FROM table";

mysql_query($sql) or die($sql . '<br />' . mysql_error());

$sql = "INSERT INTO cat1 SELECT DISTINCT category2 FROM table";

mysql_query($sql) or die($sql . '<br />' . mysql_error());

$sql = "INSERT INTO cat1 SELECT DISTINCT category3 FROM table";

mysql_query($sql) or die($sql . '<br />' . mysql_error());

$sql = "SELECT DISTINCT * FROM cat1";

$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());

Drakla

Great! Mark the thread as resolved if it's done.

It might be a good idea to look up something on "database normalisation" either here on in google, if this, or future projects are going to get huge, because really you'd be better off having a categories table and a businesses table, where category and category2 refer to ids int category table,

Categories
id - category
1 plumbers
2 grocers
3 Web Developers

Etc. Then when creating the dropdown you'd just choose everything from the category table and make the value field of the selects be the ID, eg

$sql = "SELECT * FROM categories ORDER BY category";
... etc

echo '<select name="category">';

while ($row = mysql_fetch_assoc($result))
{
    echo '<option value="'.$row['id'].'">', $row['category'], '</option>';
}

Then your query for to choose the companies would be based on an integer, not a string, so much faster - and if you also index category and category2 in the businesses table (which you might have anyway) we're talking much fastness.

usernot

Drakla,

Thanks so much for your help...I have posted the question in other forums and you were the only one that answered the question!

Cheers!!!