CODEWITHSUNDEEP
phpmysql
Leo Jiang
Leo Jiang

Reputation: 26105

Updating multiple MySQL records using one mysqli_query call in PHP

I have to update hundreds of records at once and I'm wondering if it's possible to use one mysqli_query call to update all of them. I know it isn't possible to use something like:

UPDATE table SET col=value1,col=value2,col=value3 WHERE id IN (1,2,3)

Is it possible to somehow use custom functions or another method to update multiple records at once?

Upvotes: 0

Views: 961

Answers (3)

Kirk Logan
Kirk Logan

Reputation: 783

If im understanding correctly you want to update your columns with different data based on different conditions, so:

col = value1 WHERE id = 1
col = value2 WHERE id = 2
col = value3 WHERE id = 3

etc...

If ive misinterpreted stop here.

I would loop through the data in PHP and execute multiple queries, but if you are adamant on doing it in one query, you could do something like the following with nested IF statements.

$query = "UPDATE table 
          SET col = 
          IF(
              id IN (1),value1,  
              IF(id IN (2),value2, 
              IF id IN (3),value3,NULL)
            )
            WHERE id IN (1,2,3)"

If you have a large amount of conditions then I would just stick with multiple queries in PHP. MySQL can handle a few hundred queries at once, so i really wouldnt worry on that front.

Upvotes: 0

PravinS
PravinS

Reputation: 2584

use mysqli_multi_query() function, by which you can concat all the queries and pass it the mysqli_multi_query function

Upvotes: 1

Logan Wayne
Logan Wayne

Reputation: 5991

Answer is Yes! You can do it in one process via one file.

<?php

$connection=mysqli_connect("YourHost","UserName","Password","YourDatabase");

if(mysqli_connect_errno()){

echo "Error".mysqli_connect_error();
}

mysqli_query($connection,"UPDATE table SET col='value1' WHERE id='1'");
mysqli_query($connection,"UPDATE table SET col='value2' WHERE id='2'");
mysqli_query($connection,"UPDATE table SET col='value2' WHERE id='3'");
?>

You can customize and select rows you wanted to update with certain conditions by using WHERE

Upvotes: 0

Related Questions