Compare two tables and output multiple rows that match

[simm]

I have a cart script, and I need to display the products the user has added.  This is a school assignment and I'm very stuck on this seemingly 'little' error.

 

Right now, I have two tables:

 

For time's sake, I am showing duplicate products with different prod_id's:

Table: products

prod_idcategorysub_categorythumb_hrefimage_hreftitledescsummarymanufacturerprice

17sofaleatherred_leather_sofa_small.jpgred_leather_sofa_main.jpgRed Leather Sofa[bLOB - 134B][bLOB - 59B]Balenty999.99

18sofaleatherred_leather_sofa_small.jpgred_leather_sofa_main.jpgRed Leather Sofa[bLOB - 134B][bLOB - 59B]Balenty999.99

19sofaleatherred_leather_sofa_small.jpgred_leather_sofa_main.jpgRed Leather Sofa[bLOB - 134B][bLOB - 59B]Balenty999.99

 

And this is the cart table that holds temporary cart items (storing the product ID to pull later):

Table: cart

temp_cart_itemcart_id (assigned by session_id() )prod_iduser_id

22jier11u0e7cl2ghosjodpaark217asdfasdf

23jier11u0e7cl2ghosjodpaark217asdfasdf

24jier11u0e7cl2ghosjodpaark235asdfasdf

25jier11u0e7cl2ghosjodpaark25asdfasdf

26jier11u0e7cl2ghosjodpaark219asdfasdf

 

SO, user 'asdfasdf' is logged in and has selected 5 items for his cart - two of which are the same (prod_id 17).  Basically,

 

I need to compare the prod_id's of each table and output all products (and quantities) matching the temporary cart prod_id.

 

What I have now to loop through and display the cart items are (bear with me):

 

echo "<h1>Cart</h1>";
}
// Display welcome and navigation links
displayWelcome();
displayNav();

// Getting the prod_id's from the table cart to compare with the product table
$cart_id = session_id();
$user_id = ($_SESSION['login_username']);
$query = "SELECT prod_id FROM cart WHERE cart_id='$cart_id' AND user_id='$user_id'";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$nrows = mysqli_num_rows($result);
if($nrows < 1)
{
echo "<h2>Your cart is empty</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
}
else {  // Continue to gather products for all the product IDs in the products DB table...
echo "<h4>$nrows items in your cart</h4>";
while($row = mysqli_fetch_array( $result )) {
	$user_id = ($_SESSION['login_username']);
	$product = $row['prod_id'];

	// Is the query my problem?
	$query = "SELECT * FROM products WHERE prod_id='$product'";
	$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
	$nrows = mysqli_num_rows($result);
		if($nrows < 1) { // If the item doesn't exist, display an error
			echo "<h2>That is not a valid product, or any of the items you had in your cart are missing or have been deleted.</h2><p><a href=\"catalog.php\">Go back to the catalog</a></p>";
			// Protect from continually displaying an error by deleting the invalid cart record
			$deletequery = "DELETE FROM cart WHERE prod_id='$product'";
			$deleteresult = mysqli_query($cxn,$deletequery) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
			exit();
		}
	while($row = mysqli_fetch_array( $result )) {  //  Then disply them in a table
		echo "<h2>Items in Your Cart:</h2>";
		echo "
		<table id=\"products\">
		<thead>
		<tr>
			<th class=\"col1 headercol\" scope=\"col\">Thumbnail</th>
			<th class=\"col2 headercol\" scope=\"col\">Summary</th>
			<th class=\"col3 headercol\" scope=\"col\">Manufacturer</th>
			<th class=\"col4 headercol\" scope=\"col\">Price</th>
		</tr>
		</thead>

		<tbody>\n";
		$query = "SELECT SUM(price) FROM products WHERE prod_id='$product' GROUP BY price";
		$priceResult = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
		// keeps getting the next row until there are no more to get
		// Print out the contents of each row into a table
		echo "<tr>
			<td class=\"col1\">"; 
		echo "<a class=\"imgHref\" href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\"><img src=\"../images/".$row['thumb_href']."\" alt=\"".$row['title']."\" /></a>";
		echo "</td>\n"; 
		echo "	<td class=\"col2\">"; 
		echo "<h3 class=\"title\"><a href=\"detail.php?prod_id=".$row['prod_id']."\" title=\"".$row['title']."\">".$row['title']."</a> <span class=\"inlineh3\">by <a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></span></h3>";
		echo "<p class=\"summary\">".$row['summary']."</p>
		<p class=\"descHeading\">&#9758; <em>Full Description:</em></p>
		<p class=\"desc\">".$row['desc']."</p>";
		echo "</td>\n"; 
		echo "	<td class=\"col3\">"; 
		echo "<p class=\"manufacturer\"><a href=\"categories.php?manufacturer=".$row['manufacturer']."\" title=\"View all products by ".$row['manufacturer']."\">".$row['manufacturer']."</a></p>";
		echo "</td>\n"; 
		echo "	<td class=\"col4\">"; 
		echo "<p class=\"price\">$".$row['price']."</p>";
		echo "</td>
		</tr>\n"; 
		}
		while($priceRow = mysqli_fetch_array( $priceResult )) {
		echo "<tr class=\"short\"><td class=\"col1\"><h3><a href=\"clear_cart.php\">Empty Cart</a></h3></td><td class=\"col2\"></td><td class=\"col3 nobackground\">Total:</td><td class=\"col4\">$".$priceRow[0]."</td></tr>";
		}
		echo "</tbody>
		</table>";
		echo "<p class=\"submitOrder\"><form action='submit_order.php' method='post' id='form'>
			  <input type='submit' name='pButton' value='Submit Order'>
			  <input name='order_total' type='hidden' id='order_total' value='".$priceRow[0]."' />
			</form>
		</p>";
	}
}

 

