Page 1 of 1

sorting names

Posted: Tue Dec 11, 2007 5:52 pm
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

Posted: Tue Dec 11, 2007 6:38 pm
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

Posted: Tue Dec 11, 2007 6:39 pm
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.

Posted: Tue Dec 11, 2007 7:31 pm
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

Posted: Wed Dec 12, 2007 8:29 am
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?

Posted: Wed Dec 12, 2007 8:49 am
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.

Posted: Wed Dec 12, 2007 10:26 am
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.

Posted: Wed Dec 12, 2007 5:50 pm
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.

Posted: Thu Dec 13, 2007 10:01 pm
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.