SQL syntax error

[timmah1]

I'm trying to get this cart working quickly.

 

Everything works except when adding things to cart, I keep getting this 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 '' at line 1

This is the page that the error is generating from

<?php
function pf_validate_number($value, $function, $redirect) {
if(isset($value) == TRUE) {
if(is_numeric($value) == FALSE) {
$error = 1;
}
if(@$error == 1) {
header("Location: " . $redirect);
}
else {
$final = $value;
}
}
else {
if($function == 'redirect') {
header("Location: " . $redirect);
}
if($function == "value") {
$final = 0;
}
}
return $final;
}
 
 
function showcart()
{
if(isset($_SESSION['SESS_ORDERNUM']))
{
if(isset($_SESSION['SESS_LOGGEDIN']))
{
$custsql = "SELECT id, status from orders WHERE customer_id = ". $_SESSION['SESS_USERID']. " AND status < 2;";
$custres = mysql_query($custsql)or die(mysql_error());;
$custrow = mysql_fetch_assoc($custres);
 
$itemssql = "SELECT products.*, orderitems.*, orderitems.id AS itemid FROM products, orderitems WHERE orderitems.product_id =products.id AND order_id = " . $custrow['id'];
$itemsres = mysql_query($itemssql)or die(mysql_error());;
$itemnumrows = mysql_num_rows($itemsres);
}
else
{
$custsql = "SELECT id, status from orders WHERE session = '" . session_id(). "' AND status < 2;";
$custres = mysql_query($custsql)or die(mysql_error());;
$custrow = mysql_fetch_assoc($custres);
$itemssql = "SELECT products.*, orderitems.*, orderitems.id AS itemid FROM products, orderitems WHERE orderitems.product_id = products.id AND order_id = " . $custrow['id'];
$itemsres = mysql_query($itemssql)or die(mysql_error());;
$itemnumrows = mysql_num_rows($itemsres);
 
}
}
else
{
$itemnumrows = 0;
}
if($itemnumrows == 0)
{
echo "You have not added anything to your shopping cart yet.";
}
 
else
{
echo "<table cellpadding='10'>";
echo "<tr>";
echo "<td></td>";
echo "<td><strong>Item</strong></td>";
echo "<td><strong>Quantity</strong></td>";
echo "<td><strong>Unit Price</strong></td>";
echo "<td><strong>Total Price</strong></td>";
echo "<td></td>";
echo "</tr>";
while($itemsrow = mysql_fetch_assoc($itemsres))
{
$quantitytotal = $itemsrow['price'] * $itemsrow['quantity'];
echo "<tr>";
if(empty($itemsrow['image'])) {
echo "<td><img src='productimages/dummy.jpg' width='50' alt='" . $itemsrow['name'] . "'></td>";
}
else {
echo "<td><img src='productimages/" .$itemsrow['image'] . "' width='50' alt='". $itemsrow['name'] . "'></td>";
}
echo "<td>" . $itemsrow['name'] . "</td>";
echo "<td>" . $itemsrow['quantity'] . "</td>";
echo "<td><strong>£" . sprintf('%.2f', $itemsrow['price']) . "</strong></td>";
echo "<td><strong>£". sprintf('%.2f', $quantitytotal) . "</strong></td>";
echo "<td>[<a href='delete.php?id=". $itemsrow['itemid'] . "'>X</a>]</td>";
echo "</tr>";
@$total = $total + $quantitytotal;
$totalsql = "UPDATE orders SET total = ". $total . " WHERE id = ". $_SESSION['SESS_ORDERNUM'];
$totalres = mysql_query($totalsql)or die(mysql_error());;
}
echo "<tr>";
echo "<td></td>";
echo "<td></td>";
echo "<td></td>";
echo "<td>TOTAL</td>";
echo "<td><strong>£". sprintf('%.2f', $total) . "</strong></td>";
echo "<td></td>";
echo "</tr>";
echo "</table>";
echo "<p><a href='checkout-address.php'>Go to the checkout</a></p>";
}
}
?>

 

I cannot for the life of me figure out where this error is.

 

Can somebody please help me out??

 

Thank you in advance

[gizmola]

You showed us a script with 2 functions in it.  That is not the script that is running.  

 

If those functions are in fact being called somewhere, any of them, that are using a query with a variable in it could be failing if the variable is missing or has a value that is the wrong type.  For example there are a lot of queries that expect a number, which are going to fail syntax checking if they are passing in a string instead.

 

Is that really the code -- with absolutely no indentation?  Not that it's high quality code by any means, but it's not easy to see what the logic is without indentation.

[ginerjm]

Echo out your query statements after building them and check them visually.

[QuickOldCar]

You have double semicolons your queries

AND status < 2;";

 

Is also doubles other places

 <?php
