在where子句中使用数组，其中check字段包含逗号分隔的单词

Question

Please, read the question carefully before you mark it as duplicate. It is not.

I have a table named questions, and I have field questionMetaTags in that table which accepts coma separated values (keywords)

Here is my table:

`questionID` int(11) NOT NULL AUTO_INCREMENT,
`questioncategoryID` int(11) NOT NULL,
`questionstatusID` int(11) NOT NULL,
`organizationID` int(11) NOT NULL,
`legalformID` int(11) DEFAULT NULL,
`questionProtocolID` varchar(45) DEFAULT NULL,
`questionDisplayedRecordID` int(11) NOT NULL,
`questionTitle` text NOT NULL,
`questionSummary` text,
`questionText` longtext NOT NULL,
`questionAnswerSummary` text,
`questionAnswerText` longtext,
`questionMetaTags` text,
`questionAskedBy` int(11) NOT NULL,
`questionAnsweredBy` int(11) DEFAULT NULL,
`questionAskedOnDate` datetime NOT NULL,
`questionAnsweredOnDate` datetime DEFAULT NULL,
`questionAskedFromIp` varchar(255) NOT NULL

I am trying to create a query which will "produce" related questions. I am displaying one specific question on my page, and I want bellow to display only the questions that: 1. Belong to same category AND 2. Have at least 2 or more same keywords

In those 2 variables I am holding the category and the keywords of the displayed question, and I would now like to build the query which will display "related" questions.

$keywordsInQurrentQuestion (array that holds the keywords)
$questioncategoryID (category of the question)

I am trying with something like:

SELECT *
   FROM question WHERE `questionstatusID` = 5
   AND questioncategoryID = $questioncategoryID
   // I have no idea how to check if the question have at least 2 keywords that exists in the $keywordsInQurrentQuestion array
   ");

I have found answers on how to use array in where clause since I want to check for at least 2 same keywords, those answers are not helpful to me.

Answer 1

Your database schema is not really made for that kind of queries. MySQL is a relational database after all.

Solution in MySQL (change of DB schema required)

It would be better to have an extra table for just keywords (or meta tags):

ID      tag
  1     oranges
  2     apples
...     ...
154     motor oil

A second table would then hold the relations between questions and tags:

questionID tagID
  1          1
  1          2
  1         37
  2          3
...        ...
 18        102
 19        154

Now, when querying for a similar/related question, you can do so by checking your minimum of identical tags. First, a subquery is used to get all tags from your displayed question:

SELECT tagID FROM relations WHERE questionID = 123;

This will then be used in the actual query that retrieves the similar questions:

SELECT DISTINCT(r1.questionID)
FROM relation r1
INNER JOIN relation r2
  ON r1.questionID = r2.questionID AND NOT r1.tagID = r2.tagID
WHERE r1.tagID IN (SELECT tagID FROM relations WHERE questionID = 123)
  AND r2.tagID IN (SELECT tagID FROM relations WHERE questionID = 123)

This query joins the relation table with itself (selfjoin), looking for rows with an identical question ID but different tag IDs. In the WHERE clause it checks if both joined tags are within the set of the original question. Finally, it will remove all duplicates (DISTINCT) and give you a list of related question IDs.

This query still has to be expanded to check for the category, this has been left out to make the statement clear.

Solution in PHP

According to your comment, you do not want to change the DB schema. Although retrieving all questions from the database first and then get the related ones in PHP might not be the best choice in terms of execution time and performance, it is still possible.

Assuming you have all terms of your original question in some value

$meta_tags = "oranges,apples,avocados,olive oil";

and all your questions from the same category in an array containing at least the question ID and the respective keywords

$questions = array(
  0 => array(
    "questionID" => 1,
    "metaTags" => "apples,olive oil",
  ),
  1 => array(
    "questionID" => 2,
    "metaTags" => "oranges,motor oil",
  ),
  2 => array(
    "questionID" => 3,
    "metaTags" => "oranges,avocados",
  ),
  ...
);

you can loop through your questions, check if at least two keywords are identical and then store the question IDs in some array:

function check_tags($needles, $haystack) {
  $original_tags = explode(',', $haystack);
  $original_tags = array_map('trim', $original_tags);
  $tags_to_check = explode(',', $needles);
  $tags_to_check = array_map('trim', $tags_to_check);
  $count = 0;
  foreach ($tags_to_check as $t) {
    if (in_array($t, $original_tags)) {
      count++;
    }
  }
  return $count;
}
$related = array();
foreach ($questions as $q) {
  if (check_tags($q['metaTags'], $meta_tags) >= 2) {
    $related[] = $q['questionID'];
  }
}

The array $related now contains all question IDs which are similar to your original question.