Page 1 of 1

How can i search two words in two columns?

Posted: Sat May 23, 2009 6:24 am
by djdon11
Hello All,

I have some specific requirements with my search which is :

I have a table for products which contains fields :

Code: Select all

 
product_id (int) pk
product_name (text)
product_price (decimal)
product_category (varchar)
 
Now i need to search my text with the fields product_name and product_category. For ex. if i search "nokia mobile" then if category contains the word "mobile" and product_name contains the word "nokia" then it should come in the result set. This is just an example of text search. the searching text can be anything like "mobile nokia N 70" or "N 70 mobile" etc.

Can anybody help me over this, how can i do this efficiently?

Thanks in advance

Re: How can i search two words in two columns?

Posted: Sat May 23, 2009 7:02 am
by anand
djdon11 wrote:Hello All,

I have some specific requirements with my search which is :

I have a table for products which contains fields :

Code: Select all

 
product_id (int) pk
product_name (text)
product_price (decimal)
product_category (varchar)
 
Now i need to search my text with the fields product_name and product_category. For ex. if i search "nokia mobile" then if category contains the word "mobile" and product_name contains the word "nokia" then it should come in the result set. This is just an example of text search. the searching text can be anything like "mobile nokia N 70" or "N 70 mobile" etc.

Can anybody help me over this, how can i do this efficiently?

Thanks in advance
how about this

Code: Select all

$query = mysql_query("SELECT * FROM table WHERE product_category='mobile' AND product_name='nokia' LIMIT 1;") OR die(mysql_error());
 
// if its either category OR name, THEN
 
$query = mysql_query("SELECT * FROM table WHERE product_category='mobile' OR product_name='nokia' LIMIT 1;") OR die(mysql_error());
This might help you.

Also you can use LIKE function.

Re: How can i search two words in two columns?

Posted: Sat May 23, 2009 7:08 am
by djdon11
Thanks for reply..
But the requirements are different buddy.....

I have only one text field in which user can type anything like "nokia mobile" , "nokia N 70" , "nokia", "mobile" or anything else for other products then mobile. so i can not use just OR , AND clauses only there must be some further logic for this..

Thanks for your reply again,

Re: How can i search two words in two columns?

Posted: Sat May 23, 2009 7:26 am
by anand
djdon11 wrote:Thanks for reply..
But the requirements are different buddy.....

I have only one text field in which user can type anything like "nokia mobile" , "nokia N 70" , "nokia", "mobile" or anything else for other products then mobile. so i can not use just OR , AND clauses only there must be some further logic for this..

Thanks for your reply again,
Hi djdon11, As you can see I am new here. :D

I thought you want to pick up a row which had entries something like that. If you want to use it as search, then how about this.....

Code: Select all

$search = '<--The thing user wants to search-->';
 
$query = mysql_query("SELECT * FROM table WHERE product_category LIKE '%$search%' OR product_name LIKE '%$search%';") OR die(mysql_error());
It'll pull up any row which matches/comes close to your search terms.

P.S. I am also working on a thing similar to this. So, I am also interested about this topic. IF anyone has a better option, I'll be glad to know as well.

Re: How can i search two words in two columns?

Posted: Sat May 23, 2009 7:34 am
by djdon11
Yes we can do that... but there is one more condition that if user enters suppose "nokia 70" then there should be listing of the record with the product name "nokia 70" only... :oops: ... that is why i avoided "%"

By the way Anand i have something for you if this can help you

Just replace the database connection and db name variables

Code: Select all

 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html lang='en'>
<head>
<META HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=ISO-8859-1'>
<title>Page title</title>
<style type="text/css">
table {
  border-collapse: collapse;
  border: solid 1px black;
}
td {
  padding: 2px 6px;
  border: solid 1px black;
}
</style>
</head>
<body>
<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post'>
<p>Search for: <input type='text' name='search' size='20' maxlength='64'></p>
<p><input type='submit' value='Search'></p>
</form>
<?php
if(isset($_POST['search']))
{
  $connx = mysql_connect('localhost', '', '') or die("connx");
  $db = mysql_select_db('bargainsite') or die(mysql_error());
  # convert to upper case, trim it, and replace spaces with "|":
  $search = (ini_get('magic_quotes_gpc')) ? stripslashes($_POST['search']) :
            $_POST['search'];
  $search = mysql_real_escape_string($search);
  $search = preg_replace('/\s+/', '|', trim($_POST['search']));
  
  # create a MySQL REGEXP for the search:
  $regexp = "REGEXP '[[:<:]]($search)[[:>:]]'";
   $query = "SELECT * FROM `pc_products` WHERE `product_name` $regexp OR ".
           "`product_category` $regexp";
  $result = mysql_query($query) or die($query . " - " . mysql_error());
  echo "<table>\n";
  while($row = mysql_fetch_assoc($result))
  {
    echo "<tr>";
    foreach($row as $key => $value)
    {
      echo "<td>$value</td>";
    }
    echo "</tr>\n";
  }
}
?>
</body>
</html>
 
 
 
 

Re: How can i search two words in two columns?

Posted: Sat May 23, 2009 7:50 am
by anand
Wow.. great code.

how about using explode()?

separate all the words and then search them individually.

Code: Select all

$search  = "nokia n70 mobile phone cheap price";
$query = explode(" ", $search);
 
# So, by this, query will be of 6 letters.
 
$query[0] = nokia
$query[1] = n70
 
#and so on.