Error 1064 in php call to mysql

Vikki · #11 (**permalink**) 06-21-08, 10:45 PM

The error is gone now, whew! And I changed the quantity back to 1 (adjusted for test purposes, as cause-and-effect is my main mode of learning). I'll view the code line by line to see what changes you made. But all is not well...

HTML Code:

<table width='100%' border='1'><tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr><tr>
                   <td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=removeItem&productID='>Remove</a></td>
                   <td style='padding-left:10px;'></td>
                   <td align='center'>1</td><td align='center'>$0.00</td></tr><tr>
                   <td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=removeItem&productID=OLIVE003'>Remove</a></td>
                   <td style='padding-left:10px;'>Garlic Stuffed Olives</td>
                   <td align='center'>1</td><td align='center'>$8.99</td></tr><tr>
                   <td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=removeItem&productID='>Remove</a></td>
                   <td style='padding-left:10px;'></td>
                   <td align='center'>1</td><td align='center'>$0.00</td></tr><tr><td align='center'><a href='ShowCart.php?PHPSESSID=766v4o2bqrcfvmblvodobcbaj1&operation=emptyCart'><strong>Empty Cart</strong></a></td>
        <td colspan='2'><strong>&nbsp;&nbsp;&nbsp;Your shopping cart contains 3 product(s).</strong></td><td align='center'><strong>Total: $8.99</strong></td></tr></table></body>

The cart only shows one valid item, and carries artifacts of other items with no description, no price. The first item I select is there until I select the second item, then it disappears and the second item appears. Then the second item remains and other blank item lines appear.

I think this code is possessed...

Thank you, I am learning.

wirehopper · #12 (**permalink**) 06-21-08, 11:04 PM

If you are at a point where you feel really stuck, try the following:

Put echo commands in until you find the line that is failing. This is the brute force, last resort approach to identify the problem.

I'm assuming this code is part of something that includes a session_start.

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); 

?>

Last note - do / while can be a risky approach. I prefer to use

PHP Code:


			
while ($row=$result->fetch_row())

{

  echo '<pre>';var_dump($row);echo '</pre>';

}

var_dump is a great debugging aid.

Good luck. If it is any consolation, you will solve this, and you will never forget what you learned.

Vikki · #13 (**permalink**) 06-21-08, 11:15 PM

I'm getting quite good at sticking echo commands in the right places. But I don't have enough experience yet to use that info to get a solution. Since I am doing these exercises not for classwork but for my own personal enlightenment, I find it mildly amusing that I can't let it go and say oh well. No one is grading me but me, and I am stubbornly insistent on making it work.

I believe the code is now failing in

PHP 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'><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><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>";

    }

Remove Item Product Quantity Price Each
Remove 1 $0.00 // first item added to cart disappears when second is added
Remove Jamaican Blue Mountain Coffee 1 $22.95 // second item is fine
Remove 1 $0.00 // subsequent items are blank and don't affect previous items
Empty Cart Your shopping cart contains 3 product(s). Total: $22.95

job0107 · #14 (**permalink**) 06-21-08, 11:15 PM

I am not sure what you did, but it works perfectly on my machine.
I tested it in both FireFox and IE.

This is the code I used.

PHP Code:


			
<?php
session_start();
require_once("ShoppingCart.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>
<h2>Gourmet Coffees</h2>
<?php
$Database = "";
$Table = "";
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>

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;
            $Total_total += $Total;
            $Total=0;
            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]);
        }
        $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();
    }
}
?>

ShowCart.php

PHP Code:


			
<?php
session_start();
require_once("ShoppingCart.php");
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>

But then of course I am not using your style sheet. Because you didn't provide it.

Vikki · #15 (**permalink**) 06-21-08, 11:37 PM

The style sheet is just font size and color. It's irrelevant for debugging.

I think I will make a new folder and put the files in and try it again. Hearing that it works is very encouraging!

Vikki · #16 (**permalink**) 06-22-08, 12:10 AM

It's better...but not yet there.

I copied the code from the first section above, which is GosselinGourmetCoffees.php, and copied and modified it for G-G-Olives.php and G-G-Spices.php. All that changes is the page header and table name.

When I run the program the first table selected works PERFECTLY. If I try to add items from one of the other tables, they come up blank in the shopping cart.

wirehopper · #17 (**permalink**) 06-22-08, 01:26 PM

Check the table names (in the variables) and the (construction of the) select statements. Test the other tables in MySQL directly.

Vikki · #18 (**permalink**) 06-22-08, 02:22 PM

There are three tables: coffee, olives, spices.

No matter which table I first select from, it works. When I select items from the second table, it wipes the items from the other table from the cart. Once it does that, I cannot select items from the third table. It does not matter in which order I access the tables.

The initial mysql query works in mysql monitor. The subsequently generated queries are missing the table name and product ID, which should be provided by the addItem function. It's either failing in addItem, or in getProductList. But I'm too green at this to trace it down, though I am trying.

wirehopper · #19 (**permalink**) 06-22-08, 11:52 PM

Quote:

When I select items from the second table

Sounds like a session issue or a class conflict.

