Posted by usenet on 10/01/05 04:52

Using:
- MySql v3.23
- PHP 4.2.2 (soon to be upgraded to 4.3)

I need to perform what I'm calling a "conditional sort," and can't figure out
how to do it despite lots of time spent with Google, the documentation, and the
group archive. I'm sure there's a way to do this, but I suspect that I'm not
looking in the correct direction.

I need to sort data from a query based on a combination of two fields in a
single table.

+------------+---------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+------------+-------+
| name1 | varchar(50) | YES | | NULL | |
| name2 | varchar(50) | | | | |
| fieldx | tinyint(2) | | | 0 | |
+------------+---------------+------+-----+------------+-------+

name1 sometimes has a value, or is NULL.
name2 always has a value.
fieldx always has a value.

What I need:
- Select records where fieldx = 1.
- If name1 has a value, that record should be sorted within the result on name1.
- If name1 is NULL, that record should be sorted within the result on name2.

And that's where I'm going in circles.

Sample code:
------------
$query = "SELECT * FROM mydatabase ";
$query .= "WHERE fieldx = 1 ";
$query .= "ORDER BY name1, name2";

$result = mysql_query($query)
or die("Query failed: " . mysql_error());

while ($row = mysql_fetch_object($result, MYSQL_ASSOC)) {
if (!is_null($row->name1)) {
echo $row->name1 . "<br />";
} else {
echo $row->name2 . "<br />";
}
}
------------

Naturally, that gets me output that looks approximately like:
------------
a [echoing name1]
..
..
z
a [echoing name2]
..
..
z
------------

What I need is for all of the records to be listed alphabetically, regardless of
whether name1 or name2 is the field being displayed by PHP, i.e.:
------------
a
..
..
z
------------

I've tried to find a solution with SELECT, and also by sorting the $result
object. No luck so far.

Any and all advice will be greatly appreciated. I'm looking forward to someone
making me feel like a fool by pointing out something I've failed to consider.
<g>

Thanks much.

[Back to original message]