multiple word query on simple phpMysql script

peter_G · November 26th, 2004, 07:37 AM

Hi,

i have a simple script to search a MySQL database and return values. I cant seem to work out a way for the code to allow multiple word queries. If you type for example "CPU intel" it doesnt display all results for "CPU" and "Intel" just the results for an exact match.
Can anyone help?
The code i have is as follows;

<?
if ($search) // perform search only if a string was entered.
   {
     mysql_connect("host", "db", "pw") or die ("Problem connecting to DataBase");

    $srch="%".$search."%";
    $query = "select * from price WHERE description LIKE '$srch' || ID LIKE '$srch'";

    $result = mysql_db_query("db", $query);

    if ($result)
    {
    echo "Here are the results of your Search:<br>";

        while ($r = mysql_fetch_array($result)) { // Begin while
$description = $r["Description"];
$excprice = $r["excprice"];
$incprice = $r["incprice"];
$make = $r["Make"];
$ID = $r["ID"];
$category = $r["Category"];
                  echo "<br><table width=610 border=0 align=center cellpadding=0 cellspacing=0 bgcolor=#336699>
<tr><td width=30><img src=button/singlepriceside_l.jpg></td>
<td><table width=550 border=0 align=center cellpadding=0 cellspacing=0 bgcolor=#336699>
<tr><td colspan=3><div align=left>$description</div></td></tr>
<tr><td width=190><div align=left><strong>£$excprice</strong>excl.£$incprice incl.</div></td>
<td><div align=center>Manufacturer:$make</div></td>
<td width=120><div align=right>Product Code: $ID</div></td></tr>
<tr><td height=19 colspan=3><div align=center><a href=\"/$category/$ID.php\"><CLICK HERE FOR MORE INFO></a></div></td></tr></table></td>
<td width=30><div align=right><img src=../button/singlepriceside_r.jpg></div></td></tr></table>";
        } // end while
     } else { echo "problems...."; }
   } else {
   echo "No search text entered!. <br> Type your search query in to the text box under 'PRODUCT SEARCH' and click 'GO!'";
   }

?>

Ive only just moved on to Php and MySQL so my knowledge is still quite limited im afraid. Is it just a case of adding "multiple" somewhere?

Thank you in advance
Peter g.

Snib · November 26th, 2004, 12:07 PM

First thing: you really shouldn't post in the feedback area. This is a PHP Databases issue.

The solution to your problem lies in splitting the search string into words, and then making your query like this:

SELECT * FROM whatever WHERE some_field LIKE 'CPU' OR some_field LIKE 'intel' OR ...etc

hth,

-Snib
Where will you be in 100 years?
Try new FreshView 0.2!

peter_G · November 26th, 2004, 12:15 PM

Thank you.

i can understand what your saying i need, but i cant understand what you are suggesting i do.

I need to split the search string to identify the words individually. but how? do you mean have several search fields to type the search term in?

isn't there a code to identify each word from a search term?

Snib · November 26th, 2004, 03:06 PM

Hey there,

It will look something like this:

Code:

//get the submitted data
$searchtext = $_POST['searchtext'];

//split it into an array
$searchwords = explode(" ",$searchtext);

//loop and add the words to the query
$query = "SELECT * FROM table WHERE field LIKE '$searchwords[0]'";

for($i = 1;$i < count($searchwords); $i++)
{
    $query .= " OR field LIKE '$searchwords[$i]'";
}

//do the query
$result = mysql_query($query);

//other code here

hope this clears things up a bit,

-Snib
Where will you be in 100 years?
Try new FreshView 0.2!

peter_G · December 1st, 2004, 12:48 PM

Thank you very much for your time. Unfortunatly after playing with the code its not giving any results now. What i have now is as follows;

<?
if ($search) // perform search only if a string was entered.
   {
     mysql_connect("localhost", "database", "pwd") or die ("Problem connecting to DataBase");

//get the submitted data
$searchtext = $_POST['search'];

//split it into an array
$searchwords = explode(" ",$searchtext);

//loop and add the words to the query
$query = "SELECT * FROM price WHERE description LIKE '$searchwords[0]'";

for($i = 1;$i < count($searchwords); $i++)
{
    $query .= " OR description LIKE '$searchwords[$i]'";
}

//do the query
  $result = mysql_db_query("database", $query);

then my result display stuff
************************************************** ************

what have i done wrong? im not sure if this is significant but after my results i have the following;

} else { echo "problems...."; }
   } else {
   echo "No search text entered!. <br> Type your search query in to the text box under 'PRODUCT SEARCH' and click 'GO!'";
   }

?>

Thank you again for your time.

Snib · December 1st, 2004, 03:28 PM

echo the value of $query after the for() loop, and post what it says here.

-Snib - http://www.snibworks.com
Where will you be in 100 years?

peg110 · December 1st, 2004, 04:56 PM

Any time you use the LIKE keyword you should use a WILDCARD. MySQL uses % and _ where Microsoft uses * and ?, but to use LIKE without one, you might as well say it's EQUAL.

Using peter_g's example

  <?
  if ($search) // perform search only if a string was entered.
     {
       mysql_connect("localhost", "database", "pwd") or die ("Problem connecting to DataBase");

  //get the submitted data
  $searchtext = $_POST['search'];

  //split it into an array
  $searchwords = explode(" ",$searchtext);

  //loop and add the words to the query
  $query = "SELECT * FROM price WHERE description LIKE '%".$searchwords[0]."%'";

  for($i = 1;$i < count($searchwords); $i++)
  {
      $query .= " OR description LIKE '%".$searchwords[$i]."%'";
  }

  //do the query
  $result = mysql_db_query("database", $query);

  // etc....

Hope this helps..

Paul Gardner
------------------
PHP-LIVE help
Via Web @ http://www.mnetweb.co.uk/irc
Via IRC Client pgardner.net:6667
room #PHP

peter_G · December 1st, 2004, 05:22 PM

Thank you both.

i have taken onboard both suggestions and now have the following code;

<?
if ($search) // perform search only if a string was entered.
   {
     mysql_connect("localhost", "database", "pwd") or die ("Problem connecting to DataBase");

//get the submitted data
$searchtext = $_POST['search'];

//split it into an array
$searchwords = explode(" ",$searchtext);

//loop and add the words to the query
$query = "SELECT * FROM price WHERE description LIKE '%".$searchwords[0]."%'";

for($i = 1;$i < count($searchwords); $i++)
{
  echo $query .= " OR description LIKE '%".$searchwords[$i]."%'";
}

//do the query
  $result = mysql_db_query("cheaperc_guide", $query);

**********************************

now the search works for one word searches plus 2 word searched HOWEVER. when you submit a search with more than one word, i get the following line display above the results;

SELECT * FROM price WHERE description LIKE '%mouse%' OR description LIKE '%cpu%'

I assume i have a bracket or something missing somewhere? but cant see one.

Thank you

peg110 · December 2nd, 2004, 09:55 AM

drop the echo in

  for($i = 1;$i < count($searchwords); $i++)
  {
    echo $query .= " OR description LIKE '%".$searchwords[$i]."%'";
  }

make it look like this

  for($i = 1;$i < count($searchwords); $i++)
  {
    $query .= " OR description LIKE '%".$searchwords[$i]."%'";
  }

Paul Gardner
------------------
PHP-LIVE help
Via Web @ http://www.mnetweb.co.uk/irc
Via IRC Client pgardner.net:6667
room #PHP

peter_G · December 2nd, 2004, 10:21 AM

were not worthy
were not worthy
were not worthy

cheers.