Page 1 of 1

unwanted repeating sql values

Posted: Sun Oct 15, 2006 6:21 pm
by plankguy
I'm trying to generate a jump menu with dynamic values that i've queried from my sql database. The problem that i'm getting is that i have multiple values with the same 'genre' but in my menu i only want to list those 'genres' once. Here's what I have now:

Code: Select all

<form name="jumper" action="template.php" method="get">
				<select name="page" id="jumper">
				<option selected><B>Select a Category</B></option>

Code: Select all

<?php while ($row = mysql_fetch_assoc($result)) {
					$page = $row["genre"];
					$name = $row["genre"];
					echo '<option value="' . $page . '">-' . $name . '</option>';
				}
				?>

Code: Select all

</select>
				<a href="javascript: submitform()"><img src="images/wrapper/jumper-go.gif" width="15" height="16" border="0" alt="Go" id="go"></a>
			</form>
This gives me a list of:
-pet care
-automotive
-home
-pet care
-food
-automotive

Because I have 2 items with the genre 'pet care' and 2 items with 'automotive', i get this result. Whereas I only want each of those genres listed once.

Help!

Posted: Sun Oct 15, 2006 6:41 pm
by volka
May we see the actual sql query please?

Posted: Sun Oct 15, 2006 7:06 pm
by plankguy

Code: Select all

$query = "SELECT `index`, `name`, `description`, `genre`, `link`, `logo` FROM `table` ORDER BY $order";
$result = mysql_query ($query) or die ( 'Query Error: ' . mysql_error () );

Posted: Sun Oct 15, 2006 7:10 pm
by volka
Why do you query all those fields if you're only using genre?
Try

Code: Select all

SELECT DISTINCT `genre` FROM `table` ORDER BY $order

Posted: Sun Oct 15, 2006 7:28 pm
by plankguy
I am using all those fields for a different function, I was just trying to keep the query count low. Does querying more than once not start to slow down the script?...

BTW that worked like a charm....Thanks a lot

Posted: Sun Oct 15, 2006 7:39 pm
by volka
plankguy wrote:Does querying more than once not start to slow down the script?...
Maybe, but I don see what you're gaining by querrying all the data at once.

Only guessing: The user selects a genre and the script will show a (detailed) list of all records having this genre?
Then you need all genres for the select box, but only genre for this. SELECT genre FROM ...
And all fields for the detail view, but only for one genre. SELECT ... WHERE genre='xyz'
In this case two querries are probably a good choice.
Even better if you normalize your table, splitting it up in two tables.

table `genre`: genre_id, genre_name
table `table`: index, name, description, genre_id, link, logo

You store each genre only once in `genre`. Therefore you can simply SELECT all entries of this table for the <select> element.
In `table` you only save the corresponding genre_id.