This is an archived copy of the forum for reference purposes

Executing multiple Update Query

posted by emadgh 8 years ago

Hi, I need to update several records. but storing these data take time. I need to store them faster.

The first code I wrote for this was

foreach ($items as $key => $value)
{
	$loan = new loan($key);
	$loan -> setStatus($value);
	$loan -> store();
}

This took 1.2s.

So then I wrote this

$statment = $db->prepare("UPDATE `loans` SET `status`=%s WHERE id=%i;");

foreach ($items as $key => $value)
{
	$db->execute($statment,$value,$key);
}

This took 0.8s. but it's not enough So I wrote This one. But there is no diffrent between this and the upper one.

foreach ($items as $key => $value)
{
	$sd .= "UPDATE `loans` SET `status`='$value' WHERE id=$key;";
}
$db->execute($sd);

Please, Give me some Help to make this faster.

Sorry for my bad English.

Thanks in Advance...

You should check your database design & indexes first.

posted by theyouyou 8 years ago

Hmmm, I didn't think about this.

Thanks...

posted by emadgh 8 years ago

How many items are there that you are looping through?

In addition, it is very dangerous to write code like this:

foreach ($items as $key => $value)
{
    $sd .= "UPDATE `loans` SET `status`='$value' WHERE id=$key;";
}

It is vulnerable to SQL injection since the values are not being properly escaped. You should always use the percent placeholders, either with a direct fDatabase::execute()/fDatabase::query() call, or the prepared statement like you have.

foreach ($items as $key => $value) {
    $db->execute("UPDATE loans SET status = %s WHERE id = %i", $value, $key);
}

posted by wbond 8 years ago