1

Okay, I'm quite new to Mysql and I can't find a simple solution to my question. I want to search my database and return all instances of a string. ex: user types "hello world" and database returns queries of "hello my name is world" etc. I'll take these results and push them into a php array.

here's my attempt of it according to this:

include '../config.php';
$con = new PDO('mysql:host='. DB_HOST .';dbname='. DB_NAME .'', DB_USER,DB_PASSWORD);

$firstsearcharray = array(); // push into this array
$searchqueryarray = explode(" ",$searchquery);//$searchquery is data sent from AJAX
$query = $con->prepare("SELECT * FROM srating where MATCH(title) AGAINST (':searchqueryarray')");
$query->bindParam(':$searchqueryarray',$searchqueryarray);
$query->execute();
$data = $query->fetch(PDO::FETCH_ASSOC);//I have to somehow push each result into an array (possibly using array_push and a for loop)

I believe that the way I'm searching isn't optimal. I would prefer to use CONTAINS over LIKE as I've heard that it is faster, but it is way less documented than LIKE.

Curtis Chong
  • 783
  • 2
  • 13
  • 26

1 Answers1

1

This is a solution for fulltext search.

An example:

SELECT id,prod_name, match( prod_name )
AGAINST ( '+harpoon +article' IN NATURAL LANGUAGE MODE) AS relevance
FROM testproduct 
ORDER BY relevance DESC

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Like and contains are a waste of time.

Drew
  • 24,851
  • 10
  • 43
  • 78
  • so to clarify: -id is the actual column called id -prod_name is the name of the table -testproduct is the array that I'm sending in to be searched (in this case it is $searchqueryarray) now how do I retrieve the data through a php variable (ex: $firstsearcharray) i've looked through the article and +harpoon and +article doesn't make sense -I put it in a PDO ex: $query = $con->prepare("SELECT id,prod_name, match( prod_name )AGAINST ( '+harpoon +article' INNATURALLANGUAGE MODE)ASrelevanceFROMtestproductORDER BY relevance DESC"); thanks for your answer and I'll continue to search for a solution – Curtis Chong Jul 11 '15 at 13:59
  • It was just an example and different table structure, but to point you to concept with the url provided. Let me give you a link in a second to a somewhat complete example using it. Hold on – Drew Jul 11 '15 at 14:18
  • Keep in mind that fulltext search in any dbms is probably going to disappoint. For robust search many of us integrate with systems like `solr` that have one focus (search) – Drew Jul 11 '15 at 14:34
  • yea after I find the queries, I'll do additional steps on my searched queries to further my search ex: look inside the files and count the number of occurrences of a string – Curtis Chong Jul 11 '15 at 14:44