Page 1 of 1

Displaying data on the screen using Inner Join

Posted: Fri Sep 03, 2010 9:21 am
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>';

}

Re: Displaying data on the screen using Inner Join

Posted: Fri Sep 03, 2010 10:24 am
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']

Re: Displaying data on the screen using Inner Join

Posted: Fri Sep 03, 2010 12:50 pm
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