Member Avatar for Member #250709

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.

  • 3 Contributors
  • 3 Replies
  • 75 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by digital-ether

Dani AI

Generated

A simple, robust way to stop duplicate genre headings is to do one database hit and group results in PHP while using modern APIs and safe output practices. saw the duplicate because genres were selected more than once; and pointed out the two usual approaches (deduplicate in SQL, or order and emit headers only when the genre changes). The example below shows a single-query, PDO-based grouping strategy that prints each genre once, escapes output, and keeps URLs safe.

$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->query('SELECT id, name, genre FROM bands');
$groups = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $g = trim($row['genre']);
  if ($g === '') $g = '(no genre)';
  $groups[$g][] = $row;
}
ksort($groups, SORT_NATURAL | SORT_FLAG_CASE);
foreach ($groups as $genre => $rows) {
  echo htmlspecialchars($genre).' ('.count($rows).')<blockquote>';
  foreach ($rows as $r) {
    echo '<a href="band.php?band='.rawurlencode($r['name']).'">'.htmlspecialchars($r['name']).'</a><br>';
  }
  echo '</blockquote>';
}

Notes: use prepared statements for queries that include user input, and migrate away from the old mysql extension. For very large tables prefer ordering and streaming from SQL to avoid building large arrays in memory. See the PDO manual for connection and prepared-statement details: PDO manual.

Recommended Answers

All 3 Replies

Member Avatar for 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.

Member Avatar for Member #250709

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.

Member Avatar for 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...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.