PHP Script to update multiple columns in a MySQL Table

Question

I need to update multiple columns in a single table in a single database. Right now when I run my PHP script it only completes the first UPDATE command, it does not update the entries following it. I'm not sure what is wrong with it:

<?php
$servername = "localhost";
$username = "user";
$password = "pw";
$dbname = "database";

//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//Check connection
if($conn->connect_error) {
        die("Connection failed:" . $conn->connect_error);
}

$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=10";
$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=17";
$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=22";

if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
} else {
        echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

When I run it from command line, it does not error out, it does run the first update just fine. When I go check on the database it has in fact updated the "team_id" to '15' in the "topic_id" column that has the id of "10", but the other two columns are not updated. Why is this?

you have to call mysql_query once for each sql statement. currently you're overwriting the statements with one another, so only the last is actually sent to the db (not the first) — Jakumi
– Jakumi, Commented Jan 8, 2019 at 17:49
Why not use WHERE topic_id IN (10, 17, 22) to do them all in one query? — Barmar
– Barmar, Commented Jan 8, 2019 at 17:50
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Jan 8, 2019 at 17:54

tadman · Accepted Answer · 2019-01-08 17:54:20Z

3

You're defining the same variable over and over again, stomping the previous version. Only the last definition "sticks" and is run.

Either switch to an array:

// Define an array
$sql = [ ];
// Append to the array each query
$sql[] = "UPDATE ost_help_topic SET team_id='15' WHERE topic_id=10";
$sql[] = "UPDATE ost_help_topic SET team_id='15' WHERE topic_id=17";
$sql[] = "UPDATE ost_help_topic SET team_id='15' WHERE topic_id=22";

foreach($sql as $q) {
  // Run query.
}

Or use the IN (...) approach:

UPDATE ost_help_topic SET team_id='15' WHERE topic_id IN (10,17,22)

Where one query can update multiple values to the same thing.

answered Jan 8, 2019 at 17:54

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

Pietro Aretino Over a year ago

thank you! I was not aware of the IN command. Extremely useful.

tadman Over a year ago

Technically it'd be called a "clause" as part of a "WHERE condition". A "command" is something like SELECT or INSERT, usually the first word of the SQL statement. This might seem like an academic distinction, but the right terminology helps explain your requirements better, as well as what you've done to others.

Recigio Poffo · Accepted Answer · 2019-01-08 17:54:06Z

You need to execute every line:

//1
$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=10";

if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
} else {
        echo "Error updating record: " . mysqli_error($conn);
}

//2
$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=17";

if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
} else {
        echo "Error updating record: " . mysqli_error($conn);
}

//2
$sql="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=22";

if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
} else {
        echo "Error updating record: " . mysqli_error($conn);
}

or create an array

$arra = array();
$arra[]="UPDATE ost_help_topic SET team_id='15' WHERE topic_id=10";
$arra[]="UPDATE ost_help_topic SET team_id='11' WHERE topic_id=12";
$arra[]="UPDATE ost_help_topic SET team_id='12' WHERE topic_id=13";

and foreach:

foreach ($arra as $sql) {

   if (mysqli_query($conn, $sql)) {
            echo "Record updated successfully";
    } else {
            echo "Error updating record: " . mysqli_error($conn);
    }
}

Djellal Mohamed Aniss · Accepted Answer · 2019-01-08 18:41:08Z

0

using simply an "OR" operator will fix it!

$sql = "UPDATE ost_help_topic SET team_id='15' WHERE topic_id=10 OR topic_id=12 OR topic_id=13"

answered Jan 8, 2019 at 18:41

Djellal Mohamed Aniss

1,74314 silver badges24 bronze badges

Collectives™ on Stack Overflow

PHP Script to update multiple columns in a MySQL Table

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related