pdo mysql update not updating rows with no errors

Question

I have an array called "data":

    Array
    (
        [0] => Array
            (
                [ID] => 75538
                [stock] => In Stock
                [Price] => 82.6900
            )

        [1] => Array
            (
                [id] => 75536
                [stock] => In Stock
                [Price] => 72.0000
            )
            ............(cont...)

I am running an update query as follows:

    $conn->beginTransaction();
    $sql  = "UPDATE products SET stock_status = ?,price = ? WHERE id= ?";

    try {
    $stmt = $conn->prepare($sql);
    foreach ($data as $v){
     $stmt->execute(array_values($v));
    }//end foreach
    $conn->commit();
    } catch (PDOException $e) {
          echo 'Update failed: ' . $e->getMessage();
         exit;
           }
    echo "Database was updated successfully";
    $stmt->closeCursor(); 
    $conn = NULL;

I am always getting "Database was updated successfully" message and data is not getting updated. In a empty table too i get the same result. There are no errors logged.

mysql column stock_status is varchar and price is decimal(10,4), id is varchar too... Please ignore the errorinfo part as i need to change it.

Is there anything wrong with my pdo update query ?? Help sought..

Update:

var_dump($data) gives price as string:

array(36828) {
      [0]=>
      array(3) {
        ["ID"]=>
        string(5) "75538"
        ["instock"]=>
        string(8) "In Stock"
        ["Price"]=>
        string(7) "82.6900"
      }

sample DB structure:

 CREATE TABLE IF NOT EXISTS `products` (
      `product_id` int(5) NOT NULL AUTO_INCREMENT,
      `id` varchar(10) COLLATE latin1_general_cs NOT NULL,
      `price` decimal(10,4) NOT NULL,
      `stock_status` varchar(10) COLLATE latin1_general_cs NOT NULL DEFAULT 'In Stock',
      PRIMARY KEY (`product_id`),
      UNIQUE KEY `id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs  AUTO_INCREMENT=1 ;

Update:

I have tried this too, but still no updates and no errors. Still getting success message.

    $stmt = $conn->prepare('UPDATE products SET `stock_status`=:stockstatus,`price`=:price  WHERE `id`=:id');
    foreach ($data as $v){
    $stmt->bindParam(':stockstatus', $v['instock'], PDO::PARAM_STR);
    $stmt->bindParam(':price', $v['Price'], PDO::PARAM_INT);
    $stmt->bindParam(':id', $v['id'], PDO::PARAM_STR);
    $stmt->execute();

Update:

I already have exceptions set in my connection connstring file as:

     $conn = new PDO($connStr, $user, $pass); 
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

And still getting no error message ????

Update: Issue resolved.

Sorry. My oversight. It was $v['ID'] and not $v['id'] in my code. The important thing was that it was not producing an error.

Won't you think that the sequence of the array values should match the sequence of your parameters? — VMai, Jun 19 '14 at 11:03
Your column `price` should be of the decimal type, id probably one of the integer types. It's a good idea to use the best type according to your data. — VMai, Jun 19 '14 at 11:05
You are only checking for a very specific error. After youf `if ($e... == 1062)` add `else echo "PDO ERROR: "; var_dump($e->errorInfo()); `. Also, your "successfull" message is always printed, regardless of the result. move it into the try block. — Gerald Schneider, Jun 19 '14 at 11:07
Double check that you bind your parameters correctly. Maybe the order of your array are not right? — Jacob Cohen, Jun 19 '14 at 11:09
Can you try quoting your array in the stock key? [stock] => "In Stock" — Jacob Cohen, Jun 19 '14 at 11:11
Please note that I have already provided details of array on top... — user3737431, Jun 19 '14 at 11:17
@user3737431 please consider: `array_values($v)` will result (first round) in array `(75538, "In Stock", 82.6900)` and will bound to the positional parameters. Your id will be bound to field `stock_status`, your stock status to field `price` and your price to the field `id` of your `WHERE` clause. That's not what you want. — VMai, Jun 19 '14 at 11:44
@user3737431 put your values in the right order, i.e. with `$stmt->execute(array($v['stock'], $v['Price'], $v['id']));` — VMai, Jun 19 '14 at 11:51
if you get no error message nor have data chenged, then there is no data to match WHERE condition, or no data to change. — Your Common Sense, Jun 19 '14 at 13:09
@Your Common Sense If i run the query in phpmyadmin i am getting updated results. This is a pdo issue which i am not able to identify. — user3737431, Jun 19 '14 at 13:56
I don't know how this question is marked as duplicate by @Your Common Sense — user3737431, Jun 19 '14 at 13:58
this is marked as duplicate because there are NO issues with PDO. It works for everyone. If you want to claim there is an issue with PDO, you are welcome to write a **reproduceable** code case, from creating a table to updating that doesnt work - so, everyone could copy and run it and confirm the issue with PDO — Your Common Sense, Jun 19 '14 at 14:01
@Your Common Sense Please note that there is some issue somewhere which I am unable to locate and seeking help. Please do understand. There must be a valid reason for the records not getting updated. — user3737431, Jun 19 '14 at 14:23
I **already** explained **all** the possible reasons: there is either no data to match WHERE condition, or no data to change. **check them** — Your Common Sense, Jun 19 '14 at 14:28
My god! It was $v['SKU'] and not $v['sku'] in my code. i feel ashamed..Still nobody could detect it:. — user3737431, Jun 19 '14 at 14:29
Humans **AREN'T supposed** to run the code and detect non-existent variables. Its computer's job. — Your Common Sense, Jun 19 '14 at 14:31
If you had proper error reporting on, you'd had your error massage about non-existent variable, and wouldn't blame PDO for your own mistake. Always have error_reporting(E_ALL); — Your Common Sense, Jun 19 '14 at 14:32
@ Your Common Sense I already have error reporting enabled for PDO. Please see my original post. anyway thanks for the help. — user3737431, Jun 19 '14 at 14:53
1. **Your error has nothing to do with PDO.** 2. You need to have error reporting enabled for **PHP**. — Your Common Sense, Jun 19 '14 at 14:55

Gerald Schneider · Answer 1 · 2014-06-19T11:35:13.703

0

This output is strange: [Price] => 72.0000. It looks like the value is a string. If it were a decimal value it should be displayed as 72.0. PDO will then handle it as a string, which could collide with your column definition in the database.

The same problem could be with the id ... if it is an integer in PHP and a varchar in the database it will not work.

You can check this by using var_dump() instead of print_r(), it gives more detailed information.

Update:

Try this:

foreach ($data as $v){
  $v['Price'] = floatval($v['Price']);
  $stmt->execute(array_values($v));
}

edited Jun 19 '14 at 11:35

answered Jun 19 '14 at 11:19

Gerald Schneider

17,416
9
60
78

I created an array out of a csv file from outside. – user3737431 Jun 19 '14 at 11:30
Updated the answer again. – Gerald Schneider Jun 19 '14 at 11:35
Still same issue. No errors..no updates.. foreach ($data as $v){ $stmt->bindParam(1, $v[0], PDO::PARAM_STR); $stmt->bindParam(2, $v[1], PDO::PARAM_STR); $stmt->bindParam(3, $v[2], PDO::PARAM_INT); $stmt->execute(); }//end foreach – user3737431 Jun 19 '14 at 11:35
Please try the latest suggestion (you might need to reload the question to see the edits). – Gerald Schneider Jun 19 '14 at 11:39
Your ideas sounds quite strange. In your place I would verify such statements like "could collide" and "will not work" on a some sort of workbench first. – Your Common Sense Jun 19 '14 at 12:57

pdo mysql update not updating rows with no errors

1 Answers1