To do the search that you want you had it right in the first instance.
SQL is a simple language and doesn't really have many ways of shortening when you are having to look in multiple columns for differing values.
SQL ignores whitespace so if it is a matter of your sanity, break it up over numerous lines for easier reading. At the end of the day though, you really want to have it all in one query. Even if it is a very very long query, it is much faster than
php.
If you're using
PHP, here is how I would go about constructing the query so you don't have to worry about how long the search term is.
- PHP: Select all
$search = $_POST['search']; // Fill the variable with search
// This is the first part of the where clause searching for the whole string
$where = " username LIKE %{$search}% OR firstname LIKE %{$search}% OR lastname LIKE %{$search}% ";
// Now we'll split that search term into an array by breaking it to pieces at spaces
$search = explode(' ', $search);
$nm = $search; // Count how many parts there are to the string
// If there was only one part then we won't get any more detailed than that
if ($nm > 1)
{
// So there's more than one part so we'll add the individual searches to the where clause
foreach ($search as $s)
$where .= " username LIKE %{$s}% OR firstname LIKE %{$}% OR lastname LIKE %{$s}% ";
}
// Now the whole query has been constructed so it's just a matter of getting the results now
$result = mysql_query("SELECT * FROM users WHERE $where");
// etc.....