Display which column was found with search query

Question

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 
}

The presence of a column called tags is often symptomatic of poor design. — Strawberry, Mar 07 '17 at 14:59
yes I know the column names are not perfect. I renamed them to make things simple so it was easier to ask my question. — Born2DoubleUp, Mar 07 '17 at 15:05

jeroen · Accepted Answer · 2017-03-07T15:09:30.063

1

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.

edited Mar 07 '17 at 15:09

answered Mar 07 '17 at 15:02

jeroen

91,079
21
114
132

I'll have to research your code a little more as I'm not to sure what it's doing. lol I'm still green when it comes to php and mysql, trying to teach myself all this stuff one day at a time, but that's for the suggestion I'll definitely look into it! – Born2DoubleUp Mar 07 '17 at 15:07
@Born2DoubleUp You will have 3 additional columns in every row that you find and these columns will have values of `yep` or `nope` (1 and 0 would of course be move convenient...). Just run it in for example phpMyAdmin to see the results. – jeroen Mar 07 '17 at 15:08
@Born2DoubleUp I've switched to integer output to make checking easier. – jeroen Mar 07 '17 at 15:09

spencer7593 · Answer 2 · 2017-03-07T15:57:06.123

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

score 0 · Answer 3 · answered Mar 07 '17 at 15:10

0

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

answered Mar 07 '17 at 15:10

Raymond Nijland

11,488
2
22
34

Note that this query has the potential to return the same row from `articles` multiple times. For example, if the row "matches" on both `tags` and `title`. – spencer7593 Mar 07 '17 at 15:19
@spencer7593 true then the topic starter should remove the keyword ALL after all UNIONS to remove duplicates – Raymond Nijland Mar 07 '17 at 15:22
Using `UNION` in place of `UNION ALL` doesn't really address that, because the value returned for `search_column` will be distinct between the queries, `'title'` vs `'tags'`. The unique sort operation will leave both rows. – spencer7593 Mar 07 '17 at 15:37

Display which column was found with search query

3 Answers3