View Full Version : outputting unique rows only from mysql db

11-22-2007, 12:55 AM
Here's what I want to do:
I'm working on a simple image gallery and I need to allow users to sort images into galleries they create. I'm using a dropdown to allow them to choose from galleries they have already created.

Here's what I'm using:

$result=mysql_query("SELECT DISTINCT * FROM media WHERE username='$username'");

while($row = mysql_fetch_array($result)){
//show existing galleries
echo "<option>";
echo $row['gallery'];
echo "</option>";

Here's my problem (if you gurus haven't discovered already):D:
Even with the DISTINCT clause in the select statement, I still have it outputting EVERY images gallery name into the drop down. (Sorry if that's not real clear. If you don't understand, I'll be glad to attempt to explain it in another way)

Thanks in advance for any help!

11-22-2007, 02:18 AM
edit: never mind, Mis-read the question and answered it totally wrong

11-22-2007, 02:30 AM
I'll be glad to attempt to explain it in another way

Yes, do that. Explain your table structure too, and why you aren't using the a user id/relational setup instead of the username.

11-22-2007, 02:48 AM
table structure:
id (for image)

Restating my problem:
there will be multiple images linked to the same username in this table. ALL users will have their image information stored in the same table (above structure). Every user will have the ability to sort their images into different galleries (categories). They will be able to name these categories themselves. Now, for my problem. When they want to insert a new image into an existing category, I want them to be able to select the existing category from the gallery column via a drop down menu (so: I need to pull the data from the column "gallery" and display it in a drop down menu while eliminating multiple occurrences of the same gallery name. So I need to know how to list the results of the column "gallery" in a drop down menu while eliminating multiple occurrences of the same word (gallery name) in the drop down. There are multiple occurrences of the same word (gallery name) because there are multiple images tagged with the same gallery name.
The portion in bold says it all.

I appreciate your time, guys...

11-22-2007, 03:28 AM
Using the UNIQUE keyword in your SQL query should do that for you. The idea is that it returns only a single occurrence of a given value no matter how many times that value may appear in the table.

11-22-2007, 05:29 AM
Have a table for the images(with fields for user id, and gallery id), a table for galleries(with a user id field), and a table for users(use your current one I guess). Then you can simply select the galleries for that user from the gallery table, etc.