PHP/MySQL beginner help

Hi,

Basically, I have this little tiny code I want to make. I want to be able to match a catalog number (from order_item) to the name of the corresponding catalog item (in Food). Here's the layout of my db:

joe_onlineorders
   Customer_Order   *no relevance
   Food
   order_item

Code (markup):

order_item is like this:

1) order_number, which tells the number of the order (auto_increment)
2) item_number, which is the ordinal number of their item....
blah blah blah
3) catalog_number, which is the number corresponding to the catalog_number in the Food table.

Food is like this:

1) catalog_number, which is just an auto-incrementing thing that says the number
2) name, which is the name of the item
** blah blah blah and nothing else matters

Basically, I want to just be able to match up the catalog_number from the order_item table with the catalog_number in the Food table, and then echo the name of the item.

tia,
Joe

 

What do you want to know, the SQL query that will return the information you want or the PHP code to run the query?

 

you need a subquery for that. Something like this:

SELECT name FROM food WHERE catalog_number = (SELECT catalog_number FROM order_item)

This should work

 

Thanks so much!
Now, I was wondering... well... sort of embarrassing, but how would I:

1) echo the name, and
2) there's going to be more than 1 order item most likely, so how would I cycle through and echo each name of each item ordered??

Also, I modified your code that you gave me a little (THANK YOU for that again, by the way), so would this work? :

SELECT name FROM food WHERE catalog_number = (SELECT catalog_number FROM order_item WHERE order_number = $_SESSION['order_number'])

PHP:

tia,
-joe

 

$query = SELECT name FROM food WHERE catalog_number = (SELECT catalog_number FROM order_item WHERE order_number = $_SESSION['order_number']);

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['name'];
echo "<br>";
}

 

yes that should work. And YIAM's solution is the answer for your other question. The query saves the variables in an array, so you have to cycle through each field of the array with a while loop.

 

THANK YOU GUYS SO MUCH!!!

How do I give you "thank-you points" or whatever?

 

Uh, slight problem...

It's giving me this error now:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT catalog_number FROM order_item WHERE order_number = )' a

Code (markup):

Here's my loop:

$query = 'SELECT * FROM order_item WHERE order_number = "' . mysql_real_escape_string($_SESSION['order_number']) . '"';

$result = mysql_query($query)
		or die("sql_email: ".mysql_error());

$numrows = mysql_num_rows($result);

if ($numrows) {
	$itemsmessage = "You ordered:\n\n";
	while ($row = mysql_fetch_assoc($result)) {
		
		$_SESSION['order_number'] = $ordernumber;
		
		
		$query = 'SELECT name FROM Food WHERE catalog_number = (SELECT catalog_number FROM order_item WHERE order_number = "' . mysql_real_escape_string($_SESSION['order_number']) . '")';

		$result = mysql_query($query)
			or die("sql_email: ".mysql_error());
		
		while ($row = mysql_fetch_array($result)){
			$blah = $row['name'];
			echo $row['name'];
			echo "<br>";
		}

		$itemsmessage = $itemsmessage . "Item number " . $row['item_number'] . ":" . " Item name: " . $blah . " qty: " . $row['quantity'] . "\n";
	}
};

PHP:

Help please!!!!

--Joe

 

your second query:

$query = 'SELECT name FROM Food WHERE catalog_number = (SELECT catalog_number FROM order_item WHERE order_number = "' . mysql_real_escape_string($_SESSION['order_number']) . '")';

Change to:

$query = 'SELECT name FROM Food WHERE catalog_number = (SELECT catalog_number FROM order_item WHERE order_number = "' . mysql_real_escape_string($_SESSION['order_number']) . ')"';


You have to change ") in )", the sql error gives that information read it well. You can give reputation points by clicking the scale on the right of a post, but you need 50 posts for that