And so the cart.php looks something like this (this example asdfasdf has 6 items in his cart):

 

 

So you can see that it only displays ONE item, even though asdfasdf DOES have 6 items in his cart (proven by checking database).  And the price is not accurate, either.

 

Can anyone help me in where my problem might be?  Is it my query?  Or my while clause?

[sasa]

use different variables for your results

[kickstart]

Hi

 

Think you might be able to get what you want with a single SQL call.

 

Try this:-

 

SELECT category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer, COUNT(*) AS ProdCount, SUM(price) AS PriceOfUnits
FROM cart
INNER JOIN products
ON cart.prod_id = productions.prod_id
WHERE cart_id='jier11u0e7cl2ghosjodpaark2'
GROUP BY category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer

 

and see if it comes out with the data you expect.

 

All the best

 

Keith

[simm]

use different variables for your results

 

I'm not sure what you mean... Do you mean in the while statement?

[simm]

Hi

 

Think you might be able to get what you want with a single SQL call.

 

Try this:-

 

SELECT category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer, COUNT(*) AS ProdCount, SUM(price) AS PriceOfUnits
FROM cart
INNER JOIN products
ON cart.prod_id = productions.prod_id
WHERE cart_id='jier11u0e7cl2ghosjodpaark2'
GROUP BY category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer

 

and see if it comes out with the data you expect.

 

All the best

 

Keith

 

You guys are amazing!  Thanks so much for the quick replies!!  Keith - I'll try your code now..

[simm]

Keith,

 

I'm having problems.  Your SQL statement selects all the columns that exist in the products table, but not in the cart table (category, sub_category, thumb_href, image_href, etc.).  I'm very much a beginner with SQL queries.  I have modified the query, but it's not working..

 

SELECT category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer, COUNT(*) AS ProdCount, SUM(price) AS PriceOfUnits
FROM products
INNER JOIN cart
ON cart.prod_id = products.prod_id
WHERE cart_id='$cart_id'
GROUP BY category, sub_category, thumb_href, image_href, title, desc, summary, manufacturer

[sasa]

add names of fields that you want to select after SELECT command

[kickstart]

Hi

 

As above, but you also need them in the GROUP BY clause as well.

 

GROUP BY is used when you want to use a function that acts on a group of rows. In the code I gave you it is getting the fields for the product and adding up some of the fields / counting the lines. If you use some of the fields from the cart then you should be fine, but using the id field from the cart would cause problems (as then every row would be unique and some not grouped together).

 

All the best

 

Keith

[simm]

If you use some of the fields from the cart then you should be fine, but using the id field from the cart would cause problems (as then every row would be unique and some not grouped together).

 

cart_id is the user's session_id dynamically created when the user logs in (you know that already).  temp_cart_item, however, is the unique primary key in the table to distinguish between duplicate items the user has added to their cart.  Does that make sense?

 

