[SOLVED] multi table query

[bhavin_85]

hi guys

 

 

im trying to create the tables so that my users can see their invoices online. Each item is generic hence the item table, the price is entered manually by the user

 

i have created the following tables:

Invoices: invoice_id(*), cust_id and invoice_no

Item: item_id(*), item_name

Invoice_items: invoice_item_id(*),invoices_invoice_no,item.item_no, description, weight, price

 

I need to create a query that will lift the following information:

cust_id, invoice_no, then for each item purchased show the item_name, description, weight and price

 

how do i get a query to get information from each table? is it possible in just 1 query? the main identifier will be the cust_id which is already set as a session variable and is avaialble on the page.

 

you help is much appreciated

 

Bhav

[nloding]

I'm new to multi-tables queries myself and haven't totally gotten the hang of it, but you want to look into the SQL command JOIN.  You join tables based on their keys, and can access data across them.

[Barand]

[pre]

Invoices:                Invoice_items:              Item:

 

invoice_id(*)            invoice_item_id(*)    +---- item_id(*)

cust_id        +------  invoices_invoice_no    |    item_name

invoice_no  ----+        item_item_no      ----+

                        description

                        weight

                        price

[/pre]

 

SELECT a.cust_id, a.invoice_no, c.item_name, b.description, b.weight, b.price
FROM invoices a
    INNER JOIN invoice_items b ON a.invoice_no = b.invoices_invoice_no
    INNER JOIN item c ON b.item_item_no = c.item_id
WHERE a.cust_id = '$custId'

[bhavin_85]

hi barand

 

thanks for that code

 

still having some trouble though

 

Error:

 

Unknown column 'b.invoices_invoice_no' in 'on clause'

 

ive checked and that field exists so any ideas?

[Barand]

..... so any ideas?

 

Not without being able to see what you can see. Namely the exact query you are executing and the structure of your tables.

[jcbarr]

First thing to check is if you have a column named invoices_invoice_no in table b. Check your spelling and case too to make sure everything matches up. You would be suprised what little typos can do to your code :-)

[bhavin_85]

i just realised i messed the tables up a bit...oppps

 

Invoices: invoice_id(*), cust_id

Item: item_id(*), item_name

Invoice_items: invoice_item_id(*),invoices.invoice_id,item.item_no, description, weight, price

 

so that translates into

 

 

Invoices:                Invoice_items:              Item:

 

invoice_id(*)--+        invoice_item_id(*)    +---- item_id(*)

cust_id          +----  invoices.invoice_id    |    item_name

                              item.item_id      ----+

                            description

                            weight

                            price

 

so would that translate into this code?

$sql="SELECT a.cust_id, a.invoice_id, c.item_name, b.description, b.weight, b.price
FROM invoices a
    INNER JOIN invoice_items b ON a.invoice_id = b.invoices.invoice_id
    INNER JOIN item c ON b.item_item_no = c.item_id
WHERE a.cust_id = '$custId'";

 

now im getting error:

Unknown column 'b.invoices.invoice_id' in 'on clause'

[Barand]

You can't have a "." in a column name.

[bhavin_85]

ahh right sorted

nother totally n00b question is how do i print these varaibles? ive only used session variables so far, not sure how u normally print the data from the row

[bhavin_85]

ok so i figured out that echo should do print the data...

 

still getting an error though

<?
session_start();
if ( empty($_SESSION['username'])){
header("location:default.php");
exit;
}
$id=$_SESSION['cust_id'];

include ('config.php');

$sql=$sql="SELECT a.cust_id, a.invoice_id, c.item_name, b.description, b.weight, b.price
FROM invoices a
    INNER JOIN invoice_items b ON a.invoice_id = b.invoices_invoice_id
    INNER JOIN item c ON b.item_item_id = c.item_id
WHERE a.cust_id = '$id'";
$query=mysql_query($sql) or die(mysql_error());
while (mysql_num_rows($query) != 0) { 
$row = mysql_fetch_assoc($query);
echo $row["cust_id"];
echo $row["invoice_id"];
}

 

error: 4141

Fatal error: Maximum execution time of 60 seconds exceeded in C:\Program Files\xampp\htdocs\PBL\invoice.php on line 21

 

line 21 is the last line of code pasted - the }

 

its 3 in the morning and i cant figure it out  ???

[corbin]

while (mysql_num_rows($query) != 0) {  is the entirely wrong way of doing it (no offense intended).

 

Unless reset, the number of rows will stay the same....

 

Change that line to while($row = mysql_fetch_assoc($query)) {

 

If you don't know what that does, look it up on Google or the PHP manual.

[bhavin_85]

i tried that before and i didntget it 2 work  ??? but all sorted now

 

heres the code if any1 wants it

 

<?
session_start();
if ( empty($_SESSION['username'])){
header("location:default.php");
exit;
}
$id=$_SESSION['cust_id'];

include ('config.php');

$sql=$sql="SELECT a.cust_id, a.invoice_id, c.item_name, b.description, b.weight, b.price, b.date
FROM invoices a
    INNER JOIN invoice_items b ON a.invoice_id = b.invoices_invoice_id
    INNER JOIN item c ON b.item_item_id = c.item_id
WHERE a.cust_id = '$id'";
$query=mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($query)) {
$_SESSION['cust_id'] = $row["cust_id"];
$_SESSION['invoice_id'] = $row["invoice_id"];
$_SESSION['item_name'] = $row["item_name"];
$_SESSION['description'] = $row["description"];
$_SESSION['weight'] = $row["weight"];
$_SESSION['price'] = $row["price"];
$_SESSION['date'] = $row["date"];
}
?>

[nloding]

Out of curiosity, wouldn't it be better to put the description, weight, and price under the Item table, otherwise you're not normalized ... description and weight have nothing to do with the primary key of invoice_items ...

 

It would change your query slightly, but you'd eliminate a lot of redundant data in the database ... listing the description for an item only once instead of X number of times in the invoices.

 

Just another thought to throw at you ...

[bhavin_85]

good suggestion nloding....but the price, weight and desc cant be put in the item table because the item table only hold the main categories ie ring, watch, necklace, they are a gerneralised category....where as when the invoice is created that category is used and the user manually enters the details...ie price, weight and desc

 

cheers