Perform an exact match SQL database query with PHP against a list of values in a database column-CodePudding

I'm working with a SQL Database

My database has a column with the "Term" and a column with the "Content"

Right now when I get a user input for the "Term", I query the database using that term to get the term's content.

Is it possible to have a comma separated list of terms in the "Term" column and if the user's term is in the list of terms, then it returns that term's content?

Here is my SQL Database as a PHP array:

$database = array(

0 => array("term" => "example", "content" => "example content"),
1 => array("term" => "example, examples, many examples", "content" => "example content"),

);

Right now I can only return the row with index 0. I want to be able to return row 1 if the user's term is an exact match of one of the items in the list, in this case "example", "examples", or "many examples".

<?php

        $sql_query = $connection->prepare("SELECT *
        FROM database WHERE term LIKE :term
        ORDER BY priority DESC
        LIMIT 10");
        $term = "example";
        $sql_query->bindParam(":term", $term );
        $sql_query->execute();


        $result = $sql_query->fetchAll(); // Gets all results and turns them into an array


?>

Is it possible for me to perform a database query that compares the user's "Term" to the comma separated list of "Terms"? Keep in mind they have to be exact matches.

CodePudding user response：

use this

WHERE FIND_IN_SET(:term, term)

CodePudding user response：

Use regular expression(regexp) for word match and BINARY for case sensitivity.

SELECT *
    FROM database 
    WHERE term REGEXP BINARY '[[:<:]]:term[[:>:]]'
    ORDER BY priority DESC
    LIMIT 10

Regexp is kinda the same as preg_match in php, it looks for match by pattern.

[[:<:]], [[:>:]] These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).