Sql query - if statement

Question

Hello!

I try to make an "advanced" search field for users. (the users have 5-8 field to short the search list..)

I think i have to build the query depending on which posted field is not empty.

This is my original query, but with this, i got all the row from table..

$query = "select * from MYTABLE where FIELD1 LIKE '%$sample1%' OR FIELD2 LIKE '%$sample2%' OR FIELD3 LIKE '%$sample3%' OR FIELD4 LIKE '%$sample4%' order by name";

So i think i have to use IF statement in query, but i dont know how, i got error all time. And one more thing: If ths user is fill out the "sample4", then he must to fill "sample1". How can i check this?

Thank you for your help.

Answer 1

Maybe it would be better to create a query that includes only the filter constraints that the user filled out, instead of including all.

If you prefer to follow your solution, you may use the SQL construct

CASE WHEN

Please see this for MS SQL and this for MySQL

Answer 2

You can also use array to do this, it will be more organized this way. For every where condition you store them in one array item, and finally join them when generating the SQL.

<?

$fields = array('field1', 'field2', 'field3');
// Changed this to 0, so it won't return all rows
// but also the default array element prevent the statement to fail when 
// no search is being specified at all
$wheres = array(0);
foreach ($fields as $fieldname) {
  // Get user input
  $search = $_GET[$fieldname];
  // add condition to wheres if not empty
  if (!empty($search)) {
    $wheres[] = $fieldname . ' LIKE "%' . mysql_real_escape_string($search)  . '%"';
  }
}

// Join the wheres conditions using OR, (or AND)
$query = 'select * from MYTABLE where' . join(' OR ', $wheres);

?>

Answer 3

You should consider implementing a "full text search" engine.

MySQL has a "FULLTEXT" search feature - you can start learning about it here: See also this article: Developing A Site Search Engine With PHP And MySQL

Answer 4

Perhaps you will want a php-centric full text search solution - then look at Zend_Search_Lucene which is a PHP port of Apache Lucene Presentations of Zend_Search_Lucene can be found here:

• Roll Your Own Search Engine with Zend_Search_Lucene
• A word on Lucene’s PHP port by Zend

Answer 5

You can try using the "is not null" operator like this:

select * 
from mytable
where 
(field1 is not null and field1 like '%test%') or 
(field2 is not null and field2 like '%test%') or 
(field3 is not null and field3 like '%test%') or
(field4 is not null and field4 like '%test%')
order by name

See also Handling MySQL NULL Values