CODEWITHSUNDEEP
phpmysqlmysqli
user3077454
user3077454

Reputation: 9

Multiple search query php

i have a database where i have title

Titles
Celeste from the moon
Sweet Dreams Tomorrow
Time machine from past

Now when i do my search query for keyword lets say 'celeste from' then it displays me what i want i.e. Celeste from the moon. However if i change my query to 'celeste the moon' , then it shows me no results.

This is my query script

      if(!empty($_REQUEST['string'])) 
{
      $search_string = " title LIKE %".mysql_real_escape_string($_REQUEST["string"])."%'";
      $query = mysql_query("SELECT * FROM products WHERE ".$search_string);

      $row = mysql_num_rows($query) or die(mysql_error());

      $row = $row; 

      $cur_page = $_GET['page'];
      $cur_page = ($cur_page < 1)? 1 : $cur_page; 
      $offset = ($cur_page-1)*$per_page;                

      $pages = ceil($row/$per_page);              

      $start = (($cur_page - $num_links) > 0) ? ($cur_page - ($num_links - 1)) : 1;
      $end   = (($cur_page + $num_links) < $pages) ? ($cur_page + $num_links) : $pages;

      $res  =  mysql_query("SELECT * FROM products WHERE ".$search_string." ORDER BY title LIMIT ".$per_page." OFFSET ".$offset);  

      while($row=mysql_fetch_array($res))
      {
      include ('include/form.php');
      }

  }

I tried various ways but just not able to get the desired results.

Upvotes: 0

Views: 111

Answers (2)

elixenide
elixenide

Reputation: 44833

The problem is that your query looks for the exact text you enter. So, if you enter Celeste the moon, it looks for text containing exactly that phrase. WHERE x LIKE '%foo bar%' means "find rows where x contains exactly foo bar, but it's okay if there's other stuff before or after it.

You need to break it up, like Shidil suggests, but you need to escape each and every part of the query. Shidil's code has some logic errors and is insecure. So, for example (modified, corrected, and more secure version of Shidil's code):

$words = explode(' ',$_REQUEST['string']); // split up the input

$query='SELECT * FROM `products` WHERE '; // first part of the query

foreach($words as $key=> value) {

    /* Build the WHERE clause
       Note that you need `title` LIKE for each term, and each term
       needs to be run through mysql_real_escape_string() */
    $query.='`title` LIKE "%'.mysql_real_escape_string($value).'%" OR';
}

// Strip the last "OR"
$query=rtrim($string, "OR");

/* You don't show where $per_page or $offset came from.
   If they are user input (even a drop-down or something), you MUST
   validate them; otherwise, you will have a SQL injection vulnerability */
$query.=' ORDER BY title LIMIT '.$per_page.' OFFSET '.$offset;

include 'include/common.php';

/* don't put mysql_query() in the while(); it will run the query over and over,
   but you'll just keep getting the first row over and over */
$result = mysql_query($query);

while($row=mysql_fetch_array($result)) {
    include ('include/form.php');
}

And as everyone has pointed out: Don't use mysql_*; the mysql_* functions are outdated, deprecated, and insecure. Use MySQLi or PDO instead.

Upvotes: 0

Shidil Eringa
Shidil Eringa

Reputation: 341

First you split the search string

$words = explode(' ',$_REQUEST['string']);

then use the OR operation in select

$query='SELECT * FROM `products` WHERE `title` LIKE ';
foreach($words as $key=> value){
    $query.='"%'.$value.'%" OR';
}
$query=rtrim($string, "OR");
$query.='ORDER BY title LIMIT '.$per_page.' OFFSET '.$offset;
include 'include/common.php';
while($row=mysql_fetch_array(mysql_query($query))) {
            include ('include/form.php');
    }

Nex time you use mysqli instead of mysql functions

Upvotes: 1

Related Questions