Error 1064 in php call to mysql

Vikki · #1 (**permalink**) 06-21-08, 03:00 AM

Hello. My first post...

I am working on the same homework project Spherion posted in May. I have gone over my code several times and it is all correct "by the book", but it doesn't work properly. I've found a few other copies on the web that have the same issue. Here's one example:

http://www.adamtest.net/ch11/GosselinGourmetGoods.php

If you select the first item, it adds to the cart without issue. But when you add an item, the error 1064 shows up. The line number depends on the line breaks in the $SQLstring definition, mine is called on line 2.

Code:

Error code 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 'WHERE productID=''' at line 2

I've spent two days trying to find the issue, but as a brand new PHP programmer, I am not having any luck. The problem seems to be in the public function showCart area of the script. However, the SQLstring echo that I currently have commented out returns a perfectly formed sql query, so I don't understand why I am receiving a syntax errror. SQL query and result below:

Code:

mysql> SELECT * FROM spices WHERE productID='SPICE005'
    -> ;
| productID | name             | description                                                                   | price |
| SPICE005  | Turmeric, Ground | Turmeric is a member of the ginger family and has been used for over 2,000 years in China, India, and the Middle East. It addsa warm, mild aroma and a yellow color to foods. Weight: 4.5 ounces. |  3.75 |

1 row in set (0.02 sec)

Here is the code to ShoppingCart.php:

PHP Code:


			
<?php
class ShoppingCart {
    private $DBConnect = "";
    private $DBName = "";
    private $TableName = "";
    private $Orders = array();
    private $OrderTable = array();
    
    function __construct() {
        $this->DBConnect = @new mysqli("localhost", "****", "******");
        if (mysqli_connect_errno())
            die("<p>Unable to connect to the database server.</p>" .
                "<p>Error code " . mysqli_connect_errno() .
                ": " . mysqli_connect_error()) . "</p>";
    }
    
    function __destruct() {
        $this->DBConnect->close();
    }
    
    public function setDatabase($Database) {
        $this->DBName = $Database;
        @$this->DBConnect->select_db($this->DBName)
            or die("<p>Unable to select the database.</p>"
            . "<p>Error code " . mysqli_errno($this->DBConnect)
            . ": " . mysqli_error($this->DBConnect)) . "</p>";
    }
    
    public function setTable($Table) {
        $this->TableName = $Table;
    }
    
    public function getProductList() {
        $SQLstring = "SELECT * FROM $this->TableName";
        $QueryResult = $this->DBConnect->query($SQLstring)
            or die("<p>Unable to perform the query.</p>"
            . "<p>Error code " . mysqli_errno($this->DBConnect)
            . ": " . mysqli_error($DBConnect)) . "</p>";
        echo "<table width='100%' border='1'>";
        echo "<tr><th>Product</th><th>Description</th>
        <th>Price Each</th><th>Select Item</th></tr>";
        $Row = $QueryResult->fetch_row();
        do {
            echo "<tr><td>{$Row[1]}</td>";
            echo "<td>{$Row[2]}</td>";
            printf("<td align='center'>$%.2f</td>", $Row[3]);
            echo "<td align='center'>
                <a href='ShowCart.php?PHPSESSID=" . session_id()
                . "&operation=addItem&productID=" . $Row[0]
                . "'>Add</a></td></tr>";
            $Row = $QueryResult->fetch_row();
        } while ($Row);
        echo "</table>";
    }
    
