Sorting a MySQL query with ORDER BY or with PHP so

问题:

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?

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.

                $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>

回答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

(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 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.

回答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.

                $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>