unwanted repeating sql values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
plankguy
Forum Newbie
Posts: 18
Joined: Sat Jun 25, 2005 7:39 pm

unwanted repeating sql values

Post 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!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

May we see the actual sql query please?
plankguy
Forum Newbie
Posts: 18
Joined: Sat Jun 25, 2005 7:39 pm

Post 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 () );
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
plankguy
Forum Newbie
Posts: 18
Joined: Sat Jun 25, 2005 7:39 pm

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Post Reply