通过或使用PHP排序函数对MySQL查询进行排序

I have a query that I want to sort alphabetically, but the trick is that I want the sorting to treat two columns equally. For instance, if the first row of first_col equals apple and the second row of second_col equals aardvark I want the value in the second row of second_col to be listed before the value in the first row of first_col. A value (not NULL or '') will always exist in every row of second_col, but the value in first_col can be ''. Hopefully I have explained this good enough. I don't care if I have to use MySQL or PHP for this, but once sorted, the array is read through and echoed into an HTML table. Any thoughts?

我有一个查询，我想按字母顺序排序，但诀窍是，我希望排序对两列相等。例如，如果first_col的第一行等于apple，而second_col的第二行等于aardvark，我希望在first_col的第一行的值之前列出second_col的第二行中的值。值(非NULL或")将始终存在于second_col的每一行中，但first_col中的值可以是"。希望我已经解释得足够好了。我不关心是否需要使用MySQL或PHP，但是一旦排序，数组将被读取并回显到HTML表中。任何想法吗?

EDIT

编辑

This is what I have for code right now. In my MySQL query I need b_name and l_name to be equal. The column b_name does not always have a value. When I put the values into the table it is based on the existence of b_name. If b_name does not exist the f_name and l_name are combined to replace b_name.

这就是我现在的代码。在我的MySQL查询中，我需要b_name和l_name相等。列b_name并不总是具有值。当我将值放入表中时，它是基于b_name的存在。如果b_name不存在，则合并f_name和l_name以替换b_name。

                $query = "SELECT * FROM customers ORDER BY b_name, l_name";
                $result = mysql_query($query);
                mysql_close($link);

                $num = mysql_num_rows($result);                         

                for ($i = 0; $i < $num; $i++){

                    $row = mysql_fetch_array($result);

                    $class = (($i % 2) == 0) ? "table_odd_row" : "table_even_row";

                    if($row[b_name]!=''){
                        echo "<tr class=".$class.">";

                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[c_id]."</a></td>";
                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[b_name]."</a></td>";
                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[phone]."</a></td>";

                        echo "</tr>";

                    }

                    else{
                        echo "<tr class=".$class.">";

                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[c_id]."</a></td>";
                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[f_name]." ".$row[l_name]."</a></td>";
                            echo "<td><a href=Edit_Customer.php?c_id=".$row[c_id].">".$row[phone]."</a></td>";

                        echo "</tr>";

                    }


                }

            ?>      

        </table>

5 个解决方案

#1

If your tables are very similar you can do this

如果您的表非常相似，您可以这样做

In my case I have a table test_a with 2 columns id and name

在我的例子中，我有一个带有两个列id和名称的test_a表

(SELECT * FROM test_a a1) 
UNION ALL 
(SELECT * FROM test_a a2) 
ORDER BY name DESC

#2

Your question isn't completely clear but you could try using this as your ORDER BY clause:

你的问题还不完全清楚，但你可以试着用这句话作为你的ORDER BY子句:

ORDER BY LEAST(first_col, second_col)

Demonstration:

演示:

CREATE TABLE table1 (first_col VARCHAR(100) NOT NULL, second_col VARCHAR(100) NOT NULL);
INSERT INTO table1 (first_col, second_col) VALUES
('a', 'b'),
('d', 'e'),
('f', 'c');

SELECT first_col, second_col
FROM table1
ORDER BY first_col, second_col;

a b
d e
f c

SELECT first_col, second_col
FROM table1
ORDER BY LEAST(first_col, second_col);

a b
f c
d e

#3

Try

试一试

ORDER BY CONCAT(b_name, l_name)

or (if your fields are NULL when EMPTY)

或者(如果字段为空时为空)

ORDER BY COALESCE(b_name, l_name)

#4

As they say above, UNION ALL is your friend, and, of course, if you have only one table, you can always do this:

正如上面所说的，工会是你的朋友，当然，如果你只有一张桌子，你总是可以做到:

(SELECT field1 AS name FROM TABLE1) 
UNION ALL 
(SELECT field2 AS name FROM TABLE1) 
ORDER BY name DESC

So, you are asking for two diferent rows in the same table, and ordering it as it was one.

所以，你要求在同一个表中有两个不同的行，并将它排序为1。

#5

Thanks for all your help guys, but none of your answers allowed me to sort the data AND echo it into the HTML table correctly once sorted. A UNION might have worked, but I think my solution was faster as far as figuring it all out goes.

谢谢大家的帮助，但是没有一个答案允许我对数据进行排序，并在排序后将其正确地返回到HTML表中。一个工会可能会起作用，但我认为我的解决方案在解决所有问题的过程中速度更快。

                $query = "SELECT c_id, b_name, l_name, f_name, phone FROM customers";
                $result = mysql_query($query);
                mysql_close($link);

                $num = mysql_num_rows($result);

                for ($i = 0; $i < $num; $i++){
                    $row = mysql_fetch_array($result);

                    if($row[b_name]!=''){
                        $new_result[$i]['c_id'] = $row[c_id];
                        $new_result[$i]['c_name'] = $row[b_name];
                        $new_result[$i]['phone'] = $row[phone];
                    }

                    else{
                        $new_result[$i]['c_id'] = $row[c_id];
                        $new_result[$i]['c_name'] = $row[l_name].", ".$row[f_name];
                        $new_result[$i]['phone'] = $row[phone];                         
                    }
                }                       

                foreach ($new_result as $key => $row) {
                   $c_id[$key]  = $row['c_id'];
                   $c_name[$key] = $row['c_name'];
                   $phone[$key] = $row['phone'];
                }

                array_multisort($c_name, SORT_ASC, $c_id, SORT_ASC, $new_result);   

                for ($i = 0; $i < $num; $i++){

                    $class = (($i % 2) == 0) ? "table_odd_row" : "table_even_row";

                    echo "<tr class=".$class.">";

                        echo "<td><a href=Edit_Customer.php?c_id=".$new_result[$i]['c_id'].">".$new_result[$i]['c_id']."</a></td>";
                        echo "<td><a href=Edit_Customer.php?c_id=".$new_result[$i]['c_id'].">".$new_result[$i]['c_name']."</a></td>";
                        echo "<td><a href=Edit_Customer.php?c_id=".$new_result[$i]['c_id'].">".$new_result[$i]['phone']."</a></td>";

                    echo "</tr>";                       

                }

            ?>      

        </table>

#1