Solved

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

Posted on 2007-03-28
11
185 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
WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
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.

630 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