PHP and my SQL Problem

[skoobi]

Hi ive just started to learn mysql and im stuck!!!

 

The problem i have is im trying to display 2 tables in a form... BUT the 2 tables are the User information and the 2nd table is the order information.

What i want to do is match the order_id of the 2 tables so it displays the customer information and the order status for that customer.

Most of the information is in the Customer information but there is one field i want to extract from the other table which is the Order status.....

 

Ive managed to connect to the databse and ive managed to dislay all the Customer information that i wanted but for some reason i cannot connect to the other table and display the order status.

 

Heres the code as it is now.

 

<?php

//Database Connection
$con = mysql_connect("localhost","user","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("celticbl_test",$con);

// The '$result', is just a variable, so you don't have to type the function again
$result = mysql_query("SELECT * FROM jos_vm_order_user_info ORDER BY order_id");
$result_item = mysql_query("SELECT * FROM jos_vm_order_item ORDER BY order_id");


// HTML table to display the user information
echo "<table border='1'>
<tr>
<td>Order ID</td>
<td>Last Name</td>
<td>First Name</td>
<td>Email</td>
<td>Post Code</td>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['order_id'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['user_email'] . "</td>";
  echo "<td>" . $row['zip'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

 

What do i need to do to display the order status under the 'zip' section???

The table for the customer information is 'jos_vm_order_user_info' and the location of the order status is 'jos_vm_order_item' ...

 

Also im not sure if i can do it or not but i would like it to only display the complete orders. I.e when the order status comes back as complete only that one shows not the processing ones...

 

Any help or information would be greatful...

 

Thanks in advance...

 

Chris

[chmpdog]

If I understood you correctly, I would recommend writing the the results from the table into a array.

Try this code after the query:

$order=mysql_fetch_array($result, MYSQL_ASSOC);
   echo $order['order_id'];

 

 

[Infinitive]

OK well I don't want to go too basic on you but you said you're new.

 

So first of all, when you want to match information between two different tables, you typically use a JOIN.  Something like

"SELECT *
FROM jos_vm_order_user_info
  INNER JOIN jos_vm_order_item ON jos_vm_order_item.order_id = jos_vm_order_user_info.order_id"

And then if you want them all you would add

"ORDER BY order_id"

or if you wanted just this customer you would use

"WHERE order_id = " . $Customer_Order_ID

 

Then what chmpdog said is good advice.  Use mysql_fetch_array() or mysql_fetch_assoc().  If you only retrieve the one order, then you can just use

$order = mysql_fetch_assoc($result);
echo "Text leading up to it, perhaps a table";
echo $order['order_id'];
echo "more text";
echo $order['next column name'];

etc.

 

If you get back multiple rows, you'll want to put it all in a while loop starting with

 

while ($order = mysql_fetch_assoc($result)) {
some code
}