Solved

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

Posted on 2007-03-28
11
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

738 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