Anyone know how to do multi word search?

[man5]

I have a setup where I can do single word search and it returns the data.  However, I am looking to add a multi word functionality.  So far no luck.

 

Anyone here know how to do it and point me in the right direction?

 

 

[.josh]

explain where and how the data is stored and what you are currently using to interact with the data. 

[man5]

Sure.

 

I have the data stored in MySQL database.  To simplify, say I want to retrieve search results of records from the database. To find those records, I will be searching by their 'title'.

 

Here is the basic setup that works with only 1 term in the search. 

<form action="" method="GET">
   <input type="search" name="search" placeholder="Enter search terms here" size="80px" >
   <input type="submit" name="submit" value="Search it" >
</form>

<?php
if (isset($_GET['submit']) && !empty($_GET['search'])) {

   $value    =     $_GET['search'];
   $results   = DB::getInstance->query("SELECT * FROM records WHERE title LIKE '%$value%' ");

   if($results->count() > 0) {
      foreach($results->results() as $row) {
         $title = $row->title;
      
         echo $title;
      }
   } else {
      echo 'No results found!';
   }
}

[.josh]

You can use

 

You can have multiple WHERE..LIKE clauses, e.g.:

 

SELECT * FROM records WHERE title LIKE '%value1%' OR title LIKE '%value2%' OR title LIKE '%value3%'

or you can use regex:

 

SELECT * FROM records WHERE title REGEXP 'value1|value2|value3'

[man5]

Not sure how using multiple LIKE clauses will help in my case; unless I input the keywords for each value manually as oppose to using the search form.

 

REGEXP is something new that I never heard of.  I tried it like this and it doesn't seem to work.

("SELECT * FROM records WHERE title REGEXP '$value' ")

There are so many tutorials out there that do multi word function but they are using old MySql, which is depreciated as you know.

 

For example, below is the search code.  I am having bit of problem trying to convert and fit into my PDO code.

$value	= 	$_GET['search'];
$terms	= 	explode(" ", $value)
$query     =  ("SELECT * FROM records WHERE");

foreach($terms as $each) {
	$i++;
	if ($i == 1) { 
		$query .= "title LIKE '%$each%' ";
		else {
			$query .= "OR title LIKE '%$each%' ";
		}
	}
}

$query = mysql_query($query);
$numrows = mysql_num_rows($query);

if ($numrows > 0) {
	
	while($row = mysql_fetch_assoc($query)) {
		$title = $row['title'];
	}
} else {
	echo "No results found.";

[mogosselin]

Not sure how using multiple LIKE clauses will help in my case; unless I input the keywords for each value manually as oppose to using the search form.

 

 

The code you just posted is doing exactly that. It takes the input, like "abc def hij", split all the words (with the space). And it's looping around all the words and appending it to the query.

 

REGEXP is something new that I never heard of.  I tried it like this and it doesn't seem to work.

 

 

REGEXP = Regular Expression. It's a special syntax that can "query" a string. 

 

Here's some examples: http://stackoverflow.com/questions/16166819/php-regular-expressions

 

There are so many tutorials out there that do multi word function but they are using old MySql, which is depreciated as you know.

 

 

You probably mean the mysql_* functions that are deprecated?

 

 

You can leave the mysql_* methods if you're not going to put your website/webapp live.

 

If you are going to put it online, you can change the mysql_* calls for mysqli_* , it should be easier to start with. 

 

Here's a post explaining how to do this:

http://stackoverflow.com/questions/1390607/how-could-i-change-this-mysql-to-mysqli

 

If you still want to use PDO, I would try to break it down. Start a new PHP file and try to connect to your database with PDO, then add a simple code that selects data from a single table  (SELECT * FROM TABLE). Then, you should be able to take your code and use it into your search form code.

[man5]

I am not home so I can't try any code yet.

 

However since searching last night, I found out that it is recommended to use MySql FULL-TEXT to create a search engine, as its faster and better for bigger database searching.  So I am going to try that.

 

is FULL-TEXT really the best solution for search engine?

[man5]

Alright so I had some time to play with mysql FULL-TEXT.  It works fine as a basic search. That's all I need for now.

 

Thanks for your help guys.

 

Until next time.