Order By

[Manny]

I run a football (soccer) website and want to display a squad list. Each squad member has a squad number, with the exception of one or two. These squad numbers run from 1-99, though the players who do not have a squad number have this set to zero. As I print my results in PHP, these squad numbers are printed to the screen but I do not want to display the squad number of any player who has it set to zero. I know I could use two queries, such as the ones below:

SELECT * FROM `players` WHERE `squad_number` != '0' ORDER BY `squad_number` ASC

SELECT * FROM `players` WHERE `squad_number` = '0'

The queries above would allow me to display the players in order of squad number, with those set to zero being displayed at the bottom. However, I was wondering if it would be possible to combine these queries into one? I have tried using UNION but this throws up an error because of the ORDER BY clause.

[Synook]

Why don't you just have the squad-number-checking logic in PHP?

if ($data['squad_number'] != 0) echo $data['squad_number'];

or something.

[Krewe]

What synook said would work well. And if you wanted to put the numbers + names + other info together just make it into a table.

$query = "SELECT * FROM table";$result_set = ($query, $connection);while($data = mysql_fetch_array($result_set)){<table>if ($data['squad_number'] != 0){echo "<tr><td>{$data['squad_number']}</td><td>{$data['name']}</td></tr>"}else{echo "<tr><td>{$data['squad_number']}</td><td>{$data['name']}</td></tr>"}}</table>

That should work I think.

[Manny]

That is my current solution but as I ordered the numbers in ascending order, those who have it set to zero appear at the top of the table and I want them to appear at the bottom. The PHP code you both posted above is pretty much what I already have to omit the zero values from the table and just print the player's name.

[Krewe]

Then just make that else statement an if checking if it IS equal to 0.

$query = "SELECT * FROM table";$result_set = ($query, $connection);while($data = mysql_fetch_array($result_set)){<table>if ($data['squad_number'] != 0){echo "<tr><td>{$data['squad_number']}</td><td>{$data['name']}</td></tr>"}if($data['squad_number'] == 0){echo "<tr><td>{$data['squad_number']}</td><td>{$data['name']}</td></tr>"}}</table>

That would give you what you described