Multiple LIKEs in Search Function

[jamesjmann]

How do you write multiple "LIKE" parameters for searching?

For example:

mysql_query(SELECT * FROM members WHERE username LIKE $username and name LIKE name);

or

mysql_query(SELECT * FROM members WHERE username LIKE $username, name LIKE name);

 

How is this done? I'm scripting a search function in php, using a foreach loop to define a variable, which will be placed inside of a mysql_query function.

 

Here's what the foreach loop looks like

$query = "SELECT * FROM members WHERE";

foreach ($_GET as $field => $value) {

$query .= " $field LIKE $value";

}

 

mysql _query($query);

 

Hope this helps you guys to understand better.

[QuickOldCar]

Here is an example of one of my search queries using boolean mode which incorporates single or multiple values using spaces or the +,- for include,exclude

$result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" );

 

Some LIKE examples

exact match in field1
      SELECT * FROM table WHERE field1 = '$name'

field1 contains the search word anywhere using LIKE and both '%%'
      SELECT * FROM table WHERE field1 LIKE '%$word%'

field1 contains the value that begins with our word using LIKE and the last ' %'
      SELECT * FROM table WHERE field1 LIKE '$word%'

field1 contains the value that ends with our word using LIKE and the first '% '
      SELECT * FROM table WHERE field1 LIKE '%$word'

word appears in both fields using AND
      SELECT * FROM table WHERE field1 LIKE '%$word%' AND field2 LIKE '%$word%'

word appears in any of the two fields using OR
      SELECT * FROM table WHERE field1 LIKE '%$word%' OR field2 LIKE '%$word%'

word is in field1 OR in field2 AND field3 using Parentheses
      SELECT * FROM table WHERE field1 LIKE '%$word%' OR (field2 LIKE '%$word%' AND field3 LIKE '%$word%') ORDER BY id DESC

[jamesjmann]

Here is an example of one of my search queries using boolean mode which incorporates single or multiple values using spaces or the +,- for include,exclude

$result = mysql_query("SELECT * FROM posts $post_status AND MATCH (post_title,post_content) AGAINST ('$search_words' IN BOOLEAN MODE) ORDER BY $display $order LIMIT $startrow,$posts_per_page" );

 

Some LIKE examples

exact match in field1
      SELECT * FROM table WHERE field1 = '$name'

field1 contains the search word anywhere using LIKE and both '%%'
      SELECT * FROM table WHERE field1 LIKE '%$word%'

field1 contains the value that begins with our word using LIKE and the last ' %'
      SELECT * FROM table WHERE field1 LIKE '$word%'

field1 contains the value that ends with our word using LIKE and the first '% '
      SELECT * FROM table WHERE field1 LIKE '%$word'

word appears in both fields using AND
      SELECT * FROM table WHERE field1 LIKE '%$word%' AND field2 LIKE '%$word%'

word appears in any of the two fields using OR
      SELECT * FROM table WHERE field1 LIKE '%$word%' OR field2 LIKE '%$word%'

word is in field1 OR in field2 AND field3 using Parentheses
      SELECT * FROM table WHERE field1 LIKE '%$word%' OR (field2 LIKE '%$word%' AND field3 LIKE '%$word%') ORDER BY id DESC

 

Thanks, but that's not what I'm trying to do. On my search form, there's about 8 fields, and I want to write a statement that basically checks which fields contain data. Then, write a query using that foreach loop to display the $_POST array values into the query string. Do you know what I mean? That way, a member has unlimited amounts of searching options.

For example,

name LIKE name and gender LIKE gender and location LIKE location

All with percentage signs (wildcard indicators)

A query like that would search for a member who has a name LIKE the name they type - IN ADDITION TO someone whose, say male, who ALSO lives in LA.

 

[fenway]

Then you need to build the WHERE clause dynamically.

[jamesjmann]

Then you need to build the WHERE clause dynamically.

 

Oh i figured it out. I had a problem for the longest time where empty fields would get passed to query string, breaking the script; however, a solution quickly occured to me that made sense and worked:

 

Writing if statements on each post element to check if they are empty - and if they are NOT, assign them to a new array and then call that array in the foreach statement. It works PERFECTLY now. I appreciate the help everyone!