Update with a LIMIT in zend framework

Question

Possible Duplicate:
How do I add a limit to update-query in Zend Framework?

I would like to do a update and place LIMIT 1 as precaution. Here is my code:

$vals = array();
$vals['status'] = 'closed';
$where = $write->quoteInto('id =?', $orderID);

$write->update("order", $vals ,$where);

Where can i add a LIMIT into this query?

(looks like has been asked in the past but i hope someone out there has the answer)

Thanks max. That's a question from 09 any chance something been added since then? this is a pretty common programming practice there must be a way to do this. — Gershon Herczeg, May 23 '12 at 14:54
I can see why you think this might be "safer" but, if you're really worried about your where clause not uniquely identifying a record, you should actually check the data before updating & report an error condition, rather than leaving broken, inconsistent data in the DB. If you can't trust your primary key to be unique, you've got bigger problems than accidentally updating more than one table. — Sean McSomething, May 23 '12 at 17:18
Yes Sean what is true is true and this is more of a limiting disaster in case logic fails, but really unnecessary . — Gershon Herczeg, May 23 '12 at 18:07

score 1 · Answer 1 · answered May 23 '12 at 15:34

Hmmm... My Zend is rusty, but I think in the past I have used: used http://framework.zend.com/manual/en/zend.db.statement.html to execute SQL (selects/inserts/updates) as normal sql statements...

But yes all the old and current info is correct - update() has not ability to place a 'limit' - you just need to hope you designed the DB properly and such tables won't have duplicate keys!

Also... 'order' is a REALLY bad table-name :) with 'order' being a reserved word in MySQL ( Indeed any DB! ).

good point about 'order' i just put that up as sample code, but nice catch :) — Gershon Herczeg, May 23 '12 at 15:49

score 1 · Accepted Answer · answered May 24 '12 at 10:10

It looks like you're using Zend_Db_Adapter to perform your queries so I'm not sure you can do what I do, anyway here goes.
I usually use the Zend_Db_Table_Row save() method to insert and update records, however I also use the DbTable models to provide access to the Table and Table_Row abstract api's.

public function save(Music_Model_Artist $artist) {
        //if id is not null
        if (!is_null($artist->id)) {
            //find row, uses fetchRow() so will return NULL if not found
            $row = $this->findById($artist->id);
            $row->id = $artist->id;
        } else {
            //if ID is null create new row
            $row = $this->_getGateway()->createRow();
        }
        //assign data to row
        $row->name = $artist->name;
        //save new row or update
        $row->save();
        //return the row, in case I need it for something else
        return $row;
    }

what this amounts to is that if I include and ID in the data array (or in this case an entity object) the row will be updated and if there is no ID in the object a new row will be created.

If your curious here's how I __construct the class:

public function __construct(Zend_Db_Table_Abstract $tableGateway = NULL) {
        //pass in concrete DbTable Model, parent alts to using table name string to construct class
        $this->_tableGateway = new Application_Model_DbTable_Artist();
        parent::__construct($tableGateway);
    }

Hope this provides some help.

To make it clear, `Zend_Db_Table_Row` does NOT use the `LIMIT` either. The correct answer should be "**No, there is no way to inlude `LIMIT` in Zend**". _Instead, `Zend_Db_Table_Row` uses PRIMARY keys to ensure only one row is updated._ — Radek Pech, Jan 18 '17 at 09:12

score 0 · Answer 3 · answered May 23 '12 at 15:52

Ok being that it looks like there isnt a way to do this i just decided to use the straingt query functionality. I still used the where built functionality, to remove any funny unwanted values from the WHERE.

$where = $write->quoteInto('id =?', $order_id);
$write->query("UPDATE orders SET status = 'closed' WHERE $where LIMIT 1");

Update with a LIMIT in zend framework

3 Answers3