[RESOLVED] ORDER BY mysql error.

pulp25

This code was working but I have since broken it... argh!

Being new with PHP/mysql; I cannot figure out how to finish troubleshooting this mysql error. I added or die(mysql_error()) to the line having problems. Now I am getting the fallowing error:

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 '39) ORDER BY category.category_name ASC' at line 1

Here is my code:

// Display the cart if it's not empty.
if (!$empty) {

require_once ('mysql_connect.php'); // Connect to the database.

// Retrieve all of the information for the product in the cart.
$query = 'SELECT * FROM category, product WHERE category.category_id = product.category_id AND product.product_id IN (' ;
foreach ($_SESSION['cart'] as $key => $value) {
	$query .= $key . ',';
}

///////////////////Line having Problems///////////////////////////////////
$query = substr ($query, 0, -1)  . ') ORDER BY category.category_name ASC';
////////////////////////////////////////////////////////////////////////////////


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

// Create a table and a form.
echo '<table border="0" width="63%" bgcolor="#ffffff" cellspacing="3" cellpadding="3" align="center">
<tr>
	<td align="left" width="30%"><b>Item</b></td>
	<td align="left" width="30%"><b>Item 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="view_cart.php" method="post">
';

I looked the correct syntax up in the mysql handbook and it seems to be correct. Any troubleshooting techniques would be extremely helpful or solutions woudl be extremely helpful.

leatherback

Here:
$query = substr ($query, 0, -1) . ')
you remove the 'from the query, while you try to remove the , I think.

leatherback

Try:


$keys = implode(",", array_keys($_SESSION['cart']));

// Retrieve all of the information for the product in the cart.
$query = "SELECT * FROM category, product WHERE category.category_id = product.category_id AND product.product_id IN (".$keys.") ORDER BY category.category_name ASC";
// show to debug:
echo $query;

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

pulp25

Thanks leatherback. I like your suggestion which prints out the query. Unfortunately I cannot see anything wrong with it. I double checked my table names and they all seem to be correct and should be working. Here is the query which printed out,
if you can see anything wrong with it please let me know.

SELECT * FROM category, product WHERE category.category_id = product.category_id AND product.product_id IN (,39) ORDER BY category.category_name ASCYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '39) ORDER BY category.category_name ASC' at line 1

leatherback

Have you noticed that I did a bit more than just do an echo? The whole way of getting your data into a string is changed, and should heb you with problems.

However this:

(,39)

Is a problem. You seem to have an empty value in your array.

pulp25

You have just made me realize the problem probably isn't with my view_cart code but the code that passes the information to it; add_cart. The 39 is actually the product_id for that product I added. However something with my add_cart must not be working because it is not passing any more than one product_id.

I have never made a cart before and am confused as to where I am going wrong. I'm not sure if there is anything obvious, but if there is please let me know. Otherwise I'm just going to keep on trucking and hope I can find the problem somewhere. Man coding can be tricky! hehe

Here is the code (add_cart) that adds the product to the pior code (view cart), must be a logic error somewhere...:

<?php # Script 13.8 - add_cart.php
// This page adds prints to the shopping cart.

if (is_numeric ($_GET['$pid'])) { // Check for a product ID.

$pid = $_GET['product_id'];

// Set the page title and include the HTML header.
$page_title = 'Add to Cart';
include_once ('header.html');

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

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

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

include_once ('footer.html'); // Require the HTML footer.

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

?>

The first part of my if statement is returning true, the code prints "the print has been added to your shopping cart." after execution. So maybe I'm wrong and this isn't where my problem is coming from... hmmm....

leatherback

Have you tried what the session looks like?

print_r($_SESSION);

will show you what is in the session array.

Use:

echo "<pre>";
print_r($_SESSION);
echo "<pre>";

for a structured view, or look at the source-code generated.

pulp25

The weirdest thing just happened. I took a 15 minute break, came back, and now my code is working (it also stopped working for no apparent reason in the firt place). I have no idea why, but I'll take what I can get. Weird!

Thank you leatherback for your troubleshooting tips. I have saved them in a file and will forever use them when I run into problems.

leatherback

No Worries. Glad to be of help.

Happy coding. I am off to bed. (1 Am here, work again tomorrow.. ).