[SOLVED] creating invoice

[jakebur01]

Ok, I am trying to join 3 tables together to retrieve information to display on an invoice. I am new to joining tables and I keep confusing myself.

 

Heres what I've got:

 

customers table - has customerid, name, address, city, state, zip, and country

 

orders table - has orderid, customerid, amount, date, order_status, ship_name, ship_address, ship_city, ship_state, ship_zip, ship_country, c_phone, c_email, card_type, card_number, card_month, card_year, card_name

 

order_items table - has orderid, isbn, item_price, and quanity

 

 

Here's what i'm trying to do:

 

display address

 

display shipping address

 

display orders

 

display credit card information

 

Thank you,

 

`Jake

[alby]

Hi,

 

Not sure what you  mean by 'join the tables together'.  I'd have thought what you want to do is retrieve your order details from the order table first and then use the orderid and customerid fields to retrieve the data from the order_items and customers tables.

[jakebur01]

Whatever would work best? I don't know exactly what I need to do, I just know what I want to do and I don't know exactly how to get it done.

[jakebur01]

so would i do three seperate querys under one connection?

 

Like:

$conn = db_connect();

//then

$query = select * from customers where

//then do a second query??

$query = select * from orders where

//and a third??

$query = select * from order_items

//??? I'm not sure exactly how to separate them???

[alby]

Yeah it would go

 

$query = "SELECT * FROM orders WHERE whatever";

 

You'd then create variables from this select like

 

$order_id = $row['order_id'];
$amount = $row['amount'];

$query = "SELECT * FROM order_items WHERE orderid = '$order_id'";

 

And so on...

 

 

[jakebur01]

What do I do when I am retrieving multible rows, like from the order items table where I have 5 rows where the orderid column equals 14 ?

 

How do I store those rows into variables?

 

`Jake

[per1os]

<?php
$query = "SELECT * FROM orders WHERE whatever";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
     $rows = $row;
}

echo '<pre>' . print_r($rows) . '</pre>';

?>

[jakebur01]

This is what it returned. It's all jumbled up.

 

Array ( [orderid] => 15 [isbn] => C100016 [item_price] => 50.37 [quantity] => 1 )

1

[per1os]

Not jumbled up, that is the structure of the arrays when printed.  But I now see a flaw in the logic I gave you, try this instead:

 

<?php
$query = "SELECT * FROM orders WHERE whatever";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
     $rows[] = $row; // added the [] =) my bad.
}

echo '<pre>' . print_r($rows) . '</pre>';

?>

[jakebur01]

ok ok, i see

 

now it returns this

 

Array ( [0] => Array ( [orderid] => 14 [isbn] => 30-015 [item_price] => 15.82 [quantity] => 1 ) )

1

 

with this code:

 

$query = "SELECT * FROM order_items WHERE orderid = '$coolid'";
$result = mysql_query($query, $db) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
   $rows[] = $row; 

}


echo '<pre>' . print_r($rows) . '</pre>';

[per1os]

That should answer the question

 

What do I do when I am retrieving multible rows, like from the order items table where I have 5 rows where the orderid column equals 14 ?

 

Should it not? If there were multiple rows in that table with that id it would have been returned in that array.

[jakebur01]

Thank you!

 

But now how to I retrieve the rows from the array and echo them onto the page from here?

[jakebur01]

for ($i = 0; $i<3; $i++ )
echo "$rows[$i] ";

 

I tried this. All I got back was:

Array Array

 

[chigley]

<?php

$i = 1;

foreach($array as $row) {
foreach($row as $fieldname => $value) {
  "Row $i";
  echo "$fieldname = $value<br />";
}
$i++;
}

?>

[jakebur01]

I'm sorry, I'm a little slow.

 

How do I implement that in with this following code?

 

$query = "SELECT * FROM order_items WHERE orderid = '$coolid'";
$result = mysql_query($query, $db) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
   $rows[] = $row; 

}

[chigley]

<?php

$query = "SELECT * FROM order_items WHERE orderid = '$coolid'";
$result = mysql_query($query, $db) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
   $rows[] = $row;
}

$i = 1;

foreach($rows as $rowid => $rowarray) {
echo "<b>---- Row $rowid ----</b><br />\n\n";
foreach($rowarray as $rowfield => $rowvalue) {
  echo " $rowfield = $rowvalue<br />\n";
}
}

?>

[jakebur01]

Cool.  Thank you.