    public function addItem() {
        $ProdID = $_GET['productID'];
        if (array_key_exists($ProdID, $this->Orders))
            exit("<p>You already selected that item!  Click your
                 browser's Back button to return to the previous page</p>");
            $this->Orders[$ProdID] = 1;
            $this->OrderTable[$ProdID] = $this->TableName;
    }
    
    function __wakeup() {
        $this->DBConnect = @new mysqli("localhost", "****", "******");
        if (mysqli_connect_errno())
            die("<p>Unable to connect to the database server.</p>"
                . "<p>Error code " . mysqli_connect_errno()
                . ": " . mysqli_connect_error()) . "</p>";
        @$this->DBConnect->select_db($this->DBName)
            or die("<p>Unable to select the database.</p>"
            . "<p>Error code " . mysqli_errno($this->DBConnect)
            . ": " . mysqli_error($this->DBConnect)) . "</p>";
    }
    
    public function showCart() {
        if (empty($this->Orders))
        echo "<p>Your shopping cart is empty!</p>";
        else {
            echo "<table width='100%' border='1'>";
            echo "<tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr>";
            $Total = 0;
        }
        foreach($this->Orders as $Order) {
            [color=red]$SQLstring = "SELECT * FROM "
                . $this->OrderTable[key($this->Orders)] . "
                WHERE productID='" . key($this->Orders) . "'";[/color]
//            echo "<p>" . $SQLstring . "</p>";   //display $SQLstring
            $QueryResult = @mysqli_query($this->DBConnect, $SQLstring)
                or die("<p>Unable to perform the query.</p>"
                . "<p>Error code " . mysqli_errno($this->DBConnect)
                . ": " . mysqli_error($this->DBConnect)) . "</p>";
            $Row = mysqli_fetch_row($QueryResult);
            echo "<td align='center'>";
            echo "<a href='ShowCart.php?PHPSESSID=" . session_id()
                . "&operation=removeItem&productID=" . $Row[0]
                . "'>Remove</a></td>";
            echo "<td>{$Row[1]}</td>";
            echo "<td align 'center'>$Order ";
            echo "</td>";
            printf("<td align='center'>$%.2f</td></tr>", $Row[3]);
            $Total += $Row[3] * $Order;
            next($this->Orders);           
        }
        
        echo "<tr><td align='center'><a href='ShowCart.php?PHPSESSID="
            . session_id() . "&operation=emptyCart'><strong>
            Empty Cart</strong></a></td>";
        echo "<td align='center' colspan='2'><strong>Your shopping
            cart contains " . count($this->Orders)
            . " product(s).</strong></td>";
        printf("<td align='center'><strong>Total: $%.2f</strong></td>", $Total);
        echo "</table>";
    }
    
            
    public function removeItem() {
        $ProdID = $_GET['productID'];
        unset($this->Orders[$ProdID]);
        unset($this->OrderTable[$ProdID]);
    }
    
    function emptyCart() {
        $this->Orders = array();
        $this->OrderTable = array();
    }
}
?>

And the code that calls it:

PHP Code:


			
<?php
require_once("ShoppingCart.php");
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Gosselin Gourmet Goods</title>
<link rel="stylesheet" href="php_styles.css" type="text/css" />
</head>

<body>
<h1>Gosselin Gourmet Goods</h1>
<h2>Shop by Category</h2>
<p><a href="GosselinGourmetCoffees.php">Gourmet Coffees</a><br />
<a href="GosselinGourmetOlives.php">Specialty Olives</a><br />
<a href="GosselinGourmetSpices.php">Gourmet Spices</a></p>
<h2>Gourmet Coffees</h2>
<?php
$Database = "gosselin_gourmet";
$Table = "coffee";

if (isset($_SESSION['curCart']))
    $Cart = unserialize($_SESSION['curCart']);
else {
    if (class_exists("ShoppingCart")) {
        $Cart = new ShoppingCart();
        $Cart->setDatabase($Database);
    }
    else
        exit("<p>The ShoppingCart class is not available!</p>");
}
$Cart->setTable($Table);
$Cart->getProductList();
$_SESSION['curCart'] = serialize($Cart);
?>
<p><a href='<?php echo "ShowCart.php?PHPSESSID=" . session_id() ?>'>Show Shopping Cart</a></p>        
</body>

</html>

and ShowCart.php

PHP Code:


			
<?php
require_once("ShoppingCart.php");
session_start();
if (!isset($_SESSION['curCart']))
    header("location:GosselinGourmetGoods.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Gosselin Gourmet Goods</title>
<link rel="stylesheet" href="php_styles.css" type="text/css" />
</head>

<body>
<h1>Gosselin Gourmet Goods</h1>
<h2>Shop by Category</h2>
<p><a href="GosselinGourmetCoffees.php">Gourmet Coffees</a><br />
<a href="GosselinGourmetOlives.php">Specialty Olives</a><br />
<a href="GosselinGourmetSpices.php">Gourmet Spices</a></p>

<?php
$Cart = unserialize($_SESSION['curCart']);
if (isset($_GET['operation'])) {
    if ($_GET['operation'] == "addItem")
        $Cart->addItem();
    if ($_GET['operation'] == "removeItem")
        $Cart->removeItem();
    if ($_GET['operation'] == "emptyCart")
        $Cart->emptyCart();
}
$Cart->showCart();
$_SESSION['curCart'] = serialize($Cart);
?>
</body>

</html>

Any help is greatly appreciated.

End User · #2 (**permalink**) 06-21-08, 08:16 AM

As I understand it, the error 1064 occurs due to a mismatch between reserved keywords and the mySQL version used- different versions have different reserved words. If used by a non-supported version it causes the 1064 error to be thrown.

wirehopper · #3 (**permalink**) 06-21-08, 08:32 AM

You might want to add backticks and braces, like so:

PHP Code:


			


$SQLstring = "SELECT * FROM `{$this->TableName}`";



$SQLstring = "SELECT * FROM `"

                . $this->OrderTable[key($this->Orders)] . "`

                WHERE `productID`='" . key($this->Orders) . "'";

The backticks should prevent issues if there are spaces in the OrderTable data, and the braces will evaluate what is inside them.

Vikki · #4 (**permalink**) 06-21-08, 12:08 PM

Thank you for the suggestions. Implementing those changes malformed the mySQL string so that no database was selected.

By adding echo points, I believe I have narrowed the error down to the code included in this function:

Code:

public function getProductList() {
        $SQLstring = "SELECT * FROM $this->TableName";
        $QueryResult = $this->DBConnect->query($SQLstring)
            or die("<p>Unable to perform the query.</p>"
            . "<p>Error code " . mysqli_errno($this->DBConnect)
            . ": " . mysqli_error($DBConnect)) . "</p>";
        echo "<table width='100%' border='1'>";
        echo "<tr><th>Product</th><th>Description</th>
        <th>Price Each</th><th>Select Item</th></tr>";
        $Row = $QueryResult->fetch_row();
        do {
            echo "<tr><td>{$Row[1]}</td>";
            echo "<td>{$Row[2]}</td>";
            printf("<td align='center'>$%.2f</td>", $Row[3]);
            echo "<td align='center'>
                <a href='ShowCart.php?PHPSESSID=" . session_id()
                . "&operation=addItem&productID=" . $Row[0]
                . "'>Add</a></td></tr>";
            $Row = $QueryResult->fetch_row();
        } while ($Row);
        echo "</table>";
    }

The reason I suspect this code is that the error message returns a malformed SQLstring with no table or productID included.

SELECT * FROM spices WHERE productID='SPICE003' // from my echo line in function showCart
Remove Lovage Root 1 $4.39 // table content generated

SELECT * FROM WHERE productID='' // generated when program runs from getProductList

Unable to perform the query.

Error code 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 'WHERE productID=''' at line 1

I've done a little more experimentation.

Code:

$SQLstring = "SELECT * FROM " . $this->TableName;

now provides a valid table selection, and the first item works fine. The SECOND item selected fails. So I am now suspecting the "do" loop...please help!

wirehopper · #5 (**permalink**) 06-21-08, 02:22 PM

Quote:

Error code 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 'WHERE productID=''' at line 1

indicates a MySQL problem, which may be caused by its construction with PHP.

Echo the SQL statements, then paste them into MySQL and adjust them until they work. Then, change your code accordingly.

Vikki · #6 (**permalink**) 06-21-08, 02:44 PM

The SQL statements work in the first iteration of the loop, and in the mySQL monitor as captured in the first loop. Subsequent iterations break. I cannot find what is breaking the second iteration. It's either in the "foreach" or "do" loops, but I can't see it.

HTML Code:

<table width='100%' border='1'><tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr><p>`coffee`</p><p>SELECT * FROM `coffee` 
                WHERE `productID`='COFFEE001'</p><td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=removeItem&productID=COFFEE001'>Remove</a></td><td>Jamaican Blue Mountain Coffee</td><td align 'center'>1 </td><td align='center'>$22.95</td></tr><tr><td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=emptyCart'><strong>
            Empty Cart</strong></a></td><td align='center' colspan='2'><strong>Your shopping
            cart contains 1 product(s).</strong></td><td align='center'><strong>Total: $22.95</strong></td></table></body>

Successful first iteration

HTML Code:

<table width='100%' border='1'><tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr><p>`coffee`</p><p>SELECT * FROM `coffee` 
                WHERE `productID`='COFFEE004'</p><td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=removeItem&productID=COFFEE004'>Remove</a></td><td>Pure Kona Coffee</td><td align 'center'>1 </td><td align='center'>$21.45</td></tr><p>``</p><p>SELECT * FROM `` 
                WHERE `productID`=''</p><p>Unable to perform the query.</p><p>Error code 1103: Incorrect table name ''

Failed second iteration.

I noticed on copying the output source that the </html> tag is missing on the output of the second iteration.

Okay...by echoing and commenting more script with

PHP Code:


			
        foreach($this->Orders as $Order) {

                        $SQLstring = "SELECT * FROM `" 

                . $this->OrderTable[key($this->Orders)] . "` 

                WHERE `productID`='" . key($this->Orders) . "'";  

            echo "<p>`{$this->OrderTable[key($this->Orders)]}`</p>";    //display selected table

            echo "<p>" . $SQLstring . "</p>";   //display $SQLstring

            $QueryResult = @mysqli_query($this->DBConnect, $SQLstring)

                or die("<p>Unable to perform the query.</p>"

                . "<p>Error code " . mysqli_errno($this->DBConnect)

                . ": " . mysqli_error($this->DBConnect)) . "</p>";

            $Row = mysqli_fetch_row($QueryResult);

            echo "<td align='center'>";

            echo "<a href='ShowCart.php?PHPSESSID=" . session_id()

                . "&operation=removeItem&productID=" . $Row[0]

                . "'>Remove</a></td>";

            echo "<td>{$Row[1]}</td>";

            echo "<td align 'center'>$Order ";

            echo "</td>";

            printf("<td align='center'>$%.2f</td></tr>", $Row[3]);

            $Total += $Row[3] * $Order;

            next($this->Orders);           

        }

        echo "<tr><td align='center'><a href='ShowCart.php?PHPSESSID="

            . session_id() . "&operation=emptyCart'><strong>

            Empty Cart</strong></a></td>";

        echo "<td align='center' colspan='2'><strong>Your shopping

            cart contains " . count($this->Orders)

            . " product(s).</strong></td>";

        printf("<td align='center'><strong>Total: $%.2f</strong></td>", $Total);

        echo "</table>";

    }

in the foreach loop, I now have:

`coffee`
SELECT * FROM coffee WHERE productID=`COFFEE002`
Unable to perform the query.
Error code 1054: Unknown column 'COFFEE002' in 'where clause'

It looks like the code is not building the OrderTable properly. This is with backticks.

I've only been working with PHP for a month, self study, so I have nowhere else to turn.

wirehopper · #7 (**permalink**) 06-21-08, 02:57 PM

The do loop assumes the query returns some results, but if there are no results to return, it will execute the first iteration anyway.

Check this page: http://www.php.net/manual/en/mysqli.multi-query.php and this pagehttp://www.php.net/manual/en/mysqli.use-result.php for ideas on coding the loops.

Vikki · #8 (**permalink**) 06-21-08, 03:07 PM

`coffee`

SELECT * FROM `coffee` WHERE `productID`='COFFEE004'
Remove Pure Kona Coffee 1 $21.45

First iteration.

``

SELECT * FROM `` WHERE `productID`=''

Unable to perform the query.

Error code 1103: Incorrect table name ''

Displayed on second iteration.

Thanks, I will look. I am STUCK...this is right out of the text, and this is my third day trying to make it work as the screenshots show.

Vikki · #9 (**permalink**) 06-21-08, 04:13 PM

The failure is at the function addItem(). It is not retrieving $this->Orders[$ProdID] = 1; or
$this->OrderTable[$ProdID] = $this->TableName;

The data is in the table. I can select any item succesfully as the first item in the empty cart.

Can anyone help?

PHP Code:


			
    public function addItem() {

        $ProdID = $_GET['productID'];

        if (array_key_exists($ProdID, $this->Orders))

            exit("<p>You already selected that item!  Click your

                 browser's Back button to return to the previous page</p>");

            $this->Orders[$ProdID] = 1;

            $this->OrderTable[$ProdID] = $this->TableName;

        echo "<p>Added " . $Orders[$ProdID] . " to table " . $TableName .".";

    }

Called from

PHP Code:


			
<?php

$Cart = unserialize($_SESSION['curCart']);

if (isset($_GET['operation'])) {

    if ($_GET['operation'] == "addItem")

        $Cart->addItem();

    if ($_GET['operation'] == "removeItem")

        $Cart->removeItem();

    if ($_GET['operation'] == "emptyCart")

        $Cart->emptyCart();

}

$Cart->showCart();

$_SESSION['curCart'] = serialize($Cart);

?>

job0107 · #10 (**permalink**) 06-21-08, 10:27 PM

Try your class like this. I think you will be pleasantly pleased.

ShoppingCart.php

PHP Code:


			
<?php
class ShoppingCart {
    private $DBConnect = "";
    private $DBName = "";
    private $TableName = "";
    private $Orders = array();
    private $OrderTable = array();

    public function __construct() {
        $this->DBConnect = @new mysqli("localhost", "root", "");
        if (mysqli_connect_errno())
            die("<p>Unable to connect to the database server.</p><p>Error code ".mysqli_connect_errno().": ".mysqli_connect_error())."</p>";
    }
    public function __destruct() {
        $this->DBConnect->close();
    }
    public function setDatabase($Database) {
        $this->DBName = $Database;
        @$this->DBConnect->select_db($this->DBName) or die("<p>Unable to select the database.</p><p>Error code ".mysqli_errno($this->DBConnect).": ".mysqli_error($this->DBConnect))."</p>";
    }
    public function setTable($Table) {
        $this->TableName = $Table;
    }

    public function getProductList() {
        $SQLstring = "SELECT * FROM $this->TableName";
        $QueryResult = $this->DBConnect->query($SQLstring) or die("<p>Unable to perform the query.</p><p>Error code ".mysqli_errno($this->DBConnect).": ".mysqli_error($DBConnect))."</p>";
        echo "<table width='100%' border='1'><tr><th>Product</th><th>Description</th><th>Price Each</th><th>Select Item</th></tr>";
        $Row = $QueryResult->fetch_row();
        do {
            echo "<tr><td style='padding-left:5px;'>{$Row[1]}</td><td style='padding-left:5px;'>{$Row[2]}</td>";
            printf("<td align='center'>$%.2f</td>", $Row[3]);
            echo "<td align='center'><a href='ShowCart.php?PHPSESSID=".session_id()."&operation=addItem&productID=".$Row[0]."'>Add</a></td></tr>";
            $Row = $QueryResult->fetch_row();
        } while ($Row);
        echo "</table>";
    }
    public function addItem() {
        $ProdID = $_GET["productID"];
        if (array_key_exists($ProdID, $this->Orders))
            exit("<p>You already selected that item!  Click your browser's Back button to return to the previous page</p>");
            $this->Orders[$ProdID] = $ProdID;
            $this->OrderTable[$ProdID] = $this->TableName;
    }
    public function __wakeup() {
        $this->DBConnect = @new mysqli("localhost", "root", "");
        if (mysqli_connect_errno())
            die("<p>Unable to connect to the database server.</p><p>Error code ".mysqli_connect_errno().": ".mysqli_connect_error())."</p>";
        @$this->DBConnect->select_db($this->DBName) or die("<p>Unable to select the database.</p><p>Error code ".mysqli_errno($this->DBConnect).": ".mysqli_error($this->DBConnect))."</p>";
    }
    public function showCart() {
        if (empty($this->Orders))
        echo "<p>Your shopping cart is empty!</p>";
        else {echo "<table width='100%' border='1'><tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr>";}
        foreach($this->Orders as $Order) {
            $OrderQTY = 2;
            $SQLstring = "SELECT * FROM ".$this->OrderTable[key($this->Orders)]." WHERE productID='".$Order."'";
            $QueryResult = @mysqli_query($this->DBConnect, $SQLstring) or die("<p>Unable to perform the query.</p><p>Error code ".mysqli_errno($this->DBConnect).": ".mysqli_error($this->DBConnect))."</p>";
            $Row = mysqli_fetch_row($QueryResult);
            $Total += $Row[3] * $OrderQTY;
            echo "<tr>
                   <td align='center'><a href='ShowCart.php?PHPSESSID=".session_id()."&operation=removeItem&productID=".$Row[0]."'>Remove</a></td>
                   <td style='padding-left:10px;'>{$Row[1]}</td>
                   <td align='center'>$OrderQTY</td>";
            printf("<td align='center'>$%.2f</td></tr>", $Row[3]);
            $Total_total += $Total;
            $Total=0;
        }
        $link = $Total_total ? "<a href='ShowCart.php?PHPSESSID=".session_id()."&operation=emptyCart'><strong>Empty Cart</strong></a>" : "";
        echo "<tr><td align='center'>$link</td>
        <td colspan='2'><strong>&nbsp;&nbsp;&nbsp;Your shopping cart contains ".count($this->Orders)." product(s).</strong></td>";
        printf("<td align='center'><strong>Total: $%.2f</strong></td></tr>", $Total_total);
        echo "</table>";
    }
    public function removeItem() {
        $ProdID = $_GET['productID'];
        unset($this->Orders[$ProdID]);
        unset($this->OrderTable[$ProdID]);
    }
    public function emptyCart() {
        $this->Orders = array();
        $this->OrderTable = array();
    }
}
?>