function pf_validate_number($value, $function, $redirect)
{
    if (isset($value) == TRUE) {
        if (is_numeric($value) == FALSE) {
            $error = 1;
        }
        if (@$error == 1) {
            header("Location: " . $redirect);
        } else {
            $final = $value;
        }
    } else {
        if ($function == 'redirect') {
            header("Location: " . $redirect);
        }
        if ($function == "value") {
            $final = 0;
        }
    }
    return $final;
}


function showcart()
{
    if (isset($_SESSION['SESS_ORDERNUM'])) {
        if (isset($_SESSION['SESS_LOGGEDIN'])) {
            $custsql = "SELECT id, status from orders WHERE customer_id = " . $_SESSION['SESS_USERID'] . " AND status < 2";
            $custres = mysql_query($custsql) or die(mysql_error());
            ;
            $custrow = mysql_fetch_assoc($custres);
            
            $itemssql = "SELECT products.*, orderitems.*, orderitems.id AS itemid FROM products, orderitems WHERE orderitems.product_id =products.id AND order_id = " . $custrow['id'];
            $itemsres = mysql_query($itemssql) or die(mysql_error());
            $itemnumrows = mysql_num_rows($itemsres);
        } else {
            $custsql = "SELECT id, status from orders WHERE session = '" . session_id() . "' AND status < 2";
            $custres = mysql_query($custsql) or die(mysql_error());
            $custrow  = mysql_fetch_assoc($custres);
            $itemssql = "SELECT products.*, orderitems.*, orderitems.id AS itemid FROM products, orderitems WHERE orderitems.product_id = products.id AND order_id = " . $custrow['id'];
            $itemsres = mysql_query($itemssql) or die(mysql_error());
            $itemnumrows = mysql_num_rows($itemsres);
            
        }
    } else {
        $itemnumrows = 0;
    }
    if ($itemnumrows == 0) {
        echo "You have not added anything to your shopping cart yet.";
    }
    
    else {
        echo "<table cellpadding='10'>";
        echo "<tr>";
        echo "<td></td>";
        echo "<td><strong>Item</strong></td>";
        echo "<td><strong>Quantity</strong></td>";
        echo "<td><strong>Unit Price</strong></td>";
        echo "<td><strong>Total Price</strong></td>";
        echo "<td></td>";
        echo "</tr>";
        while ($itemsrow = mysql_fetch_assoc($itemsres)) {
            $quantitytotal = $itemsrow['price'] * $itemsrow['quantity'];
            echo "<tr>";
            if (empty($itemsrow['image'])) {
                echo "<td><img src='productimages/dummy.jpg' width='50' alt='" . $itemsrow['name'] . "'></td>";
            } else {
                echo "<td><img src='productimages/" . $itemsrow['image'] . "' width='50' alt='" . $itemsrow['name'] . "'></td>";
            }
            echo "<td>" . $itemsrow['name'] . "</td>";
            echo "<td>" . $itemsrow['quantity'] . "</td>";
            echo "<td><strong>£" . sprintf('%.2f', $itemsrow['price']) . "</strong></td>";
            echo "<td><strong>£" . sprintf('%.2f', $quantitytotal) . "</strong></td>";
            echo "<td>[<a href='delete.php?id=" . $itemsrow['itemid'] . "'>X</a>]</td>";
            echo "</tr>";
            @$total = $total + $quantitytotal;
            $totalsql = "UPDATE orders SET total = " . $total . " WHERE id = " . $_SESSION['SESS_ORDERNUM'];
            $totalres = mysql_query($totalsql) or die(mysql_error());
        }
        echo "<tr>";
        echo "<td></td>";
        echo "<td></td>";
        echo "<td></td>";
        echo "<td>TOTAL</td>";
        echo "<td><strong>£" . sprintf('%.2f', $total) . "</strong></td>";
        echo "<td></td>";
        echo "</tr>";
        echo "</table>";
        echo "<p><a href='checkout-address.php'>Go to the checkout</a></p>";
    }
}
?> 

[Psycho]

 

You have double semicolons your queries

AND status < 2;";

 

There's nothing wrong with that. It is perfectly acceptable to close a MySQL query with a semi-colon (although not required). But, you must have a semi-colon to close a PHP statement. So this is perfectly fine

$query = "SELECT * FROM table_name;";

@timmah1,

 

Follow ginerjm's advice and echo out the queries when there are errors. Change the "or die()" commands to something like this

 

$custres = mysql_query($custsql) or die("Query: {$custsql}<br>Error: " . mysql_error());

 

NOTE: You should NEVER use "or die()" for error handling in production code, nor should you ever echo actual system errors to the page for the user to see. This gives away information about your application that a hacker could use to compromise your application and data. You should instead add appropriate error handling logic that gives the user a friendly, non-specific error message while logging the actual system error where only you can view it.

Edited April 6, 2015 by Psycho