Unable to update/insert on mySQL DB using PHP/PDO

Question

I'm having trouble doing updates and inserts on my database using PDO in PHP. The error code notes a success, but the expected changes aren't reflected in the database.

Here's where I set up my connection:

    $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
    $db = new PDO($dsn, DB_USER, DB_PASS);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);

I can connect and SELECT on things just fine, but it won't update or insert at all. I've tried just using a hard-coded string, but not even that will work. Here's the code for that:

     $ins = "insert into choice_history (id_choice_history,choice_num,choice_taken) values ( 0, 10, 28);";
     if($stmt = $this->_db->prepare($ins))
     {
        $status  = $stmt->execute();
        return  $errorcode = $stmt->errorCode();
     }

I have a similar update string but they both have the same results.

Table definition (it has no constraints):

mysql> describe choice_history;
+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| id                | int(11) | NO   | PRI | NULL    | auto_increment |
| id_choice_history | int(11) | NO   |     | NULL    |                |
| choice_taken      | int(11) | YES  |     | NULL    |                |
| choice_num        | int(11) | YES  |     | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

The error code for this is always 00000 (success). I've also tried calling exec() with the same string, but no dice. I can paste that query into mysql on my server and it executes fine, but from PDO nothing happens.

At this point I'm at a loss. PDO reports success but nothing happens in the database. How can I figure out where the problem is?

Mike Brant · Accepted Answer · 2013-01-18 21:50:06Z

2

You have turned auto-commit off, so you need to explicitly commit() all transactions.

Your queries are successful (thus the results you are seeing), they are just never committed.

If you don't want to have to explicitly commit every request, you need to remove this line of code:

$db->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);

answered Jan 18, 2013 at 21:50

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to update/insert on mySQL DB using PHP/PDO

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related