[RESOLVED] Problem with SQL query including variables from a session

[jme]

I am writing a cart script for a site that when something is added to a cart it gets added to the users session using addcart.php

<?php 

session_name ('funkyStore');
session_start();

if (is_numeric ($_GET['id'])) { 

$id = $_GET['id'];

$page_title = 'Add to Cart';
include_once ('includes/header.inc');
include_once ('includes/nav.inc');

// Check if the cart already contains one of these prints.
if (isset ($_SESSION['cart'][$id])) {
	$qty = $_SESSION['cart'][$id] + 1;
} else {
	$qty = 1;
}

// Add to the cart session variable.
$_SESSION['cart'][$id] = $qty;

// Display a message.
echo '<p>The print has been added to your shopping cart.</p>';

include_once ('includes/footer.inc'); // Require the HTML footer.

} else { // Redirect
	header ("Location:  http://" . $_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php1");
	exit();
}

?>

Then the users accesses vcart.php to see the contents of the cart:

<?php 

session_name ('funkyStore');
session_start();

$page_title = 'View Your Shopping Cart';

if (isset ($_POST['submit'])) {
	foreach ($_POST['qty'] as $key => $value) {
		if ( ($value == 0) AND (is_numeric ($value)) ) {
			unset ($_SESSION['cart'][$key]);
		} elseif ( is_numeric ($value) AND ($value > 0) ) {
			$_SESSION['cart'][$key] = $value;
		}
	}
}

$empty = TRUE;

if (isset ($_SESSION['cart'])) {
	foreach ($_SESSION['cart'] as $key => $value) {
		if (isset($value)) {
			$empty = FALSE;	
		}
	} 
}	

if (!$empty) {

require_once ('mysqldbc.php'); 

$query = 'SELECT * FROM fps_items where item_id = (';
foreach ($_SESSION['cart'] as $key => $value) {
	$query .= $key . ',';
}
$query = substr ($query, 0, -1) . ') ORDER BY item_name ASC';
$result = mysql_query ($query);

echo '<table border="0" width="90%" cellspacing="3" cellpadding="3" align="center">
<tr>
	<td align="left" width="5%"><b>id</b></td>
	<td align="left" width="65%"><b>Name</b></td>
	<td align="right" width="10%"><b>Price</b></td>
	<td align="center" width="10%"><b>Qty</b></td>
	<td align="right" width="10%"><b>Total Price</b></td>
</tr>
<form action="vcart.php" method="post"> ';


$total = 0;
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

	$subtotal = $_SESSION['cart'][$row['item_id']] * $row['item_price'];
	$total += $subtotal;

	echo "	<tr>
	<td align=\"left\">{$row['item_id']}</td>
	<td align=\"left\">{$row['item_name']}</td>
	<td align=\"right\">&pound;{$row['item_price']}</td>
	<td align=\"center\"><input type=\"text\" size=\"3\" name=\"qty[{$row['item_id']}]\" value=\"{$_SESSION['cart'][$row['item_id']]}\" /></td>
	<td align=\"right\">&pound;" . number_format ($subtotal, 2) . "</td>
</tr>\n";
} 

echo '	<tr>
	<td colspan="4" align="right"><b>Total:<b></td>
	<td align="right">&pound;' . number_format ($total, 2) . '</td>
</tr>
</table><div align="center"><input type="submit" name="submit" value="Update My Cart" /></form><br /><br /><a href="checkout.php"><font size="+3">Checkout</font></a></div>';

mysql_close(); 

} else {
	echo '<p>Your cart is currently empty.</p>';
}


?>

If there is one item in the cart there is no problem and it all works as I would expect it to however if there are more than one item in the cart it throws us a SQL error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/com/httpdocs/wcopy/vcart.php on line 51

I have had a play around and can't seem to think of anything.

Any suggestions please?

Jamie 🙂

[Oersoep]

Your query will look like this:

SELECT * FROM fps_items where item_id = (4,5,7) ORDER BY item_name ASC'

I suspect you ment to use IN():

SELECT * FROM fps_items where item_id = IN(4,5,7) ORDER BY item_name ASC'

[jme]

Thanks for your reply. Reading through the documentation I think that you're right suggesting that I need to use in() but when I incorporate this into my script it now throws up the SQL error when there is anything in the cart - even one thing now.

Any ideas?

Thanks, Jamie 🙂

[Oersoep]

Yes.
Make it like this:

SELECT FROM fps_items where item_id = IN('4','5','7') ORDER BY item_name ASC
SELECT FROM fps_items where item_id = IN('4') ORDER BY item_name ASC
And with an empty cart:
SELECT * FROM fps_items where item_id = IN('') ORDER BY item_name ASC

Or just a hard if() in your php 🙂

[jme]

I'm still getting SQL errors with those queries though. Both when in the script and when I put them into phpMyadmin.

#1064 - 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 'IN('0') ORDER BY item_name ASC LIMIT 0, 30' at line 1

Thanks for your patience. 🙂

[Lars_Berg]

It's

IN

not

= IN

[Oersoep]

Yup. You're right. Sorry.

[jme]

Excellent. Thanks guys. 😃