So what I'm doing is trying to match the prod_id's in the product table with the prod_id's in the cart table WHERE the user's session (cart_id) is equal to the current user's session.

 

Thanks so much for the replies so far, but I'm still stuck

[kickstart]

Hi

 

Let me know the fields you want out, and which ones you want adding up (ie price I think) and I will have a play when I get home.

 

If you could export the table defs then I can have an easier play!

 

All the best

 

Keith

[simm]

Here's what I have to do:

 

[*]Gather all products (including description, price, everything in the rows)

[*]Output the products in a table to display in the cart

[*]Add up the total

 

You can have a go at the application by going to http://devowe.com/sessions/php/ex6/login/ and logging in with 'asdfasdf' as the username and password.

 

Thank you so much!

PS - my deadline is tomorrow, so I have to keep banging away at this - I really, really appreciate all your help!!

PPS - attached are the table definitions...let me know if that is what you wanted.

 

 

[attachment deleted by admin]

[simm]

Since my deadline is tomorrow, I just decided to create a new table called cart2.  In it, I store the prod_id, username, title, and price of products.  The "buy now" buttons send the information to the table, then my cart.php page pulls that information to display it.

 

So in a sense, my problem is solved.  But in reality, it isn't because there are security issues with my new method.  I would like to hear if there are any solutions to my original problem.

 

Thanks all for your help!!

[kickstart]

Hi

 

Sorry for the delay. Back now.

 

Had a play and this is (crudely) I think what you want.

 

<?php

$cxn = mysqli_connect("localhost", "root", "", "testarea");

echo "<h1>Cart</h1>";

$query = "SELECT DISTINCT cart_id, user_id FROM cart ";
$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
while($row = mysqli_fetch_array( $result )) 
{
echo "<a href='CartTest.php?cartid=".$row['cart_id']."'>".$row['cart_id'].' - '.$row['user_id']."</a><br />";
}

if (isset($_REQUEST['cartid']))
{
echo "<h2>Items in Your Cart:</h2>";
echo "
<table id=\"products\">
<thead>
<tr>
	<th class=\"col1 headercol\" scope=\"col\">Thumbnail</th>
	<th class=\"col2 headercol\" scope=\"col\">Summary</th>
	<th class=\"col3 headercol\" scope=\"col\">Manufacturer</th>
	<th class=\"col4 headercol\" scope=\"col\">Quantity</th>
	<th class=\"col5 headercol\" scope=\"col\">Unit Price</th>
	<th class=\"col6 headercol\" scope=\"col\">Total Price</th>
</tr>
</thead>";
$CartId = mysqli_real_escape_string($cxn, $_REQUEST['cartid']);
$query = "SELECT b.user_id, b.prod_id, b.cart_id, a.category, a.sub_category, a.thumb_href, a.image_href, a.title, `a`.`desc` , a.summary, a.manufacturer, a.price, COUNT( * ) AS ProdCount, SUM( price ) AS PriceOfUnits
FROM products a
INNER JOIN cart b ON b.prod_id = a.prod_id
WHERE cart_id = '$CartId'
GROUP BY category, b.user_id, b.prod_id, b.cart_id, a.category, a.sub_category, a.thumb_href, a.image_href, a.title, `a`.`desc` , a.summary, a.manufacturer, a.price";

$result = mysqli_query($cxn,$query) or die("<h2 class=\"warning\">Whoa! Problem with the cart script here!</h2>");
$TotalPrice = 0;
while($row = mysqli_fetch_array( $result )) 
{
	echo "
	<tr>
		<td class=\"col1 headercol\" ><img src='".$row['image_href']."' /></td>
		<td class=\"col2 headercol\" >".$row['summary']."</td>
		<td class=\"col3 headercol\" >".$row['manufacturer']."</td>
		<td class=\"col4 headercol\" >".$row['ProdCount']."</td>
		<td class=\"col5 headercol\" >".$row['price']."</td>
		<td class=\"col6 headercol\" >".$row['PriceOfUnits']."</td>
	</tr>";
	$TotalPrice += $row['PriceOfUnits'];
}	
echo "
<tr>
	<td colspan='5' >Total</td>
	<td >$TotalPrice</td>
</tr></table>";

}

?>

 

I have just put a list of carts at the top, and click on one of those and you get the page back with the cart contents at the bottom (saved as CartTest.php).

 

All the best

 

Keith