[RESOLVED] Searching with empty string

sonny100

Evening all,

I've got an issue with a search string that's bugging me a bit!

The search form has 5 fields, I want users to be able to search on any combination of these, but for the search string to ignore any fields that are left blank.

At the moment, I think that the search string is searching for all 5 fields, and if any are left blank, it's searching for a blank field in the database, so no results are returned as it can't match all 5 fields.

The query i'm using at the moment is:

"SELECT * from artist WHERE userid = '$userid' AND location = '$location' AND style = '$style' AND wherework = '$studio' AND $q"

Does this make sense?

NogDog

You'll need to build the query in pieces with some conditional logic. One approach:

$sql = "SELECT blah blah blah . . .";
$searches = array();
if(!empty($foo))
{
   $searches[] = "foo='" . mysql_real_escape_string($foo) . "'";
}
if(!empty($bar))
{
   $searches[] = "bar='" . mysql_real_escape_string($bar) . "'";
}
// . . . etc. . . .
if(!empty($searches))
{
   $sql .= " WHERE " . implode(' AND ', $searches);
}

sonny100

Thanks for the reply, got it all working fine now!

Modified it slightly though as it didn't seem to like the

. mysql_real_escape_string($foo) .

part, so just went with the following in the end and it works perfectly...

if(!empty($username))
{
   $searches[] = "userid= '$username'";
}

I'll escape the strings further up when I set the variables.

Thanks again!

johanafm

mysql_real_escape_string requires a conneciton to a db, as it escapes the string based on a specific db's settings.

sonny100

johanafm;10932166 wrote:
mysql_real_escape_string requires a conneciton to a db, as it escapes the string based on a specific db's settings.

Yeah that's fine, i've used this command quite extensively.