使用ORDER BY或PHP排序功能对MySQL查询进行排序 [英] Sorting a MySQL query with ORDER BY or with PHP sort functions

问题描述

我有一个查询，我想按字母顺序进行排序，但是诀窍在于我希望排序能够平等地对待两列.例如，如果first_col的第一行等于apple，而second_col的第二行等于aardvark，我希望将second_col的第二行中的值列在之前 first_col第一行中的值. second_col的每一行中将始终存在一个值(不是NULL或'')，但是first_col中的值可以是''.希望我已经解释得足够好了.我不在乎是否必须为此使用MySQL或PHP，但是一旦排序，该数组将被读取并回显到HTML表中.有什么想法吗?

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?

编辑

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

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>

推荐答案

感谢您的所有帮助人员，但是您的回答都没有让我对数据进行排序，并在排序后将其正确地回送到HTML表中. UNION可能有用，但是我认为我的解决方案要想尽办法就可以了.

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>

这篇关于使用ORDER BY或PHP排序功能对MySQL查询进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！