• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

PHP help to sort table by name using only client id!

Hello,

Basically what I need to do is SORT a table by Client Surname then Client Firstname which is easy enough.

However I am trying to sort records of sales and the only link to the customer is by id. For example my table is similar to:

sale_id | client_id1 | client_id2 | sale_date | sale_type | sale_net | sale_total

and 2 dummy records are

1 | 77 | NULL | 20070105 | Phone | 77.81 | 86.99
2 | 89 | 90 | 20070207 | Store | 532.05 | 599.99

And obviously the client records that refer to 77,89,90 all hold detailed information about each client including firstname, suranme etc.  So how can i sort the sales (including the firstname and surname) and sort by them?

Cheers,
0
drews1f
Asked:
drews1f
  • 5
  • 4
1 Solution
 
steelseth12Commented:
You will have to do a JOIN of the two tables
0
 
drews1fAuthor Commented:
how would i do this? :o
0
 
steelseth12Commented:
SELECT a.* FROM salestable a LEFT JOIN customertable b ON a.client_id=b.client_id ORDER BY b.Clientsurname,bClientfirstname
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
drews1fAuthor Commented:
Hello,

I have tried this but it doesnt work although i think it should? Can you do 2 LEFT JOINS?

Also is there anyway to echo to the screen the mechanics of what is happening behind the select statement so i can maybe see where its falling down?  Any help appreciated.  Here is my current code:



$queryx = 'SELECT s1.sale_id AS s1_sale_id, s1.sale_client_no AS s1_sale_client_no, s1.sale_id1 AS s1_sale_id1, s1.sale_id2 AS s1_sale_id2, c1.client_name AS c1_client_name, c2.client_name AS c2_client_name, c1.client_surname AS c1_client_surname, c2.client_surname AS c2_client_surname, c1.client_rel_id AS c1_client_rel_id'
            . ' FROM sale s1'
            . ' LEFT JOIN client c1 ON c1.client_id = s1_sale_id1'
            . ' LEFT JOIN client c2 ON c2.client_id = s2_sale_id1'
            . ' WHERE s1.sale_in_progress = "Yes"'
            . ' ORDER BY c1.client_surname, c1.client_name';

            $resultsx = mysql_query($queryx);
            while ($rowx = mysql_fetch_assoc($resultsx)) {


          echo $rowx['client_id'];


            }
0
 
drews1fAuthor Commented:
sorry the echo is wrong. I have also tried:

echo $rowx['c1_client_name'];

and

echo $rowx['s1_sale_id1'];

Neither pritned anythign to the screen :(
0
 
steelseth12Commented:
Can you explain the logic of your code as i dont understand what you are trying to do .
0
 
drews1fAuthor Commented:
Im trying to display a list of recent sales in the following format:

Date | Client(s) | Adviser | Product(s)
eg

11/02/2007 | Joe Bloggs & Mary Bloggs | Mark Clark | Mortgage

At the moment the headers date and adviser are links which reload the page with a different SELECT statement to order the data from the table by: sale_date or sale_adviser.

However I have no fields called sale_firstname and sale_surname. I only ahve the field sale_id and for obvious reason i cannot order the table by this as it will not be alphabetical.

Therefore I wish to the merge the client table with the sale table so firstname and surname can be displayed in the outputted list.

It becomes more tricky because products can be sold to COUPLES as well as single clients.  Hope that clears it up for you :)
0
 
drews1fAuthor Commented:
i think ive figured it out. It was a case of changing this :

. ' LEFT JOIN client c1 ON c1.client_id = s1_sale_id1'

to:

. ' LEFT JOIN client c1 ON c1.client_id = s1.sale_id1'


The c1.client_id was supposed to equal s1.sale_id1 and NOT s1________sale_id1

0
 
steelseth12Commented:
$queryx = 'SELECT s1.sale_id AS s1_sale_id, s1.sale_client_no AS s1_sale_client_no, s1.sale_id1 AS s1_sale_id1, s1.sale_id2 AS s1_sale_id2, c1.client_name AS c1_client_name, c1.client_surname AS c1_client_surname c1.client_rel_id AS c1_client_rel_id'
            . ' FROM sale s1'
            . ' LEFT JOIN client c1 ON c1.client_id = s1.client_id' // i assume your foreign key is client_id
            . ' WHERE s1.sale_in_progress = "Yes"'
            . ' ORDER BY c1.client_surname, c1.client_name';

            $resultsx = mysql_query($queryx);
            while ($rowx = mysql_fetch_assoc($resultsx)) {


          echo $rowx['c1_client_name'];


            }
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now