Overwriting duplicate mysql results

[tie372]

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

<?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.

[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.

[tie372]

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.

[digital-ether]

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