How can i search two words in two columns?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
djdon11
Forum Commoner
Posts: 90
Joined: Wed Jun 20, 2007 5:03 pm

How can i search two words in two columns?

Post 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
anand
Forum Commoner
Posts: 80
Joined: Fri May 22, 2009 11:07 am
Location: India
Contact:

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

Post 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.
djdon11
Forum Commoner
Posts: 90
Joined: Wed Jun 20, 2007 5:03 pm

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

Post 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,
anand
Forum Commoner
Posts: 80
Joined: Fri May 22, 2009 11:07 am
Location: India
Contact:

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

Post 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.
djdon11
Forum Commoner
Posts: 90
Joined: Wed Jun 20, 2007 5:03 pm

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

Post 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>
 
 
 
 
Last edited by Benjamin on Tue May 26, 2009 11:22 am, edited 1 time in total.
Reason: Changed code type from text to php.
anand
Forum Commoner
Posts: 80
Joined: Fri May 22, 2009 11:07 am
Location: India
Contact:

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

Post 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.
Post Reply