You might want to pass the table into the class upon instantiation.

job0107 · #20 (**permalink**) 06-24-08, 09:07 AM

Ok Vikki, I have been tossing this around for a while and have finally come up with a solution for you.

Instead of storing your items in a simple array, I used a multi-dimensional array to store the item ID and table name. These values are used to lookup the items.

The multi-dimensional array is constructed as rows with two columns ( itemID & Table name ($Orders[row][column]) ).

I took your links and put them in a separate file. You need to put your table names in there. Where table=table name.

The default table name is stored in GosselinGourmetGoods.php under:

PHP Code:


			
 $Table = !empty($_GET["table"])?$_GET["table"]:"users"; // Replace "users" with the default table name. //

So here is the code in four files:

GosselinGourmetGoods.php

PHP Code:


			
<?php
session_start();
require_once("ShoppingCart.php");
$Title=!empty($_GET["product"])?$_GET["product"]:"Gourmet Coffees";
?>
<!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>
<?php
$Database = ""; // MySql database name goes here. //
$Table = !empty($_GET["table"])?$_GET["table"]:"users"; // Replace "users" with the default table name. //
include "links.html";
echo "<h2>$Title</h2>";
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>

ShoppingCart.php

PHP Code:


			
<?php
class ShoppingCart {
    private $DBConnect = "";
    private $DBName = "";
    private $TableName = "";
    private $OrderTable = array();
    private $ItemCount = 0;
    public $TempArray = array();
    public $Orders = array();
    public $count = 0;

    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();
       while($Row) {
          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]."&Ic=".$this->ItemCount."'>Add</a></td></tr>";
          $Row = $QueryResult->fetch_row();
       }
       echo "</table>";
    }

    public function addItem() {
       $ProdID = $_GET["productID"];
       for($i=0;$i<count($this->Orders);$i++) {
          if($this->Orders[$i][0]==$ProdID) {
             exit("<p>You already selected that item!  Click your browser's Back button to return to the previous page</p>");
          }
       }
       $this->Orders[$this->ItemCount][0] = $ProdID;
       $this->Orders[$this->ItemCount][1] = $this->TableName;
       $this->OrderTable[$ProdID] = $this->TableName;
       $this->ItemCount++;
    }

    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() {
       $sw = 0;
       for($i=0;$i<count($this->Orders);$i++) {
          $Order = $this->Orders[$i];
          $Key = $Order[1]?$Order[1]:"users";
          $Value = $Order[0]?$Order[0]:"";
          $OrderQTY = 2;
          if($Key && $Value) {
             if($sw == 0) {
                echo "<table width='100%' border='1'><tr><th>Remove Item</th><th>Product</th><th>Quantity</th><th>Price Each</th></tr>";
                $sw = 1;
             }
             $SQLstring = "SELECT * FROM ".$Key." WHERE productID='".$Value."'";
             $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;
             $Total_total += $Total;
             $Total=0;
             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]);
             $found=TRUE;
             $this->count++;
          }
       }
       if(!$found){echo "<table width='100%' border='1'><tr>";}
       else {
          $link = $Total_total ? "<a href='ShowCart.php?PHPSESSID=".session_id()."&operation=emptyCart'><strong>Empty Cart</strong></a>" : "";
          echo "<tr><td align='center'>$link</td>";
       }
       echo "<td colspan='2'><strong>&nbsp;&nbsp;&nbsp;Your shopping cart contains ".$this->count." product(s).</strong></td>";
       printf("<td align='center'><strong>Total: $%.2f</strong></td></tr>", $Total_total);
       echo "</table>";
       $this->count = 0;
    }

    public function removeItem() {
       $ProdID = $_GET['productID'];
       for($i=0;$i<count($this->Orders);$i++) {
          if($this->Orders[$i][0] == $ProdID) {
             $this->Orders[$i][0] = "";
             $this->Orders[$i][1] = "";
          }
       }
    }

    public function emptyCart() {
       for($i=0;$i<count($this->Orders);$i++) {
          $this->Orders[$i][0] = "";
          $this->Orders[$i][1] = "";
       }
    }
}
?>

ShowCart.php

PHP Code:


			
<?php
session_start();
require_once("ShoppingCart.php");
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>
<?php
include "links.html";
$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>

links.html

HTML Code:

<h1>Gosselin Gourmet Goods</h1>
<h2>Shop by Category</h2>
<p><a href="GosselinGourmetGoods.php?table=users&product=Gourmet Coffees">Gourmet Coffees</a><br />
<a href="GosselinGourmetGoods.php?table=users1&product=Specialty Olives">Specialty Olives</a><br />
<a href="GosselinGourmetGoods.php?table=users2&product=Gourmet Spices">Gourmet Spices</a></p>

In links.html replace users, users1 and users2 with your table names.

When you remove items from the Cart or empty the Cart, it doesn't unset that array element it just NULLs the elements for that item.
So when you fetch the items from the Orders array, you neeh to fetch only the elements that have value.

The Orders array will look like this:

Product ID: $Orders[row][0]
Table name: $Orders[row][1]

The only thing this program doesn't do yet is allow you to adjust the quantity from the program.