Please support our PHP advertiser: Lunarpages PHP Web Hosting
 |
•
•
Join Date: Dec 2007
Posts: 10
Reputation: 
Rep Power: 2
Solved Threads: 0
Newbie Poster
Overwriting duplicate mysql results How would I make it so when I select something from a mysql database, that information isn't stored in the array twice? Here is my code
I have two entries with the genre 'metal.' and so when I execute this code, it displays the header 'metal' twice.
<?php
include "header.html";
include "db.php";
if($_GET[by]==genre)
{
$sort = mysql_query("SELECT genre FROM bands ORDER BY genre");
while($sort2 = mysql_fetch_array($sort))
{
$sql=mysql_query("SELECT * FROM bands WHERE genre='$sort2[genre]' ORDER BY name");
$number=mysql_num_rows($sql);
$sort2[genre]=str_replace("%20", " ", "$sort2[genre]");
echo $sort2[genre]." (".$number.")<blockquote>";
while($rows=mysql_fetch_array($sql))
{
$name="$rows[name]";
$name2=str_replace("%20", " ", "$rows[name]");
echo "<a href=band.php?band=".$name.">".$name2."</a><br>";
}
echo "</blockquote>";
}
}
...I have two entries with the genre 'metal.' and so when I execute this code, it displays the header 'metal' twice.
Last edited by tie372 : Jan 5th, 2008 at 3:01 pm.
•
•
Join Date: Aug 2005
Location: somewhere in time
Posts: 83
Reputation:
Rep Power: 4
Solved Threads: 5
Junior Poster in Training
Have you tried using SELECT DISTINCT?
That will return only one "distinct" row per genre value.
It looks like you are doing things the hard way. You should be looking at pulling all of your data with one hit on the database by joining the tables. If you have 10 genres, the way you are doing things will hit the database 11 times.
$sort = mysql_query("SELECT DISTINCT genre FROM bands ORDER BY genre");That will return only one "distinct" row per genre value.
It looks like you are doing things the hard way. You should be looking at pulling all of your data with one hit on the database by joining the tables. If you have 10 genres, the way you are doing things will hit the database 11 times.
•
•
Join Date: Dec 2007
Posts: 10
Reputation:
Rep Power: 2
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by TopDogger 
Have you tried using SELECT DISTINCT?
$sort = mysql_query("SELECT DISTINCT genre FROM bands ORDER BY genre");
That will return only one "distinct" row per genre value.
It looks like you are doing things the hard way. You should be looking at pulling all of your data with one hit on the database by joining the tables. If you have 10 genres, the way you are doing things will hit the database 11 times.
Thanks it worked perfect.
•
•
Join Date: Sep 2005
Posts: 758
Reputation:
Rep Power: 6
Solved Threads: 45
Master Poster
•
•
•
•
Originally Posted by tie372
Thanks it worked perfect.
Since you're obviously selecting all genres, you don't need to SELECT the genres first. You just have to order your results by the genre, and then by the name.
Just use:
$sql=mysql_query("SELECT * FROM bands ORDER BY genre, name");If there are some bands that do not have a genre, those will also be selected. This may be desired or you can leave them out with a WHERE clause.
$sql=mysql_query("SELECT * FROM bands WHERE genre != '' ORDER BY genre, name");Other than that, I don't see any reason for selecting genres.
Usually, you'd want to use the index on a separate table to reference it. Like having genre_id and referencing this in bands. This makes the db smaller and more efficient. Thats off topic however...
Last edited by digital-ether : Jan 7th, 2008 at 3:30 am. Reason: code formatting...
www.fijiwebdesign.com - web design and development and fun
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!
|
Other Threads in the PHP Forum
- Previous Thread: php/mysql Help Needed ( Badly )
- Next Thread: Email in Forms
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
