排序mysql查询的结果 [英] Sorting the results of a mysql query

问题描述

我很难找到一种优雅的解决方案，用于基于定界字符串对mysql查询的结果进行排序.病态在下面有更详细的解释

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

我正在创建一个联系人数据库，单个用户可以在其中添加/删除列表中的人员.当用户添加新联系人时，我将添加的联系人ID附加到带分隔符的字符串中，并将该数据存储在与该用户相关联的数据库列中(命名为联系人):

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?

推荐答案

有两种显而易见的方法:

There are two obvious approaches:

1. 获取结果后将其排序
2. 在一个查询中全部获取它们，并让数据库对其进行排序

对于#1，请使用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>');
}

对于#2，构建ID列表并使用IN:

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>');
}

这篇关于排序mysql查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！