What's the most efficient way to query and sort a semicolon separated list of ids according to their associated strings from another table?

Question

I have 2 tables in a mysql database : users and pets. In the users table there is a field that contains a semicolon separated list of the pets that a user has (pet_ids = 1;2;3;4;). These ids correspond to unique ids (keys) in the pets table which contain the pet name (1 = rex, 2 = goldie, 3 = squeak, 4 = bubble).

I want to present a list of these pets on a PHP page in alphabetical order but retain the id of the pet name to pass as a variable to another page when the name of the pet is clicked.

What is the most efficient way to achieve this.

So far the list I generate is not in alphabetical order but in order that the pets were added -

Funtions for querying the database

function getUserPets($user_id) {    
  $query= "SELECT pet_ids FROM users WHERE id='$user_id'";  
  $result = @mysql_query ($query);
  if (!$result) {
      die('Invalid query: ' . mysql_error());
  }
  while($row = mysql_fetch_array($result)) {
          $the_pets = $row['pet_ids'];
  }
  return $the_pets; //1;2;3;4;
} 
function getPetName($id) {
$name = "";
    $query= "SELECT name FROM pets WHERE id='" . $id . "' ";
$result = @mysql_query ($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
while($row = mysql_fetch_array($result)) {
    $name = $row['name'];
}
return urldecode($name);
}

The code for generating the list ...

$petstring = getUserPets($userid);
$petsarray = explode(";", $petstring);
foreach ($petsarray as $pet_id) {
    echo("<li><h4><a href=\"pet.php?id=$pet_id\">" . writeListPetName($pet_id) . "</a></h4></li>");     
}

What is the most efficient way to get an alphabetized list - I need to keep the database structure the same.

thanks all

Answer 1

If you have to work with separated lists for a set of ids, rather than a properly normalised table, at least use a single db query to return the set of pet names rather than separate queries for each individual pet.

$petsarray = implode(',',explode(";", $petstring)); 

function getPetName($ids) { 
   $name = ""; 
   $query= "SELECT id, name FROM pets WHERE id IN (" . $ids . ") ORDER BY name"; 
   $result = mysql_query ($query); 
   if (!$result) { 
      die('Invalid query: ' . mysql_error()); 
   } 
   while($row = mysql_fetch_array($result)) { 
      $names[$row['id']] = urldecode($row['name']); 
   } 
   return $names; 
} 

var_dump($names);

And don't use @ to suppress errors, trap for them properly

P.S. Why do you need to urldecode() the pet names?