PHP SQL - Conditional sort of mulitple columns?

Author

Conditional sort of mulitple columns?

usenet@isotopeREEMOOVEmedia.com

2005-09-30, 9:56 pm

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.

Bob Stearns

2005-10-01, 3:55 am

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)

usenet@isotopeREEMOOVEmedia.com

2005-10-01, 6:56 pm

On Fri, 30 Sep 2005 23:21:36 -0400, Bob Stearns <rstearns1241@charter.net>
wrote:

>usenet@isotopeREEMOOVEmedia.com wrote:
>
>ORDER BY COALESCE(name1, name2)

Bob, your check is in the mail. <g> Thanks so much; I never took notice of
that option before.

And as expected, I wasn't looking at the right part of the documentation. But I
sure did learn a lot along the way!

Thanks again.

Sponsored Links