How do you build a query from a number of search fields

[86Stang]

I've got a keyword, category, start_date and end_date fields that I want the user to pick from but I'm having a hell of a time putting it into a 'master query' based on what they've entered.  I'm trying something like this:

 

if (isset($keyword_search))
{
	$keyword_qry = "WHERE title LIKE '%$keyword_search%' OR info LIKE '%$keyword_search%'";
}
else
{
	$keyword_qry = "";
}

if (isset($category_search))
{
	$category_qry = "WHERE category LIKE '%$category_search%'";
}
else
{
	$category_qry = "";
}

$qry = "SELECT * FROM table " . $keyword_qry . $category_qry;
$result = mysql_query($qry) or die("Doh!");

 

but I know this isn't right because I have multiple WHEREs.  Any help would be greatly appreciated!

[MRushton]

Perhaps something like:

 

  $where = array();

  foreach ($_POST['search'] as $key => $value) {

    if (!ctype_alpha($key)) {
      continue();
    }

    $where[] = $key . " LIKE '%" . mysql_real_escape_string($value) . "%'";

  }

  $sql = 'SELECT * FROM table WHERE ' . implode(' OR ', $where);

 

With the HTML being something like:

 

...
<input type="text" name="search['keyword']" />
<input type="text" name="search['category']" />
...

[jl5501]

there are many ways of going about this

 

1 is to define the whereclause initially as 'where ' and then each time you want to add something you say if $whereclause == 'where ' $whereclause .= yournewcondition

else $whereclause .= ' and '. yournew condition

 

another way is to define the initial where clause as ' where 1 = 1'

and then for each new condition

$whereclause .= ' and '. yournewcondition

[Psycho]

$where = '';

if (isset($keyword_search) || isset($category_search))
{
    $where_clause = array();

    if (isset($keyword_search))
    {
        $where_clause[] = "(title LIKE '%$keyword_search%' OR info LIKE '%$keyword_search%')";
    }

    if (isset($category_search))
    {
        $where_clause[] = "category LIKE '%$category_search%'";
    }

    $where = implode(' AND ', $where_clause);
}

$qry = "SELECT * FROM table {$where}";
$result = mysql_query($qry) or die("Doh!");

 

NOTE: contained the keyword search in parens. Otherwise teh ANDs and ORs may not always react as you would want.

[86Stang]

Thanks so much for the help!  It's throwing this error though:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title LIKE '%test%' OR info LIKE '%test%') AND category LIKE '%%'' at line 1

 

Any thoughts?

[Psycho]

Thanks so much for the help!  It's throwing this error though:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title LIKE '%test%' OR info LIKE '%test%') AND category LIKE '%%'' at line 1

 

Any thoughts?

 

Looks like $category_search is set as an empty string. I wrote my code using the same logic you had. You should add a secondary check to ensure the value is not an empty string. Personally, I prefer to echo the query to the page when it fails (in the development phase) so I can ensure it is as I expect.

 

$where = '';
if (isset($keyword_search) || isset($category_search)){
    $where_clause = array();
    if (isset($keyword_search) && !empty($keyword_search))
    {
        $where_clause[] = "(title LIKE '%{$keyword_search}%' OR info LIKE '%{$keyword_search}%')";
    }
    if (isset($category_search) && !empty($category_search))
    {
        $where_clause[] = "category LIKE '%{$category_search%}'";
    }
    $where = implode(' AND ', $where_clause);
}

$qry = "SELECT * FROM table {$where}";
$result = mysql_query($qry) or die("Doh!");

[86Stang]

That works for a blank search (nothing in either field but when I put something in one of the fields, such as 'test' in the category field, I get the same syntax error:

category LIKE '%test%'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'LIKE '%test%'' at line 1

 

Could this be because it's not actually inserting the word WHERE in there anywhere?

[86Stang]

I think that was it.  This seems to be working as I want it to:

 

$where = '';

if (isset($keyword_search) || isset($category_search))
{
    $where_clause = array();

    if (isset($keyword_search) && !empty($keyword_search))
    {
        $where_clause[] = "(title LIKE '%$keyword_search%' OR info LIKE '%$keyword_search%')";
    }

    if (isset($category_search) && !empty($category_search))
    {
        $where_clause[] = "event_type LIKE '%$category_search%'";
    }

    $where = implode(' AND ', $where_clause);
}

if ($where <> '') {
$where_param = "WHERE ";
}
$qry = "SELECT * FROM test_events {$where_param} {$where}";
$result = mysql_query($qry) or die("Doh!");

 

If there's a better way to do that, feel free to enlighten me!!

[Psycho]

Oh, I accidentally removed the actual WHERE text that I was defining. It would make sense to move that inside the first IF statement instead of doing another comparison:

 

$where = '';
if (isset($keyword_search) || isset($category_search)){
    $where_clause = array();
    if (isset($keyword_search) && !empty($keyword_search))
    {
        $where_clause[] = "(title LIKE '%{$keyword_search}%' OR info LIKE '%{$keyword_search}%')";
    }
    if (isset($category_search) && !empty($category_search))
    {
        $where_clause[] = "category LIKE '%{$category_search%}'";
    }
    $where = "WHERE " . implode(' AND ', $where_clause);
}

$qry = "SELECT * FROM table {$where}";
$result = mysql_query($qry) or die("Doh!");

[86Stang]

That's originally where I put it but it keeps throwing a syntax error when all fields are blank so I moved it outside the if.

[roopurt18]

I realize you may be past this point in your search for help, but I usually build queries like so:

 

<?php
    
/**
* Demonstrates query building with all fields required
* 
* @param string $last_login
* @param string $user_group
*/
function find_user_by_login_and_group( $last_login, $user_group ) {
    // validation
    $last_login = strtotime( $last_login );
    if( in_array( $last_login, array( -1, false ), true ) )
        throw new Exception( '$last_login is invalid' );
    if( ! preg_match( '/[a-z][a-z0-9_]+/i', $user_group ) )
        throw new Exception( '$user_group is invalid' );
        
    $last_login = date( 'Y-m-d H:i:s', $last_login );
    
    // escaping
    $last_login = mysql_real_escape_string( $last_login );
    $user_group = mysql_real_escape_string( $user_group );
    
    $select_stmt = "
        SELECT * FROM `users` WHERE `group`={$user_group} AND `last_login`>={$last_login}
    ";
    
    // Now run the query...
}
    
/**
* Demonstrates query building with optional fields
* 
* @param string $fname
* @param string $lname
* @param int $age
*/
function find_user( $fname = null, $lname = null, $age = null ) {
    $wheres = array();
    if( is_string( $fname ) ) {
        if( strpos( $fname, '%' ) !== false ) $join = ' LIKE ';
        else $join = ' = ';
        $wheres[] = "`fname` {$join} " . mysql_real_escape_string( $fname );
    }
    if( is_string( $lname ) ) {
        if( strpos( $lname, '%' ) !== false ) $join = ' LIKE ';
        else $join = ' = ';
        $wheres[] = "`lname` {$join} " . mysql_real_escape_string( $lname );
    }
    if( is_int( $age ) ) {
        $wheres[] = "`age`={$age}";
    }
    $wheres = count( $wheres ) > 0 ? " WHERE " . implode( ' AND ', $wheres ) : " ";
    
    $select_stmt = "
        SELECT * FROM `users` {$wheres}
    ";

    // Now run the query...
}
?>