1

I need to Search Allthrough my database using PHP and MYSQL code , What i have done for now is 

$mysearch = $_GET['search'];//the search query , for ex - best potato
$recepiesuisine_quer = mysql_query("SELECT * FROM addrecepie WHERE  MATCH (name,method,contributedby,ingredients,healthytip,cuisine) AGAINST ('".$mysearch."' IN BOOLEAN MODE) ");
// Here the MATCH contain all the columns of my table
             while($row = mysql_fetch_assoc($recepiesuisine_quer)) {
             echo" 
                <a href='recepie_detail.php?id=".$row['id']."&cuisine=".$row['cuisine']."' id='foodie1_title' class='span-7'>
                <div class='span-1'><img src='".$row['image']."' width='80'></div>
                <div class='span-5'>
                    ".$row['name']." ,<br /> ".$row['contributedby'].",<br /> ".$row['cuisine']." <br />
                </div>
                </a>
             ";
             }

My Search is working fine for single character , but as if i want to search 2 characters , for ex :- Best Potato it will give me the result of

  1. All Potato anywhere in the table rows.

  2. All Best anywhere in the table rows.

  3. All Best Potato anywhere in the table rows.

I want my 3rd condition to be display at first and then after it may show 1st and 2nd result. I need to show the most related item first. With the above query i am unable to do so. Please Help me what query should i use.

hakre
  • 193,403
  • 52
  • 435
  • 836
Adwitya Media
  • 65
  • 1
  • 6
  • 5
    **Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). – eggyal Jun 05 '12 at 19:04
  • Also, please stop writing new code with the ancient MySQL extension: it is no longer maintained and the community has begun the [deprecation process](http://news.php.net/php.internals/53799); you can use instead either the improved [MySQLi](http://php.net/mysqli) extension or the [PDO](http://php.net/pdo) abstraction layer. – eggyal Jun 05 '12 at 19:04
  • Thank you eggyal , I will take your note into consideration – Adwitya Media Jun 07 '12 at 17:42

2 Answers2

0

You could use this query -

SELECT * 
FROM addrecepie 
WHERE  MATCH (name,method,contributedby,ingredients,healthytip,cuisine) 
       AGAINST ('".$mysearch."' IN BOOLEAN MODE) 
OR MATCH (name,method,contributedby,ingredients,healthytip,cuisine) 
       AGAINST (SUBSTRING_INDEX('".$mysearch."', ' ', 1) IN BOOLEAN MODE)
OR MATCH (name,method,contributedby,ingredients,healthytip,cuisine) 
       AGAINST (SUBSTRING_INDEX('".$mysearch."', ' ', -1) IN BOOLEAN MODE)

The SUBSTRING_INDEX() function would split the words with a space. This is for 2 words with 1 space. You might have to use the TRIM() function as well.

JHS
  • 7,761
  • 2
  • 29
  • 53
0

Always bind your variable otherwise mysql query will fail in case their is single quotes in search parameter value.

Onkar Janwa
  • 3,892
  • 3
  • 31
  • 47