How to pass an SQL expression for an UPDATE to Zend?

Asked

Viewed 259 times

2

My Mysql DBMS has a table called elements where one of the fields is called posicao and is like int (6).

I need to update (SQL) where the field posicao shall be updated with its own value by subtracting 1 according to the clause where. The following query (SQL) executes this:

UPDATE elementos
SET posicao = (posicao - 1)
WHERE posicao > 9

Converting this query for use in the model, one of the attempts I made was this, but it did not roll: 

$db = new Application_Model_DbTable_Elemento();
$dados = array(
'posicao' => (posicao - 1)
);
$where = $db->getAdapter()->quoteInto('posicao > ?', 9);
$db->update($dados, $where);

I believe the problem is in the data defined in the array $dados. What would be the syntax to execute this UPDATE?

php mysql sql zend mvc

  • In the PHP code, (posicao - 1) should be string, right? Or it will give syntax error.

  • Hi @bfavaretto, hi. If I replace (position - 1) with a string the calculation will not be executed and there will be an incorrect update in the table, because the field is of type int (6).

  • Look, I don’t use Zend and I would personally end up doing the query at hand, but I think I found your answer here: http://stackoverflow.com/a/12267340/825789

  • That’s right there @bfavaretto! Now it’s cool. Thanks for the tip from me!

  • Ok, posted the solution as an answer so it can be useful to future site visitors.

  • Xi, was I too quick? If you want to keep your answer, I delete mine.

  • Not @bfavaretto, it’s perfect. Once more!

Show 2 more comments

1 answer

1


From what I saw in a reply in English, to pass an expression like this to Mysql it is necessary to create an object of type Zend_Db_Expr. With that your code would look like this:

$db = new Application_Model_DbTable_Elemento();
$dados = array(
    'posicao' => new Zend_Db_Expr('posicao - 1')
);
$where = $db->getAdapter()->quoteInto('posicao > ?', 9);
$db->update($dados, $where);

Browser other questions tagged php mysql sql zend mvc

You are not signed in. Login or sign up in order to post.