Split sentence into words and check MySQL for occurance

Question

I have a variable that is a sentence for example this is a sentence. I have a MySQL database which has rows where one column contains the following:

"random sentences" 
"few random words" 
"this is cool"
"a car" 
"nice placement"

I need to print out occurrences of any word in the sentence existing in the database rows. For the example sentence given above, the result would be:

"random sentences" 
"this is cool" 
"a car"

This is what I have tried so far:

<?php
$servername = "localhost";
$username = "dsfdsfds";
$password = "sdfdfsdsf";
$dbname = "sdf";

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

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

$galleries = "This is a sentence";
$sql = "
SELECT * 
  FROM rawwords 
 WHERE origin LIKE '%" . $galleries. "%'
";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - location: " . $row["sentence"]. " <br><br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

`$galleries` is a sentence, not a group of words. `explode` on spaces, or `preg_split` on spaces and punctuation, and then build the `where` dynamically for each word. Also parameterize your query. — user3783243, May 18 '18 at 19:08
Just for clarity, based on your examples, when $galleries="This is a sentance", what would you want the expected output to be? — Stephen, May 18 '18 at 19:18

score 0 · Answer 1 · answered May 18 '18 at 19:19

Create the right sql :D

$galleries ="This is a sentence";
$arr = explode(" ", $galleries);

$sql = "SELECT * FROM rawwords WHERE origin LIKE";

$lenght = count($arr);

for ($i=0; $i<$lenght; $i++) {
    if ($i == 0) {
        $sql .=" '% " . $arr[$i]. " %'";
    } else {
        $sql .=" or '% " . $arr[$i]. " %'";
    }
}

score 0 · Answer 2 · answered May 18 '18 at 19:22

You could use REGEXP to fin words in your sentences using [[:<:]]word[[:>:]] (word boundaries). You could split your string to get words, then use array_map() to transform them for the query. Finally, you could use implode() with " or " glue:

$galleries = "This is a sentence" ;
$words = preg_split('~\W~', $galleries, -1, PREG_SPLIT_NO_EMPTY);

$words = array_map(function($word) { 
    return ' origin REGEXP "[[:<:]]'.$word.'[[:>:]]" '; 
}, $words);
$where = implode(' OR ', $words);

$sql = "SELECT * FROM rawwords WHERE $where";

Will generate the query:

SELECT * FROM rawwords 
WHERE origin REGEXP "[[:<:]]This[[:>:]]" 
   OR origin REGEXP "[[:<:]]is[[:>:]]" 
   OR origin REGEXP "[[:<:]]a[[:>:]]" 
   OR origin REGEXP "[[:<:]]sentence[[:>:]]"

Important, you should have an attentive look to parameterized queries.
Please read this article with several examples: How can I prevent SQL injection in PHP?

Split sentence into words and check MySQL for occurance

2 Answers2