[eruckus]

Greetings!

I am trying to query my database through php but its starting to get real complicated. This is the setup: a user visits page1.php to search for a name in the database. I want the user to be able to search for this name by user name, first name or last name. Say they type in "John Smith" into the form...

The script then querys the database: SELECT * FROM users WHERE ((username LIKE '%John%') OR (firstname LIKE '%John%') OR (lastname LIKE '%John%')) OR ((username LIKE '%Smith%') OR (firstname LIKE '%Smith%') or (lastname LIKE '%Smith%'))

So I'm wondering is there an easier way to query the database and get the same results? I am planning to use another search for business names where users might type in more than two words and I would hate for the query to get any more complicated than it already is.

Many thanks in advance!
- e.ruckus

[MommyLeah]

I'm guessing that your search form has separate fields for username, name, business name, etc. You don't have to write a query that checks every field that they can search, just the ones that they entered. You can use PHP to build the query as a string. The PHP will be longer, but your SQL will be as short as possible. It might look something like this if you are getting your search variables from the POST data:

// the base of the query
// (1=1 just makes it easy to append more sql and know you are safe to use AND)
$sql = 'SELECT * FROM users WHERE 1=1 ';

// build sql if the username is found (repeat this for every field)
if( $_POST['username'] != '' ) {
$username = addslashes($_POST['username']);
$sql .= " AND username = '$username' ";
}

// execute the query.