CODEWITHSUNDEEP
phpmysqlpdo
Justin White
Justin White

Reputation: 714

Trouble with PHP, PDO, and MySQL

I'm building a script to add an item to a shopping cart..

I want to first check to see if there's an existing cart table for the user. If not, I want to create a new table for the shopping cart.

If there is already a table, I want to see if the current item is already in it. If it is, then I just want to update the quantity. If it's not already in the table, then I want to add a new row.

In the code below, the UPDATE statement is firing whether there is an itemNumber match or not. This means that there's no exception, and that the new row is not being inserted for new the new itemNumber. Everything else is working as expected.

try {

    // create the shopping cart. If it already exists, throw an exception

    $cartDb->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); //Error Handling
    $sql = 'CREATE TABLE `' . $table . '` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `itemNumber` VARCHAR(100) NOT NULL, `title` TEXT NOT NULL, `price` VARCHAR(20) NOT NULL, `color` VARCHAR(100) NOT NULL, `size` CHAR(20), `quantity` INT(5) NOT NULL, `category` TEXT NOT NULL, `photo` TEXT NOT NULL);';
    $cartDb->exec($sql);

    try {

        // if you get this far then the cart has been newly created. Insert the item information into the cart

        $cartDb->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $sql = 'INSERT INTO `' . $table . '` (`itemNumber`, `title`, `price`, `color`, `size`, `quantity`, `category`, `photo`) VALUES ("' . $itemNumber . '", "' . $title . '", "' . $price . '", "' . $color . '", "' . $size . '", "' . $quantity . '", "' . $category . '", "' . $photo . '");';
        $cartDb->exec($sql);

    } catch(PDOException $e) {

        // we got an exception == could not insert the data into the table

        echo $e->getMessage();

    }

} catch(PDOException $e) {

    // we got an exception == table already exists

    echo $e->getMessage();

    try {

        // first see if the item number is already in the table

        $cartDb->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $sql = "UPDATE `" . $table . "` SET `quantity` = `quantity` + " . $quantity . " WHERE `itemNumber` = '" . $itemNumber . "'";
        $cartDb->exec($sql);

    } catch(PDOException $e) {

        // exception thrown == item number is not yet in the table

        echo $e->getMessage();

        try {

            $cartDb->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $sql = 'INSERT INTO `' . $table . '` (`itemNumber`, `title`, `price`, `color`, `size`, `quantity`, `category`, `photo`) VALUES ("' . $itemNumber . '", "' . $title . '", "' . $price . '", "' . $color . '", "' . $size . '", "' . $quantity . '", "' . $category . '", "' . $photo . '");';
            $cartDb->exec($sql);

        } catch(PDOException $e) {

            echo $e->getMessage();

        }

    }   

}

Upvotes: 0

Views: 56

Answers (2)

R.P Silveira
R.P Silveira

Reputation: 402

You have to change the order of the statements: Simply put the INSERT before the UPDATE, because the INSERT will catch an exception if the record already exists. The UPDATE won't.

Upvotes: 0

camelCase
camelCase

Reputation: 5598

I would assign the value of your exec() statement to a variable like: $return = $cartDb->exec($sql); then echo that variable.

I believe if no rows are updated then it returns 0 for the count of the affected rows and not an exception.

Upvotes: 1

Related Questions