|
|
Posted by Bob Stearns on 10/01/05 06:21
usenet@isotopeREEMOOVEmedia.com wrote:
> 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.
ORDER BY COALESCE(name1, name2)
Navigation:
[Reply to this message]
|