How can you use CASE Statements to assign values to matches in your query?

Question

Let's say your running a query and you have a lot of different user inputs that may not find an exact match. Now would it be possible to do something like the following?

$query = "SELECT *,
              CASE matchingValues
                  WHEN $field1 LIKE '$a' THEN value = '1' ELSE value = '0'
                  WHEN $field2 LIKE '$b' THEN value = '1' ELSE value = '0'
                  WHEN $field3 LIKE '$c' THEN value = '1' ELSE value = '0'
                  WHEN $field4 LIKE '$d' THEN value = '1' ELSE value = '0'
                  WHEN $field5 LIKE '$e' THEN value = '1' ELSE value = '0'
              END AS score
              FROM . $usertable
              WHERE
                  $field1 LIKE '$a' AND
                  $field2 LIKE '$b' AND
                  $field3 LIKE '$c' AND
                  $field4 LIKE '$d' AND
                  $field5 LIKE '$d'
              ORDER BY score DESC";

if($result = mysql_query($query)) {

    if(mysql_num-rows($result)==NULL) {
    echo 'No Results Found';

    }else{

    ....

Answer 1

Yes this is possible, though you would do it without the WHERE clause, and you would want to add up all the inputs instead of using a CASE to get the total score. Since each LIKE statement returns a boolean 1 or 0, you can just add them up to find out how many matches you have. A HAVING clause then limits to rows returned with a score > 0, if you want to return only those that actually matched.

  SELECT *,
      (($field1 LIKE '$a') +
      ($field2 LIKE '$b') +
      ($field3 LIKE '$c') +
      ($field4 LIKE '$d') +
      ($field5 LIKE '$e')) AS score
  FROM . $usertable
  HAVING score > 0
  ORDER BY score DESC";

We assume you have already added % wildcards to the LIKE variables, and that they have been properly escaped prior to use in your query.

However, while you can do it this way (a poor-man's full-text search), MySQL offers the possibility of creating a full text index across multiple columns on MyISAM tables.

Update: To artificially weight them...

If you have a need to weight them, use CASE statements and put in higher numbers for the top few:

  SELECT *,
      (CASE WHEN ($field1 LIKE '$a') THEN 5 ELSE 0 END +
      CASE WHEN ($field2 LIKE '$b') THEN 4 ELSE 0 END +
      CASE WHEN ($field3 LIKE '$c') THEN 3 ELSE 0 END +
      CASE WHEN ($field4 LIKE '$d') THEN 2 ELSE 0 END +
      CASE WHEN ($field5 LIKE '$e') THEN 1 ELSE 0 END) AS score
  FROM . $usertable
  HAVING score > 0
  ORDER BY score DESC";

To just enforce that the first one match, put it in your WHERE clause.

  SELECT *,
      (($field1 LIKE '$a') +
      ($field2 LIKE '$b') +
      ($field3 LIKE '$c') +
      ($field4 LIKE '$d') +
      ($field5 LIKE '$e')) AS score
  FROM . $usertable
  WHERE $field1 LIKE '$a'
  HAVING score > 0
  ORDER BY score DESC";

Again, to be clear - a very closely related functionality is already built-in and optimized in MySQL's full text indexing and the MATCH keyword.