How can PHP developers efficiently count the occurrences of a specific word in multiple columns using the LIKE operator in MySQL queries?

To efficiently count the occurrences of a specific word in multiple columns using the LIKE operator in MySQL queries, PHP developers can construct a SQL query that uses the SUM function along with the LIKE operator for each column. By summing the results of the LIKE comparisons for each column, developers can accurately count the total occurrences of the specific word across multiple columns. 

<?php

// Establish a connection to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

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

// Specify the specific word to count occurrences of
$specificWord = "example";

// Construct the SQL query to count occurrences of the specific word in multiple columns
$sql = "SELECT 
            SUM(column1 LIKE '%$specificWord%') + 
            SUM(column2 LIKE '%$specificWord%') + 
            SUM(column3 LIKE '%$specificWord%') AS total_occurrences
        FROM table_name";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output the total occurrences of the specific word
    $row = $result->fetch_assoc();
    echo "Total occurrences of '$specificWord': " . $row['total_occurrences'];
} else {
    echo "No results found.";
}

// Close the database connection
$conn->close();

?>

Keywords

PHP MySQL LIKE operator count occurrences multiple columns

Related Questions