array_unique with mysql_fetch_row($result)

how to use both on the same result

[harrybailey]

I have been trying to master this for over 2 hours now so I thought it was fair enough I hand it over to someone who might be able to tell me what to do straight away.
I have a list of categories and I want to list them in a drop down menu. The only problem is that I don't want to show each one more than once. Hence using the array_unique function. The problem I am having is using it alongside/before/after the mysql_fetch_row($result) bit.
Here is what I have so far. See what you can do:

$query = ("SELECT * FROM sitemap") or die("something messed up");
$result = mysql_query($query);
while ($cat_row = mysql_fetch_row($result)){
print("<option value=\"" . $cat_row[1] . "\">" . $cat_row[1]);

But this is without array_unique() and so returns the same category more than once.
Heeeeeelllllppppp!

[harrybailey]

$query = ("SELECT * FROM sitemap") or die("something messed up");
$result = mysql_query($query);
$result2 = mysql_fetch_array($result);
while ($cat_row = mysql_fetch_array($result)){
print("<option value=\"" . $cat_row[1] . "\">" . $cat_row[1]);
}

EDIT:
ok scratch that. It didnt work when I added another input with the same category. 2 worked 3 of the same doesn't.
Again.....help.

[dreamcatcher]

Hi harrybailey,

Have you tried using the MySQL DISTINCT clause?

$query = ("SELECT DISTINCT row FROM sitemap") or die("something messed up");

This prevents duplicate rows being fetched.

[harrybailey]

Hi Dreamcatcher

just tried it but cant seem to get it working:

$query = ("SELECT DISTINCT category FROM sitemap") or die("something messed up"); 
$result = mysql_query($query); 
while ($cat_row = mysql_fetch_row($result)){ 
print("<option value=\"" . $cat_row[1] . "\">" . $cat_row[1]); 

doesn't return anything where as SELECT DISTINCT * FROM sitemap still returns duplicated. Am I doing something wrong or is there another way.
The obvious otherway to do it would be to have the categories in there own table so that there would be no duplicates in the table. This involves a re-write though.

[dreamcatcher]

How about:

SELECT DISTINCT (category) FROM....

added

To use array_unique try the following:

$query = ("SELECT category FROM sitemap") or die("something messed up");
$result = mysql_query($query);

while ($row = mysql_fetch_array($result))
{

$categories[] = $row['category'];

}

$newData = array_unique($categories);

foreach ($newData as $dropDown)
{
echo "<option>" . $dropDown . "</option>\n";
}

Think that should work.

[edited by: dreamcatcher at 4:43 pm (utc) on Aug. 4, 2004]

[harrybailey]

nope still no joy.
This I will put it down to me making a stupid mistake somewhere.
Guess that going with the seperate table will be better in the long run anyway.
All efforts result in no (zero) results unless I use the (*) in them (SELECT * FROM).

Ahh well thanks for your efforts. Let me know if you think of anything else.
Im doing this on my own computer where I have MYSQL and PHP and APACHE so Im assuming it is something to do with my setup.

[dreamcatcher]

Try the code above. :)

[harrybailey]

You are a star. Works a treat. I'll go look through it now and see why it works.

Thank you, thank you, thank you.

[dreamcatcher]

You`re welcome. :)