Complicated database querys

linchpin311 · Feb 1st, 2008, 23:03

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 im 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!

- Linchpin

Jack Franklin · Feb 2nd, 2008, 16:14

Surely though you will want to have the search query as a variable. EG:

PHP: Select all


$searchterm = $_POST['search'];

So you would simply search for the variable, but I have never done searches in MySQL. Some of the more PHP experts should be able to help you

linchpin311 · Feb 2nd, 2008, 16:23

oh absolutely, im not that crazy...

i've done that...but i was hoping i could say something like:

Code: Select all

SELECT * FROM users WHERE (username OR firstname OR lastname) LIKE ('%John%' OR '%Smith%')

just to save some of my sanity. i have tried plugging that in instead of what i have now and it doesnt work properly. you think there is something similar i could say that would work and be not as long and tedious as what i have now?

Rakuli · Feb 3rd, 2008, 01:37

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.....

linchpin311 · Feb 3rd, 2008, 05:23

wow, you must have hacked my desktop...thats pretty much exactly how i had choose to do it, granted it took me about 16 hours to figure out how to make it work this way...

good point about SQL and white space though, i wasn't thinking that way. i can break the query up over different lines and different variables. i kinda feel silly for not thinking about that before.

spinal007 · Feb 3rd, 2008, 10:54

Quote:

Originally Posted by Rakuli

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.....

Not a bad bit of code, but you can do it all in one little line:

PHP: Select all


$where = substr(preg_replace("(\w+)", "OR (username LIKE '%$0%' OR firstname LIKE '%$0%' OR lastname LIKE '%$0%')", $_POST['search']), 3);

$result = mysql_query("SELECT * FROM users WHERE $where");

Use a regular expression to
a) find each word
b) replace it with " OR username LIKE ..."
c) use the substr function to ignore the first "OR "

...and this would work much better as a function you can use in different applications. Write something once and re-use it 'til the cows come home'

Rakuli · Feb 3rd, 2008, 10:57

Good point, didn't think to use a regex.

spinal007 · Feb 3rd, 2008, 11:08

Here's that function (and a few demos):

PHP: Select all


function search_sql($words, $sql){
 $sql = str_replace("WORD", "$0", $sql);
 $words = substr(preg_replace("(\w+)","OR (" . $sql . ")", $words), 3);
    return $words;
};

print "Test 1: " . search_sql('A B C', "username LIKE '%WORD%' OR firstname LIKE '%WORD%' OR lastname LIKE '%WORD%'") . '<br/>';
print "Test 2: " . search_sql('X Y Z', "type='WORD'") . '<br/><br/>';
print "Test 3: " . search_sql('John Doe', "firstname LIKE '%WORD%' OR surname LIKE '%WORD%'") . '<br/><br/>';
print "Test 4: " . search_sql('John Doe', "surname LIKE '%WORD%'") . '<br/><br/>';
print "Test 5: " . search_sql('UserX', "username='WORD' OR (firstname LIKE '%WORD%' OR surname LIKE '%WORD%')") . '<br/><br/>';

Test results

Test 1: (username LIKE '%A%' OR firstname LIKE '%A%' OR lastname LIKE '%A%') OR (username LIKE '%B%' OR firstname LIKE '%B%' OR lastname LIKE '%B%') OR (username LIKE '%C%' OR firstname LIKE '%C%' OR lastname LIKE '%C%')

Test 2: (type='X') OR (type='Y') OR (type='Z')

Test 3: (firstname LIKE '%John%' OR surname LIKE '%John%') OR (firstname LIKE '%Doe%' OR surname LIKE '%Doe%')

Test 4: (surname LIKE '%John%') OR (surname LIKE '%Doe%')

Test 5: (username='UserX' OR (firstname LIKE '%UserX%' OR surname LIKE '%UserX%'))