MySQL Update SET replace with PHP mutiple queries

Question

I'm trying to run 3 lines of the same code as seen below.I've also tried the W3Schools PHP mysqli_multi_query() Function but it gives me a syntax error and only the 1-st line of code works the ExistingWord1

I also read a couple of stackoverflow posts about this and this can't figure it out

<?php
include 'words.php';
include('../config.php');
$servername = DB_HOSTNAME;
$username = DB_USERNAME;
$password = DB_PASSWORD;
$dbname = DB_DATABASE;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
/*UTF-8 format*/
$conn->set_charset("utf8");

$sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord1', '$ReplacerWord1') WHERE name LIKE '%$SearchWord1%';";
$sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord2', '$ReplacerWord2') WHERE name LIKE '%$SearchWord2%';";
$sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord3', '$ReplacerWord3') WHERE name LIKE '%$SearchWord3%';";

if ($conn->query($sql) === TRUE) {
    echo "Products updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

This is the full Code that i am trying to run as by itself it only runs the 1st $sql =""

and ignores the other 2

as for the variables in the REPLACE

$ExistingWord1 words that are in the database name
$ReplacerWord1  replaces the ExistingWord1
'%$SearchWord1%' real database names

Thanks for the help guys

Everyone in this post was really helpful i really do appriciate it.

Answer 1

Why not just nest the REPLACE functions, and get it done in one fell swoop.

  SET t.name = REPLACE(REPLACE(REPLACE( t.name, 'fee','bah'),'fi','hum'), 'fo','bug')

You don't really need a WHERE clause, if you're going to be scanning the whole table anyway.

 WHERE t.name LIKE '%fee%'
    OR t.name LIKE '%fi%'
    OR t.name LIKE '%fo%'

That's only going to require one scan through the table.

But this approach only "works" if you're not "swapping" keywords, and don't have collisions. (For example, first replacing "fee" with "foo" and the replacing "foo" with "bar"... the order the operations is important.

---

NOTES:

The REPLACE function replaces strings, not words. This is important if you are planning on running this against text or comments.

Code that incorporate potentially unsafe values in SQL text is vulnerable SQL Injection.

Potentially unsafe values must be properly escaped using e.g. mysql_real_escape_string.

Or, the preferred pattern is to use prepared statements with bind placeholders.

Enabling multiquery opens up a whole host more opportunities for even nastier SQL injection; giving the ability to slide in whole statements, like DROP TABLE statements. Don't enable multiquery until you are positive your code isn't vulnerable.

FOLLOWUP

Preferred pattern is to use prepared statement with bind placeholders.

I'd prefer doing the replacement with one scan through the table, rather than three. The LIKE '%...' predicates in the WHERE clause aren't sargable, it's gonna be a full scan anyway.

---

$sql = "UPDATE product_description SET name = REPLACE(REPLACE(REPLACE(name,?,?)?,?)?,?)
         WHERE name LIKE CONCAT('%',?,'%')
            OR name LIKE CONCAT('%',?,'%')
            OR name LIKE CONCAT('%',?,'%') ";

$sth=$conn->prepare($sql);
$sth->bind_param("sssssssss",$ExistingWord1,$ReplacerWord1
                            ,$ExistingWord2,$ReplacerWord2
                            ,$ExistingWord3,$ReplacerWord3
                            ,$SearchWord1
                            ,$SearchWord2
                            ,$SearchWord3
                );

$sth->execute();

---

(Add appropriate error checking around the prepare and execute calls. With PDO, we'd use try/catch/finally blocks.)

Answer 2

I don't know if this is the right way but upon reading your guys comments mainly @Alex's comment

I changed the variables to

$sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord1', '$ReplacerWord1') WHERE name LIKE '%$SearchWord1%';";
$A = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord2', '$ReplacerWord2') WHERE name LIKE '%$SearchWord2%';";
$B = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord3', '$ReplacerWord3') WHERE name LIKE '%$SearchWord3%';";

And added

if ($conn->query($sql) === TRUE) {
    echo "Everything worked as planned";
} else {
    echo "Error updating record: " . $conn->error;
}
if ($conn->query($A) === TRUE) {

} else {
    echo "Error updating record: " . $conn->error;
}
if ($conn->query($B) === TRUE) {

} else {
    echo "Error updating record: " . $conn->error;
}

Answer 3

According to the fragment provided only last $sql value will be executed, since you redefine same variable 3 times, only the last value stored in var $sql.

To fix that you can try:

$sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord1', '$ReplacerWord1') WHERE name LIKE '%$SearchWord1%';";
$sql .= "UPDATE product_description SET name = REPLACE(name, '$ExistingWord2', '$ReplacerWord2') WHERE name LIKE '%$SearchWord2%';";
$sql .= "UPDATE product_description SET name = REPLACE(name, '$ExistingWord3', '$ReplacerWord3') WHERE name LIKE '%$SearchWord3%';";

UPDATE So now you have just debug your query and code any preffered way. For example:

 $sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord1', '$ReplacerWord1') WHERE name LIKE '%$SearchWord1%';";
if ($conn->query($sql) === TRUE) {
    echo "1. Products updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}
    $sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord2', '$ReplacerWord2') WHERE name LIKE '%$SearchWord2%';";
if ($conn->query($sql) === TRUE) {
    echo "2. Products updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

    $sql = "UPDATE product_description SET name = REPLACE(name, '$ExistingWord3', '$ReplacerWord3') WHERE name LIKE '%$SearchWord3%';";

if ($conn->query($sql) === TRUE) {
    echo "3. Products updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}