Find & Replace in SQL using php

by Faisal Khan Janjua

JavaScript

<?php

// Database connection details
$servername = "localhost";
$username = "root";
$password = "123";
$database = "staging_pestdefence";

// Array of table names to perform find and replace
$tables = array(
    '_wp_rg_form_view',
    '_wp_rg_lead',
    '_wp_rg_lead_detail',
    '_wp_rg_lead_detail_long',
    'active_employees'
);

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// String to find and replace
$find = "http://staging.pestdefense.com";
$replace = "http://localhost/pestdefense";

// Loop through each table
// Assuming $conn is a valid database connection

// Loop through each table
foreach ($tables as $table) {
    // Get the list of columns for the current table
    $columns = array();
    $result = $conn->query("SHOW COLUMNS FROM $table");

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $columns[] = $row['Field'];
        }

        // Construct and execute the update query for each column
        foreach ($columns as $column) {
            // Check if the string to be replaced exists in the column
            $checkSql = "SELECT COUNT(*) AS count FROM $table WHERE `$column` LIKE '%$find%'";
            $checkResult = $conn->query($checkSql);

            if ($checkResult) {
                $countRow = $checkResult->fetch_assoc();
                $rowCount = $countRow['count'];

                if ($rowCount > 0) {
                    // Perform the replace if the string exists in the column
                    $sql = "UPDATE $table SET `$column` = REPLACE(`$column`, '$find', '$replace')";
                    // echo '<br>';
                    // echo $sql;
                    if ($conn->query($sql) === TRUE) {
                        echo '<br>';
                        echo "------------------Replaced values in $table.$column successfully<br>";
                    }...