CODEWITHSUNDEEP
phpmysql
SK0004
SK0004

Reputation: 71

Searching with MySQL database and PHP

I have a search page in PHP which i can enter a string to search. Also I have written a page which displays the results which matches with the string in the database. SQL query is 

$query = "select * from table_name WHERE Name LIKE '$srch' || Last LIKE '$srch' || email LIKE '$srch' || comment LIKE '$srch'";

If the search string is "John David", I would like to show every record that contains either "John" or "David" rather than only showing results that have both "John David", since I have records like John Samuel,David Grecco, Jennifer Davidson etc

Upvotes: 0

Views: 138

Answers (3)

William
William

Reputation: 3529

I haven't used it personally, but you might have luck with mySQL's REGEXP operator.

From mySQL's REGEXP:

Regular Expression Operators expr NOT REGEXP pat, expr NOT RLIKE pat

This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression, the syntax for which is discussed later in this section. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

Note Because MySQL uses the C escape syntax in strings (for example, “\n” to represent the newline character), you must double any “\” that you use in your REGEXP strings.

REGEXP is not case sensitive, except when used with binary strings.

mysql> SELECT 'Monty!' REGEXP '.'; -> 1 mysql> SELECT 'new\n*line' REGEXP 'new\*.\*line'; -> 1 mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A'; -> 1 0 mysql> SELECT 'a' REGEXP '^[a-d]'; -> 1 REGEXP and RLIKE use the character set and collations of the arguments when deciding the type of a character and performing the comparison.

Upvotes: 0

Micah Carrick
Micah Carrick

Reputation: 10197

You might want to do a MySQL FULLTEXT search in boolean mode. See http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

Edit: To elaborate, you query would become:

$query = "SELECT * FROM table_name WHERE 
          MATCH (Name,Last,email,comment) 
          AGAINST('$srch' IN BOOLEAN MODE)";

If that does work for you then you will want to add a FULLTEXT index on the columns you are searching.

Upvotes: 2

Headshota
Headshota

Reputation: 21449

you can explode your string into words and do search on each of them individually.

$where_clause = "";
foreach($words as $key=>$word){
  $or = "";
  if (isset($words[$key+1])){
   $or = " OR";
  }
  $where_clause = "Name LIKE '$srch' || Last LIKE '$srch' || email LIKE '$srch' || comment LIKE '$srch' {$or}";
}

I think this would be correct

Upvotes: 0

Related Questions