Displaying data on the screen using Inner Join

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
webphotogeek
Forum Newbie
Posts: 18
Joined: Sun Jul 04, 2010 12:11 pm

Displaying data on the screen using Inner Join

Post by webphotogeek »

I have this code which searchs for all items not delivered by the date given. When it finds it, it displays the info on the screen, ordered by city and time.
I'm using INNER JOIN to combine 2 tables. One table (purchasing) has the item info and the other (general) has the name, address info. The script works fine.
The problem is - I can easily display the purchasing info, but how can I display the address info (from the general table), which I want to do each time there is a new cust_id.
Any help would be appreciated!

$Res_pur = "SELECT * FROM Purchasing INNER JOIN general ON general.ID = Purchasing.ID WHERE Date = '$date' AND Status = 'Undelivered' ORDER BY general.City, Purchasing.AMPM ";
$Results_pur = mysql_query($Res_pur)or die('Could Not Access Purchasing Table: ' . mysql_error());
while ($Results_pur_Arr = mysql_fetch_array($Results_pur)) {

/* Display the info (from purchasing table) */
echo '<tr><td>'.$Results_pur_Arr['ID'].'</td><td>'.$Results_pur_Arr['Date'].'</td><td>'.$Results_pur_Arr['AMPM'].'</td><td>'.$Results_pur_Arr['Item'].'</td><td>'.$Results_pur_Arr['Description'].'</td><td>'. $Results_pur_Arr['Total'].'</td><td>'.$Results_pur_Arr['Status'].'</td><td>'.$Results_pur_Arr['Comments'].'</td></tr>';

}
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Displaying data on the screen using Inner Join

Post by mikosiko »

that should be an easy fix because you are joining both tables already, you just have to modify this select

Code: Select all

$Res_pur = "SELECT * FROM Purchasing INNER JOIN general ON general.ID = Purchasing.ID WHERE Date = '$date' AND Status = 'Undelivered' ORDER BY general.City, Purchasing.AMPM ";
for something like this

Code: Select all

$Res_pur = "SELECT Purchasing.*, general.<replace for any field that you want> FROM Purchasing INNER JOIN general ON general.ID = Purchasing.ID WHERE Date = '$date' AND Status = 'Undelivered' ORDER BY general.City, Purchasing.AMPM ";
and after that just use the added field(s) in the same way that you are doing now i.e

Code: Select all

$Results_pur_Arr['the field that you selected']
webphotogeek
Forum Newbie
Posts: 18
Joined: Sun Jul 04, 2010 12:11 pm

Re: Displaying data on the screen using Inner Join

Post by webphotogeek »

Wow, that was an easy fix. Must have been too early in the morning for me. Next time, I'll grab some coffee before I ask :D

Thanks
Post Reply