best way to delete multiple records from table?

TheZend · #1 (**permalink**) 08-16-2008, 08:44 PM

Hi,

Relative newbie here...trying to figure out the best way to delete multiple records from a single table.

the only way I've been able to get all the syntax to work is the function listed below here. I'm passing to it the database connection and an array of the id's to be deleted from a table called 'event'.

Code:

public function DeleteMultiple($db, $aDeleteList)
{		
	$count = 0;
	foreach ($aDeleteListas $value)
	{	
		$where = $db->quoteInto('id = ?', $value);
		$db->delete('event', $where);
		$count++;
		}
	return $count;
}

I'm sure there is a more efficient way to do this...something like:

Code:

$del_id_list = mysql_real_escape_string(implode(',', $_POST['checkbox']));

$sql = "DELETE FROM $tbl_name WHERE queueID IN ($del_id_list)";

any advice?
Thanks!

meman1188 · #2 (**permalink**) 08-17-2008, 11:50 PM

Quote:

Originally Posted by TheZend

Hi,

Relative newbie here...trying to figure out the best way to delete multiple records from a single table.

the only way I've been able to get all the syntax to work is the function listed below here. I'm passing to it the database connection and an array of the id's to be deleted from a table called 'event'.

Code:

public function DeleteMultiple($db, $aDeleteList)
{		
	$count = 0;
	foreach ($aDeleteListas $value)
	{	
		$where = $db->quoteInto('id = ?', $value);
		$db->delete('event', $where);
		$count++;
		}
	return $count;
}

I'm sure there is a more efficient way to do this...something like:

Code:

$del_id_list = mysql_real_escape_string(implode(',', $_POST['checkbox']));

$sql = "DELETE FROM $tbl_name WHERE queueID IN ($del_id_list)";

any advice?
Thanks!

The delete method provided in Zend_DB does not support multiple deletes, but you could execute the second query you generated directly.

*Wouldn't let me post the code i guess for security reasons but check out the link below for how to execute quires directly.
Zend Framework: Documentation

TheZend · #3 (**permalink**) 08-18-2008, 03:06 AM

Hmm, do you mean build a sql string dynamically, then execute it like-a dis?:

Code:

<?php
require_once 'Zend/Db/Statement/Mysqli.php';
$sql = 'DELETE* FROM my_table WHERE id = ?';
$stmt = new Zend_Db_Statement_Mysqli($db, $sql);
// and then run the execute() method...but...
...
?>

I understand the above, but when it comes time for the execute() method, it is expecting an array of values, one per variable in the $sql WHERE statement, like if your $sql read:

Code:

$sql = 'DELETE* FROM bugs WHERE reported_by = ? AND bug_status = ?';

then your execute would expect two values in the array, ie:

Code:


$stmt->execute(array('goofy', 'FIXED'));

but to delete multiple records using a comma delimited list or an array of id's, what would be the syntax in this case?

Code:

$del_id_list = implode(',', $_POST['checkbox']));  // create string comma separated id's
$stmt->execute(array($del_id_list);

or just:

Code:

 
$stmt->execute($del_id_list);

Not sure which is the right path here,
Thanks so much!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode