Solved

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

Posted on 2007-03-28
11
176 Views
Last Modified: 2013-12-13
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
Comment
Question by:drews1f
  • 5
  • 4
11 Comments
 
LVL 20

Expert Comment

by:steelseth12
ID: 18806745
You will have to do a JOIN of the two tables
0
 

Author Comment

by:drews1f
ID: 18806757
how would i do this? :o
0
 
LVL 20

Expert Comment

by:steelseth12
ID: 18806773
SELECT a.* FROM salestable a LEFT JOIN customertable b ON a.client_id=b.client_id ORDER BY b.Clientsurname,bClientfirstname
0
 

Author Comment

by:drews1f
ID: 18807061
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
 

Author Comment

by:drews1f
ID: 18807067
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 20

Expert Comment

by:steelseth12
ID: 18807076
Can you explain the logic of your code as i dont understand what you are trying to do .
0
 

Author Comment

by:drews1f
ID: 18807101
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
 

Author Comment

by:drews1f
ID: 18807129
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
 
LVL 20

Accepted Solution

by:
steelseth12 earned 500 total points
ID: 18807601
$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
 
LVL 1

Expert Comment

by:Computer101
ID: 21291953
Forced accept.

Computer101
EE Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now