0

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.

Improve this question
1
  • You only want it to match all words? not some? Commented Jan 14, 2023 at 13:31

1 Answer 1

Reset to default
-1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

What happens if the text is something like hello, how would you handle this?
Referring to the column named text in the table.
Perhaps a trick question? I'm hoping you may enlighten me if I am missing something
I'm referring to something like like'%and%' will find and within a word such as handle - it's not quite as straightforward unless you also match on word boundaries and cater for start/end of strings; punctuation too.
That's a good point, I guess you could use REGEXP to match whole words, but that's slow as.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.