PHP script runs an SQL update command and it's beeing ignored

Question

I have a custom import and update products script for Magento. The script worked fine for a long time, I changed some stuff in the script but a command I did not change stopped working

This is the code that stopped working:

require_once("connArray.php");
$dbConfig = GetConn();
$db = Zend_Db::factory('Pdo_Mysql', $dbConfig);
$update = "UPDATE catalog_product_entity_decimal p_dec
       SET  p_dec.value = $dec_value
       WHERE  p_dec.entity_id = $entity_id AND p_dec.attribute_id = 64";
echo $update;
echo "<br>";
$result = $db->query($update);
var_dump($result);
echo "<br>";

The output is:

UPDATE catalog_product_entity_decimal p_dec SET p_dec.value = 30.8 WHERE p_dec.entity_id = 2 AND p_dec.attribute_id = 64
object(Zend_Db_Statement_Pdo)#451 (9) { ["_fetchMode:protected"]=> int(2) ["_stmt:protected"]=> object(PDOStatement)#440 (1) { ["queryString"]=> string(124) "UPDATE catalog_product_entity_decimal p_dec SET p_dec.value = 30.8 WHERE p_dec.entity_id = 2 AND p_dec.attribute_id = 64" } ["_adapter:protected"]=> object(Zend_Db_Adapter_Pdo_Mysql)#33 (12) { ["_pdoType:protected"]=> string(5) "mysql" ["_numericDataTypes:protected"]=> array(16) { [0]=> int(0) [1]=> int(1) [2]=> int(2) ["INT"]=> int(0) ["INTEGER"]=> int(0) ["MEDIUMINT"]=> int(0) ["SMALLINT"]=> int(0) ["TINYINT"]=> int(0) ["BIGINT"]=> int(1) ["SERIAL"]=> int(1) ["DEC"]=> int(2) ["DECIMAL"]=> int(2) ["DOUBLE"]=> int(2) ["DOUBLE PRECISION"]=> int(2) ["FIXED"]=> int(2) ["FLOAT"]=> int(2) } ["_defaultStmtClass:protected"]=> string(21) "Zend_Db_Statement_Pdo" ["_config:protected"]=> array(8) { ["host"]=> string(9) "localhost" ["username"]=> string(16) "***" ["password"]=> string(9) "***" ["dbname"]=> string(17) "***" ["driver_options"]=> array(1) { [1002]=> string(14) "SET NAMES UTF8" } ["charset"]=> NULL ["persistent"]=> bool(false) ["options"]=> array(2) { ["caseFolding"]=> int(0) ["autoQuoteIdentifiers"]=> bool(true) } } ["_fetchMode:protected"]=> int(2) ["_profiler:protected"]=> object(Zend_Db_Profiler)#20 (4) { ["_queryProfiles:protected"]=> array(0) { } ["_enabled:protected"]=> bool(false) ["_filterElapsedSecs:protected"]=> NULL ["_filterTypes:protected"]=> NULL } ["_defaultProfilerClass:protected"]=> string(16) "Zend_Db_Profiler" ["_connection:protected"]=> object(PDO)#32 (0) { } ["_caseFolding:protected"]=> int(0) ["_autoQuoteIdentifiers:protected"]=> bool(true) ["_allowSerialization:protected"]=> bool(true) ["_autoReconnectOnUnserialize:protected"]=> bool(false) } ["_attribute:protected"]=> array(0) { } ["_bindColumn:protected"]=> array(0) { } ["_bindParam:protected"]=> array(0) { } ["_sqlSplit:protected"]=> array(1) { [0]=> string(124) "UPDATE catalog_product_entity_decimal p_dec SET p_dec.value = 30.8 WHERE p_dec.entity_id = 2 AND p_dec.attribute_id = 64" } ["_sqlParam:protected"]=> array(1) { [0]=> string(124) "UPDATE catalog_product_entity_decimal p_dec SET p_dec.value = 30.8 WHERE p_dec.entity_id = 2 AND p_dec.attribute_id = 64" } ["_queryId:protected"]=> NULL }

After the script runs, I check the DB and the relevant row didn't change.
I've copied the outputed query to phpMyAdmin and it works fine there.

A select command that I run just before the above code runs fine and I have another update command in the script that does work, so it doesn't look like a connection problem.

Thanks in advance.

///////////////////////////////////// UPDATE //////////////////////////////////////////////

I've tried to change the code to :

$data = array(
        'value'      => $dec_value
    );

    $where[] = "entity_id = " . $entity_id;
    $where[] = "attribute_id = 64";

    $result = $db->update('catalog_product_entity_decimal', $data, $where);
    var_dump($result);
    echo "<br>";

but it still doesn't work. $result output is int(1).

I've tried to add $db->beginTransaction() and $db->commit() (even though that by default, all Zend_Db Adapter classes operate in auto-commit mode) but I get an error : "SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction"

What are you doing for error checking? Have you tried running the query in an interface like phpMyAdmin? — Pekka
I ran it in phpMyAdmin and it worked fine (row changed). I don't get any errors. — Shani1351
Not sure how zend works, if it's the same as php PDO, but try update instead of query. framework.zend.com/manual/en/zend.db.adapter.html, you can also try $db->commit(); Looks like it doesn't commit unless you tell it too. It will attempt the query, but not commit yet. Look at example #23 and #31 — Matt
Tried to use update instead of query but it didn't help. Tried to add $db->beginTransaction() and $db->commit() (even though the article you gave says "By default, all Zend_Db Adapter classes operate in auto-commit mode.") but I get an error : SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction — Shani1351
I fixed the problem. It was a bug in my code (I loaded the product object before the price change and I saved it after the price change so the object had the old price...) — Shani1351

Shani1351 Shani1351 · Accepted Answer · 2011-11-13T13:49:22

I fixed the problem. It was a bug in my code (I loaded the product object before the price change and I saved it after the price change so the object had the old price...)

PHP script runs an SQL update command and it's beeing ignored

1 Answers