显示使用搜索查询找到的列

I have a simple piece of code that searches three columns from all the rows in one of my database tables. the table name is "articles" and the 3 columns I am searching are "Title, Description, and Tags". At the moment I search for a word or phrase and it shows me the results, what I wanted to know is if there was a way for me to show which of the 3 columns were found with a result. Do I have to run a separate query for every row that comes back as a result or is there an easier way? I've included a snippet of my code below.

$query = $_GET['q'];
$sql = "SELECT * FROM articles WHERE (`title` LIKE '%".$query."%') OR (`description` LIKE '%".$query."%') OR (`tags` LIKE '%".$query."%') ORDER BY dateadded";
$query = $db->query($sql);
while($row = $query->fetch_assoc()) {
display 
} 

A quick fix would be to select some additional columns using your conditions:

SELECT *,
  IF(`title` LIKE '%".$query."%',       1, 0)  AS conditionTitle,
  IF(`description` LIKE '%".$query."%', 1, 0)  AS conditionDescription,
  IF(`tags` LIKE '%".$query."%',        1, 0)  AS conditionTags
FROM articles 
WHERE (`title` LIKE '%".$query."%')
  OR (`description` LIKE '%".$query."%')
  OR (`tags` LIKE '%".$query."%') 
ORDER BY dateadded";

Now you can check the value of these additional columns in each row to check if the $query was found in that specific field.

Note that you should use prepared statements as right now you possibly have an sql injection problem.

One alternative is to test a condition in an expression the SELECT list.

I think the simplest way to explain it would be by example. But I can't in good conscience post example code that is vulnerable to SQL Injection.

Current query:

 SELECT a.*
   FROM articles
  WHERE a.`title`       LIKE CONCAT('%', ? ,'%')
     OR a.`description` LIKE CONCAT('%', ? ,'%')
     OR a.`tags`        LIKE CONCAT('%', ? ,'%')
  ORDER
     BY a.dateadded

To the current query, we can add expressions to the SELECT list. As an example:

 SELECT IF( a.`title`       LIKE CONCAT('%', ? ,'%') ,1,0) AS match_in_title 
      , IF( a.`description` LIKE CONCAT('%', ? ,'%') ,1,0) AS match_in_description
      , IF( a.`tags`        LIKE CONCAT('%', ? ,'%') ,1,0) AS match_in_tags
      , a.*
   FROM articles
  WHERE a.`title`       LIKE CONCAT('%', ? ,'%')
     OR a.`description` LIKE CONCAT('%', ? ,'%')
     OR a.`tags`        LIKE CONCAT('%', ? ,'%')
  ORDER
     BY a.dateadded

For each row returned, the expression in the SELECT list is evaluated. The conditional test (LIKE comparison) is evaluated to be either TRUE, FALSE or NULL. The MySQL IF function will evaluate the first argument as a boolean, if that's true, it will return the second argument, elise it returns the third argument.

In this example, the expression returns a 1 if there's a match. Otherwise, it returns a 0. There's lots of other choices for values we could return. We could also combine the conditions in a single expression, to return a single value.

And there are a lot of possible expressions that can include a conditional test. I've just used the MySQL IF function. A more ANSI standards compliant expression could use CASE, e.g.

 SELECT CASE WHEN a.`title`       LIKE CONCAT('%', ? ,'%') 
             THEN 1
             ELSE 0
        END AS match_in_title

Something like this should work. Works with a delivered table named search_articles and UNION ALL to combine the SELECT queries together.

SELECT
  search_column
, *
FROM (
  SELECT
   'title' AS search_column
   , *
  FROM
   artices
  WHERE
   title LIKE '%query%'

  UNION ALL 

  SELECT
   'description' AS search_column
   , *
  FROM
   artices
  WHERE
   description LIKE '%query%' 

  UNION ALL 

  SELECT
   'tags' AS search_column
   , *
  FROM
   artices
  WHERE
   tags LIKE '%query%'  
)
 AS
   search_articles