note 25670 modified in function.array-multisort by vrana

more on SQL ORDER BY statements when NULL and empty values are involved The problem I ran into tonight was this: I have one column that is sometimes empty, sometimes NULL, and sometimes has a value. When I try to sort on that column it lists all the NULLs (sorted on the second column), all the empties (sorted on the second column), finally all the values (sorted second on the second column). This is technically right, but it's not what I wanted. I wanted NULL+empty, then values. Here's the work around that I used: The mysql_query is collected in a separate function and is also not really relevant to the problem here... it does have ORDER BY in it, but as I mentioned, it wasn't sufficient to solve the NULL+empty problem. <?php foreach ($all_res as $rowsreturned) { $orderby_col[] = $rowsreturned["col_to_order_first"]; $orderby_name[] = $rowsreturned["col_to_order_second"]; } array_multisort($orderby_col, $orderby_name, $all_res); ?> NOTES: - You actually want the full array LAST in the multisort, not first. As the documentation says, the first array is the first value you want to sort on. i.e. 1) sort on the first column 2) sort on the second column 3) throw everything else in too. - If you wanted to sort on more columns you would just add them into the foreach loop that collects the column names. --was-- more on SQL ORDER BY statements when NULL and empty values are involved The problem I ran into tonight was this: I have one column that is sometimes empty, sometimes NULL, and sometimes has a value. When I try to sort on that column it lists all the NULLs (sorted on the second column), all the empties (sorted on the second column), finally all the values (sorted second on the second column). This is technically right, but it's not what I wanted. I wanted NULL+empty, then values. Here's the work around that I used: The mysql_query is collected in a separate function and is also not really relevant to the problem here... it does have ORDER BY in it, but as I mentioned, it wasn't sufficient to solve the NULL+empty problem. foreach ($all_res as $rowsreturned) { $orderby_col[] = $rowsreturned["col_to_order_first"]; $orderby_name[] = $rowsreturned["col_to_order_second"]; } array_multisort($orderby_col, $orderby_name, $all_res); NOTES: - You actually want the full array LAST in the multisort, not first. As the documentation says, the first array is the first value you want to sort on. i.e. 1) sort on the first column 2) sort on the second column 3) throw everything else in too. - If you wanted to sort on more columns you would just add them into the foreach loop that collects the column names. http://php.net/manual/en/function.array-multisort.php