CODEWITHSUNDEEP
phpmysqlsearchsorting
Skyros
Skyros

Reputation: 37

Sorting the results of a mysql query

Im having difficulties trying to figure out an elegant solution to sorting the results of a mysql query based on a delimited string. Ill explain in more detail below

I am creating a database of contacts where individual users can add/remove people from a list. When the user adds a new contact I append the added contact id to a delimited string and store that data in a database column associated with that user (named contacts):

$userID = ?? //YOUR ID
$contactID = ?? //WHATEVER THE ADDED USER ID IS
$confirm = 0 //has the user been confirmed
$sql = "SELECT contacts FROM user WHERE id = '$userID'";
$query = mysql_query($sql);
$row = mysql_fetch_array($query);
$contact = $row['contacts']; 
$contact .= '|'.$contactID.':'.$confirm;
$update = mysql_query("UPDATE user SET contacts = '$contact' WHERE id = '$userID'");

//contact column data might be:  |10:0|18:0|36:0|5:0

When the user searches their contacts I grab the data from the contacts column, explode/split the string and return the individual users names:

$userID = ?? //YOUR ID
$sql = "SELECT contacts FROM user WHERE id = '$userID'";
$query = mysql_query($sql);
$row = mysql_fetch_array($query);
$contacts = explode("|", $row['contacts']);
foreach($contacts as $item)
{
 list($contactID,$confirm) = split(":", $item);
 $sql = "SELECT name FROM ".user." WHERE id = '$contactID'";  
 $query = mysql_query($sql);
 $row = mysql_fetch_array($query);
 echo($row['name'].'<BR>');
}

This indeed does return all the names, but it returns them in the order of the delimited string. I cant seem to find an elegant way to sort by name alphabetically.

Should I not store the contacts list in a delimited string? How would you solve this?

Upvotes: 1

Views: 4266

Answers (3)

Nathaniel Ford
Nathaniel Ford

Reputation: 21269

If you're using a relational database, then what you want is a separate table that stores person-contact relationships. Then you would modify your sql query to select based on a join across two tables 

SELECT * FROM person, contact
JOIN contact ON person.id = contact.personid
JOIN person ON contact.contactid = person.id
WHERE person.id = $id
ORDER BY person.lastname

(That code is probably not quite correct.)

If you're using a no-sql type implementation, the way you're doing it is fine, except that you will either have to programmatically sort after the fact, or sort-on-insert. Sort on insert means you'd have to query the current list of contacts on inserting one, then sort through to find the right position and insert the id into the delimited string. Then save that back to the db. The downside to this is you'll only be able to sort one way.

Generally, people use relational databases and 'normalize' them as described above.

Upvotes: 1

Cal
Cal

Reputation: 7157

There are two obvious approaches:

  1. Sort the results once you've fetched them
  2. Fetch them all in one query and have the DB sort them

For #1, use usort():

$rows = array();
foreach ($contacts as $item){
    list($contactID,$confirm) = split(":", $item);
    $query = mysql_query("SELECT name FROM user WHERE id = '$contactID'");
    $rows[] = mysql_fetch_array($query);
}
usort($rows, 'sort_by_name');

function sort_by_name($a, $b){
    return strcmp($a['name'], $b['name']);
}

foreach ($rows as $row){
    echo($row['name'].'<BR>');
}

For #2, build a list of IDs and use IN:

$ids = array();
foreach ($contacts as $item){
    list($contactID,$confirm) = split(":", $item);
    $ids[] = $contactID;
}
$ids = implode(',', $ids);
$query = mysql_query("SELECT name FROM user WHERE id IN ($ids) ORDER BY name ASC");
while ($row = mysql_fetch_array($query)){

    echo($row['name'].'<BR>');
}

Upvotes: 2

Godwin
Godwin

Reputation: 9937

You're right, you should not store the contacts in a string. Instead use another table which contains the user information. The new table should look something like the following:

Table: user_contacts
| user_id    |    contact_id    | confirm |
-------------------------------------------
|  your data here...                      |

Then when you need your contact list you can simply perform another query:

SELECT * FROM `user_contacts`
JOIN `users` ON `users`.`id` = `user_contatcs`.`user_id`
WHERE `users`.`id` = $id
ORDER BY `users`.`name`;

Or however you need to order it.

Upvotes: 2

Related Questions