[Resolved] Dropdown Question

[usernot]

I am currently using this script (posted by another member) to create a dropdown list which is created from my MySQLdatabase.

//Query to get info
$trow = mysql_query("SELECT * FROM table");

if (!$trow) {

echo 'Could not run query: '.$trow."\n". mysql_error();

exit;

}

//Dropdown list
PRINT "<p><font size='2' face='Tahoma'>Please Select Category.</font><br>
<select name='search' size='1' style='background: white' style='border_width: 1pt' style='border-color: black'>";

//looping over db records
while ($row = mysql_fetch_row($trow)) {
print "<option>$row[2]</option>";
}

I am pulling business categories to fill in the dropdown list and lots of businesses share the same category type. When this script is run, it lists the entire category column from the database in the dropdown which includes multiple repeats and in order in which they were entered into the database.

Is there a way to:
1) Remove repeats from the dropdown list (even thought they are in the MySQL database?

2) Sort the dropdown alphabetically?

Thanks in advance.

[LarsLai]

if i understand you right, you can do all you want with the sql query:
SELECT DISTINCT * FROM table ORDER BY name
name, of course, has to be the field you want to order by.

[usernot]

LarsLai,

Yes, your suggestion does sort alphabetically- thanks! But here is the problem I am still facing...

Say this is my database

business name category
company 1 retail
company 2 lodging
company 3 restaurant
company 4 lodging
company 5 retail
company 6 restaurant

When I run the script and get a dropdown list from the database it looks like this...

retail
lodging
restaurant
lodging
retail
restaurant

After adding your suggestion it looks like this...

lodging
lodging
restaurant
restaurant
retail
retail

Can I get rid of these category repeats in the dropdown list? I would like the dropdown to look like this...(without changing the database)

lodging
restaurant
retail

Thanks!

[usernot]

P.S.

I added your DISTINCT suggestion but it still didn't remove the duplicates:

$trow = mysql_query("SELECT DISTINCT * FROM main ORDER BY category");

[ahundiak]

SELECT DISTINCT category FROM main ORDER BY category;

You might want to spend some time learning SQL. It has lot's of good stuff in it.

[usernot]

You are right...there are lots of good things to learn about PHP and I am certainly a newbie. I had tried

$trow = mysql_query("SELECT DISTINCT category FROM main ORDER BY category");

but it just brought up a blank dropdown. I figured out that I needed to change:

print "<option>$row[2]</option>";

to

print "<option>$row[0]</option>";

and that seemed to do the trick!

Thanks.