Zend_Db - Building a Delete Query

Question

I'm trying to recreate the following in Zend Framework and am not sure how to do it:

DELETE FROM mytablename WHERE date( `time_cre` ) < curdate( ) - INTERVAL 4 DAY

I was thinking something like:

$table = $this->getTable();
$db = Zend_Registry::get('dbAdapter');
$db->delete($table, array(
    'date(`time_cre`) < curdate() - interval 4'
));

Does that seem correct?

What is the best way to handle something like this?

EDIT: Ack! Sorry, I was adapting this from a SELECT I was using to test and didn't change the syntax properly when I pasted it in. I've edited the example to fix it.

@rg88 it's not clear what you need to delete. The syntax of delete is DELETE FROM [table_name] — criticus
– criticus, Commented Mar 3, 2011 at 19:03
what dbms are you using (I would assume mysql?) Your syntax is non-standard and rather odd. If you only want to remove data from one column you should use UPDATE and set the column to NULL,'',0, etc. DELETE is specifically for removing entire rows, unless your dbms supports something i'm unfamiliar with. — Mr Griever
– Mr Griever, Commented Mar 3, 2011 at 19:05
From where you get that syntax: DELETE [something] FROM table [where] ? Neither MySQL, PostgreSQL or Oracle uses that. After DELETE you don't need anything, because you're deleting whole row, not column value. — Radek
– Radek, Commented Mar 3, 2011 at 19:06
@criticus @Matt @singles - I failed to edit the example properly. I've updated my example.. sorry about that. — Lothar
– Lothar, Commented Mar 3, 2011 at 19:31

Lothar · Accepted Answer · 2011-03-03 23:20:29Z

2

Figured it out...

public function pruneOld($days) {
    $table = $this->getTable();
    $db = Zend_Registry::get('dbAdapter');
    $where = $db->quoteInto("DATE(`time_cre`) < CURDATE() - INTERVAL ? DAY", $days);

    return $table->delete($where);
}

$table gets an reference of the table I want to edit...

$db grabs an instance of the database adapter so I can use quoteInto()...

$where builds the main part of the query accepting $days to make things a bit more flexible.

Create an action to call this method... something like:

public function pruneoldAction() {

    // Disable the view/layout stuff as I don't need it for this action
    $this->_helper->layout()->disableLayout();
    $this->_helper->viewRenderer->setNoRender(true);

    // Get the data model with a convenience method
    $data = $this->_getDataModel();

    // Prune the old entries, passing in the number of days I want to be older than
    $data->pruneOld(2);
}

And now hitting: http://myhost/thiscontroller/pruneold/ will delete the entries. Of course, anyone hitting that url will delete the entries but I've taken steps not included in my example to deal with this. Hope this helps someone.

answered Mar 3, 2011 at 23:20

Lothar

3,5398 gold badges47 silver badges58 bronze badges

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

1 Comment

Radek Over a year ago

I assume, that $data inherits from Zend_Db_Table_Abstract. So within your pruneOld method you have access to db adapter using $this->getAdapter() - you don't have to take it from registry. Second thing - inside model you have also method delete(), which takes one parameter - where array.

Jane Yun · Accepted Answer · 2011-03-04 00:20:34Z

0

I usually do this to delete a row

$table = new yourDB;
$row = $table->fetchRow($table->select()->where(some where params));

$row->delete();

answered Mar 4, 2011 at 0:20

Jane Yun

18512 bronze badges

1 Comment

Mr Griever Over a year ago

the problem with this form is you're processing two queries, minimum (one for the select, one for the delete) to perform the delete operation. the pdo delete action accepts the same parameters and only performs one query.

Nitin · Accepted Answer · 2013-11-14 08:18:43Z

0

try: $db->delete($table, array( 'date(time_cre) < ?' => new Zend_Db_Expr('curdate() - interval 4') ));

answered Nov 14, 2013 at 8:18

Nitin

593 bronze badges

Collectives™ on Stack Overflow

Zend_Db - Building a Delete Query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related