Question 21262 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I select where a string contains all items in an array in PHP MySQL prepared statement?

Say I have the array:
$myArray = ["hello", "and"];

and then I have a table like:

id text
1 "hello larry!"
2 "hello bob, and how are you?"
3 "and what are you up to?"

How can I run a query like

$stmt = $conn->prepare("SELECT text FROM myTable WHERE text CONTAINS allItemsIn(a) LIMIT 10");
$stmt->bind_param("a", $myArray);
$stmt->execute();

Ideally, I’d like the query to only return the row with id 2, as the text contains both the strings "hello" and "and". I don’t want the order of the items in the array to matter, so even if the array had "hello" and "and" the other way around, it should still return row 2. How can I do this?

I thought about making a for loop, and for each item in $myArray, run a query where the text contains the item in the given index of $myArray, however, I realize that this could become quite slow, especially if $myArray contained a large amount of items.

Some posts asking for a similar thing’s answers use WHERE x IN. I cannot use this, as text will never be in any item in $myArray, as text could be longer than some of the items in $myArray.

Tags:

2

Answers


  1. 0

    You can use implode to convert your array to string. as follows :

    $myArray = ["hello", "and"];
$myText = '%'.implode($myArray, '%').'%';

$stmt = $conn->prepare("SELECT text FROM myTable WHERE text like :a LIMIT 10");
$stmt->bind_param("a", $myText);
$stmt->execute();
    Login or Signup to reply.
  2. 0

    You can achieve this with a LIKE for each word in your search string and joining them with an AND.

    $query = "SELECT text FROM myTable WHERE ";
foreach($myArray as $word) {
    $query .= "text LIKE '%$word%' AND ";
}
$query = rtrim($query, " AND ");
$query .= " LIMIT 10";

$stmt = $conn->prepare($query);
$stmt->execute();

    However if you would like to match by any word in the search string, and order them depending on the amount of matches:

    $query = "SELECT text, COUNT(*) as matches FROM myTable WHERE ";
foreach($myArray as $word) {
    $query .= "text LIKE '%$word%' OR ";
}
$query = rtrim($query, " OR ");
$query .= " GROUP BY text ORDER BY matches DESC LIMIT 10";

$stmt = $conn->prepare($query);
$stmt->execute();

    For a search term like: "hello, how would you handle this?" the query generated would look like:

    SELECT
    text,
    COUNT(*) as matches FROM myTable WHERE 
FROM
    myTable
WHERE
    text LIKE '%hello%'
    OR text LIKE '%how%'
    OR text LIKE '%would%'
    OR text LIKE '%you%'
    OR text LIKE '%handle%'
    OR text LIKE '%this%'
GROUP BY
    text
ORDER BY
    matches DESC
LIMIT 10

    You should strip out non alpha-numeric characters like punctuation, most search engines also strip out common words like "the" "is" "and" "but" (referred to as "stop words" for some reason) internally as their use is very frequent and often results in returning less relevant items, it also makes the query faster.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search