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,
drews1fAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.