Shopping Cart problem (warning long post)

Ok need help again but this time with a shopping cart.

I have several mysql tables that I’m wanting to use, I can get one working with no problems (part of the code looks like this and where I can get the data from)

$sql = "SELECT * FROM gameordersmd WHERE id = '".$id."'";
$Result = mysql_db_query ("user", $sql, $Link);
while ($row = mysql_fetch_array($Result)) {

PHP:

Now I’ve tried this bit of code

$sql = "SELECT * FROM gameordersmd, gameordersms WHERE id = '".$id."'";
$Result = mysql_db_query ("user", $sql, $Link);
while ($row = mysql_fetch_array($Result)) {

PHP:

With some sort of success with the items being added to the cart but get the message “Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /web/users/USER/test2/functions.php on line 37” instead of the price of the item, the item description and the quantity.

So I tried this bit of code

$sql = "SELECT * FROM gameordersmd WHERE id = '".$id."'";
$Result = mysql_db_query ("mysqlusername", $sql, $Link);
			
$sql = "SELECT * FROM gameordersms WHERE id = '".$id."'";
$Result = mysql_db_query ("mysqlusername", $sql, $Link);
while ($row = mysql_fetch_array($Result)) {

PHP:

Again with some success as what ever item is added from gameordersms or gameordersmd will add to the cart but the price, the item description and quantity will only show up if something has been selected from the gameordersms table.


I’m just wondering what needs adding or changing, so the the price of the item, the item description and the quantity will show up if selected from either table.


The full code of the functions.php which I seem to be having trouble with can be seen here

<?php

function writeShoppingCart() {
	$cart = $_SESSION['cart'];
	if (!$cart) {
		return '<p>You have no items in your shopping cart</p>';
	} else {
		// Parse the cart session variable
		$items = explode(',',$cart);
		$s = (count($items) > 1) ? 's':'';
		return '<p>You have <a href="cart.php">'.count($items).' item'.$s.' in your shopping cart</a></p>';
	}
}


function showCart() {
	global $db;
	$cart = $_SESSION['cart'];
	if ($cart) {
		$items = explode(',',$cart);
		$contents = array();
		foreach ($items as $item) {
			$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
		}
		
		
	
		$Link = mysql_connect("localhost", "mysqlusername", "mysqlpassword");
		
		$output[] = '<form action="cart.php?action=update" method="post" id="cart">';
		$output[] = '<table>';
		foreach ($contents as $id=>$qty) {
			
			$sql = "SELECT * FROM gameordersmd WHERE id = '".$id."'";
			$Result = mysql_db_query ("mysqlusername ", $sql, $Link);
		
			while ($row = mysql_fetch_array($Result)) {

			$output[] = '<tr>';
			$output[] = '<td><a href="cart.php?action=delete&id='.$id.'" class="r">Remove</a></td>';
			$output[] = '<td>'.$row['title'].' by '.$row['format'].'</td>';
			$output[] = '<td>&pound;'.$row['price'].'</td>';
			$output[] = '<td><input type="text" name="qty'.$row['id'].'" value="'.$qty.'" size="3" maxlength="3" /></td>';
			$output[] = '<td>&pound;'.($row['price'] * $qty).'</td>';
			$total += $row['price'] * $qty;
			$output[] = '</tr>';
			}
		}
		$output[] = '</table>';
		$output[] = '<p>Grand total: <strong>&pound;'.$total.'</strong></p>';
		$output[] = '<div><button type="submit">Update cart</button></div>';
		$output[] = '</form>';
	} else {
		$output[] = '<p>You shopping cart is empty.</p>';
	}
	return join('',$output);
}

?>

PHP:

Any help would be appreciated

Chris

 

If the tables have the same columns, you can do:

select * from table_A where blahblah
union
select * from table_B where blahblah

But it looks to me like you might have failed to normalise your data - though without knowing what "sms" and "smd" mean, I couldn't say for sure.. Maybe a little background reading about relational database theory would be helpful.

 

The 'sms' and 'smd' at the end of gameorder are just table names in mysql, both have the same structure, just have slightly different in data in them in terms of format and price.

I'll give the

"select * from table_A where blahblah
union
select * from table_B where blahblah"

a shot and see if it helps.

thanks for the help

 

Ok, just a tip - if they have the same structure, then they should probably be combined into one table.

 

Yeah I thought of that.

What it is, is, its part of an assessment and we've got to create a fictional e-commerce site, since we have to create a minimum of 10 pages, I thought I would let a 'user' add different products from different pages with-in the site to the cart.

I did manage to create the one table and was able to add multiple items to the shopping cart' but as I've got around 72 items, I thought it's be easier if the items where spread out on other pages (rather than just a long list) as I was able to add images about that product.

I think speaking to my tutor about it may help as he wrote the code we used in one of the lessons, but thanks anyway.

 

Trust me, in the long run you will really wish it was all one table. Just add another column to the table that says which page it belongs on. Then you can:

select * from whatever where page=3;

This way you can reuse the same code for everywhere, and you can perform operations that span items from multiple pages at once.

Having it in multiple tables will spiral out of control quickly and you will end up with an unmanageable database as things grow. If your tutor is not teaching you this, then I hate to say it, but he is doing you a major disservice, as it's the single most central lesson about relational databases.