sorting names

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

sorting names

Post by speedy33417 »

I have a page where the user can sort client names from the database.
My problem is that these names are stored in different columns. There are residential customers and commercial customers.
All data is in the same table as follows:

Residential customers
cust_firstname and cust_lastname

Commercial customers
cust_companyname

I have the sort page done and working if it only needs to sort either residential names or commercial names. The problem is when they all need to be sorted in the same list.
For example I have these four clients
John Smith (residential)
Bruce Willis (residential)
ABC Distributing (commercial)
Microsoft (commercial)

Then when sorted it should be
BY FIRST NAME
ABC Distributing, Bruce Willis, John Smith, Microsoft

BY LAST NAME
ABC Distributing, Microsoft, John Smith, Bruce Willis

So I think there's a sort function that re-sorts the value of an array. How is that done on more than one at the same time?
After the data's been pulled from the database it is stored in four arrays as follows:

Code: Select all

clientID[]   clientFirstName[]   clientLastName[]   clientCompanyName[]
12           John                Smith              NULL    
23           Bruce               Willis             NULL
74           NULL                NULL               Microsoft
89           NULL                NULL               ABC Distributing
How can I re-sort them alphabetically where it's based on two columns? Either last name and company name or first name and company name.
My idea would be to move the company name to the first name or last name column based on the sort requirement. Like so:

This is based on ORDER BY FIRST NAME

Code: Select all

clientID[]   clientFirstName[]   clientLastName[]   clientCompanyName[]
12           John                Smith              NULL    
23           Bruce               Willis             NULL
74           Microsoft           NULL               NULL
89           ABC Distributing    NULL               NULL
Once it's done I need to sort the value of clientFirstName array alphabetically and all the other arrays in the same fashion.
Can someone help me figure out how to do that?
Or is it possible to query the database directly to get the results in my arrays already in that order?

Thanks
User avatar
stereofrog
Forum Contributor
Posts: 386
Joined: Mon Dec 04, 2006 6:10 am

Post by stereofrog »

Assuming your DB is mysql, you can try something like

Code: Select all

select
   coalesce(
      concat(cust_firstname, ' ', cust_lastname ), 
      cust_companyname
   ) as cname
...
   order by cname
http://dev.mysql.com/doc/refman/5.0/en/ ... n_coalesce
http://dev.mysql.com/doc/refman/5.0/en/ ... ion_concat
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

I have never tried this, but possibly you could do a Union query, something like:

Code: Select all

SELECT LastName AS x, FirstName, ... FROM tblName AS A
WHERE  CoName=""
UNION SELECT CoName AS x, ... FROM tblName AS B 
WHERE LastName=""
I'm just grasping at straws, here.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I believe this will work for you, provided you can identify residential or commercial based on data in the table. There are other ways of writing the order by clause.

Code: Select all

select
  clientID,
  clientFirstName,
  clientLastName,
  clientCompanyName
from
  customers
where
  clientID > 0
order by
  if (clientType = 'residential', clientFirstName, clientCompanyName) asc
limit
  1000
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post by speedy33417 »

I went with astions' version first, because it seemed like it would be the easiest way to do it.

I need to make a correction, the company name is saved under cust_firstname and cust_lastname is blank in that case.

I have the following query based on astions' suggestion:

Code: Select all

$sql = "SELECT cust_id, cust_firstname, cust_lastname FROM customer ORDER BY IF (cust_type = 'res', cust_lastname, cust_firstname)";
This is supposed to be an ORDER BY last name search for all clinets. Ordering by last name if the customer is residential else ordering by first name.

However once I echo the values they show up in this order
First it echoes the commercial clients by cust_id
Then echoes the residential clients by last name

Any ideas how to correct it?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Can you post the actual results, or similar? Please include the ID, customer type, first name and last name along with the actual query you ran.
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post by speedy33417 »

There's too many results, plus there's sensitive information, so I'll do similar

Code: Select all

+---------+----------------+---------------+-----------+
| cust_id | cust_firstname | cust_lastname | cust_type |
+---------+----------------+---------------+-----------+
|       1 | Bruce          | Willis        | res       |
|       2 | Microsoft      |               | com       |
|       3 | ABC Distribute |               | com       |
|       4 | John           | Smith         | res       |
+---------+----------------+---------------+-----------+
This is what my database looks like. Company name is actually saved as a first name. If the customer is 'com' or commercial, then it means that the last name field is empty and I have to use the first name field when sorting.
It's a nightmare.

This is the query that's supposed to sort all clients by last name:

Code: Select all

$sql = "SELECT cust_id, cust_firstname, cust_lastname FROM customer ORDER BY IF (cust_type = 'res', cust_lastname, cust_firstname)";
It generates these results:

Code: Select all

+---------+----------------+---------------+-----------+
| cust_id | cust_firstname | cust_lastname | cust_type |
+---------+----------------+---------------+-----------+
|       1 | Microsoft      |               | com       |
|       2 | ABC Distribute |               | com       |
|       3 | John           | Smith         | res       |
|       4 | Bruce          | Willis        | res       |
+---------+----------------+---------------+-----------+
There's close to a thousand results on my end, so I'm pretty sure that what the query actually does is dump in all the cust_type = 'com' ordered by cust_id, then adds the residential customers ordered by cust_lastname.

Hope this helps.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Try this..

Code: Select all

SELECT
  c.clientID,
  c.clientFirstName,
  c.clientLastName,
  c.clientCompanyName
FROM
  customers c
ORDER BY
  (SELECT IF(c2.cust_type = 'res', c2.cust_lastname, c2.cust_firstname) from customers c2 where c2.cust_id = c.cust_id LIMIT 1) ASC
I'm thinking there is probably a better way to do this. Off the top of my head I'm not sure why the first query I gave you didn't work. Maybe there are NULL fields in there or something.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

The underlying problem is the design of the table. I'm not sure that I know how I would do it, but what you're struggling with is the ambiguity about either what the table represents, or the appropriate columns. If it's valid to consider that a residential customer is different from a commercial customer, they are really separate entities and should be in separate tables, according to relational database theory. If, in your situation, they are all just customers, then you have to ask yourself what properties every customer has or can have, and there must be a column for every property. Relational database design greatly dislikes putting different kinds of data into the same column! Perhaps there should be 3 columns: Firstname, Lastname, Companyname, and a flag that specifies whether a customer is of the residential or commercial category. That doesn't really solve your sorting issue, I realize, but it is the proper way to think about tables and columns.

Yeah, I know, that's all theoretical. I just thought this may be a good situation in which to bring up the underlying schema design and the issues that arise when the design is flawed. You probably inherited the table design and maybe can't change it now, for practical reasons. But sometimes just "talking through" the design can be helpful in deciding what to do, even if you can't go back and redesign it from scratch.
Post Reply