Search by multiple keywords

sharapov

Hello,

I am using the following code to display a search from MySQL database. My problem is if I try to search database with several keywords, the search doesn't find any results. For example if I search for color and type "red" in my search field it would give me several results, but if I search for color and type red blue, it won't show any resolts whatsoever. How can I make my search to search for several keywords at the same time.


<html>
<head>
  <title>Search Results</title>
</head>
<body>
<h1>Search Results</h1>
<?
  if (!$searchtype || !$searchterm)
  {
     echo "You have not entered search details.  Please go back and try again.";
     exit;
  }

  $searchtype = addslashes($searchtype);
  $searchterm = addslashes($searchterm);

  @ $db = mysql_pconnect("localhost", "root", "password");

  if (!$db)
  {
     echo "Error: Could not connect to database.  Please try again later.";
     exit;
  }

  mysql_select_db("catalog");
  $query = "select * from cars where ".$searchtype." like '%".$searchterm."%'";
  $result = mysql_query($query);

  $num_results = mysql_num_rows($result);

  echo "<p>Number of records found: ".$num_results."</p>";

  for ($i=0; $i <$num_results; $i++)
  {
     $row = mysql_fetch_array($result);
     echo "<p><strong>".($i+1).". Model: ";
     echo stripslashes($row["model"]);
     echo "</strong><br>Color: ";
     echo stripslashes($row["color"]);
     echo "<br>Year: ";
     echo stripslashes($row["year"]);
     echo "<br>Price: ";
     echo stripslashes($row["price"]);
     echo "</p>";
  }

?>

</body>
</html>

bastien

$query = "select * from cars where ".$searchtype." in ".$searcharray.'";

where $searcharray is created by letting users enter comma separate items....(eg Ford, Ferrari, Porsche) in a textbox

you need to split the textbox data by the comma using the split() function

$array = split(" ",$textboxdata); //split on space
then manipulate the data to place a single quote around each word (eg (final result) $searcharray = ('Ford','Ferrari','Porsche'))
by iterating through the array and changing the data

$x=count($array); //counts the data elements (how many items)
$searcharray = "("; sets the opening bracket for the query
for ($i=0;$x<=$x;$i++){ //arrays are always starting at zero
if ($i<>$x){ //check for the last element as it doesn't need a
//ending comma
$searcharray.="'".$array[$i]."',"; //combines the quotes and
//the data value
}else{
$searcharray.="'".$array[$i]"'"; //last element therefore no
//ending comma
} //end the if statement
} //end the for loop

$searcharray.=")"; //add closing bracket
$sql="select from cars_table where model IN $searcharray";
echo $sql; //use this to test the statement...should read
//select from cars_table where model IN ('Ford','Ferrari','Porsche');

$result = mysql_query($sql);

hth

sharapov

Thanks for your reply. Can this be done with space " " instead of commas. I want my users to just list words in the search field like: Ford Nissan Toyota instead of Ford, Nissan, Toyota.

Do you mind posting the whole code. I am kind of new to PHP and have a hard time understanding what is going on if you write a snipet of the code.

Thanks

bastien

note that is the whole code...the only parts missing are the actual name of the text box that brings the info into the page, though you only need to substitute the name into the code supplied...

and the output code...to show the results...but that is straight forward

hth

sharapov

bastien,

Thank you for your help. I've made changes just like you have suggested, but I still have problems. My result page doesn't show no at all. Can you take a look at my code at the bottom. Perhaps I made a mistake somewhere. Thanks.

index.php


<html>
<head>
  <title>Catalog Search</title>
</head>

<body>
  <h1>Catalog Search</h1>

  <form action="results.php" method="post">
    Choose Search Type:<br>
    <select name="searchtype">
  <option selected value="item_number">Item Number</option>
  <option value="description ">Description</option>
  <option value="case_pack">Case Pack</option>
  <option value="upc_code">UPC Code</option>
  <option value="on_hand">On Hand</option>
  <option value="on_order">On Order</option>
  <option value="on_demand">On Demand</option>
  <option value="price">Price</option>
  <option value="cat_class">Category Class</option>
  <option value="ytd_sales">YTD Sales</option>
  </select>
    </select>
    <br>
    Enter Search Term:<br>
    <input name="searchterm" type=text>
    <br>
    <input type=submit value="Search">
  </form>

</body>
</html>

results.php

<html>
<head>
  <title>Search Results</title>
</head>
<body>
<h1><u>Search Results</u></h1>
<?
  if (!$searchtype || !$searchterm)
  {
     echo "You have not entered search details.  Please go back and try again.";
     exit;
  }

  $searchtype = addslashes($searchtype);
  $searchterm = addslashes($searchterm);

  @ $db = mysql_pconnect("localhost", "catalog", "");

  if (!$db)
  {
     echo "Error: Could not connect to database.  Please try again later.";
     exit;
  }

  mysql_select_db("catalog");



  $query = "select * from pricelist where ".$searchtype." IN ".$searcharray.";

$array = split(" ",$searchterm); 
$x=count($array);
$searcharray = "(";
for ($i=0;$x<=$x;$i++){
if ($i<>$x){
$searcharray.="'".$array[$i]."',";
}else{ 
$searcharray.="'".$array[$i]"'";
}
}
$searcharray.=")";

$sql="select * from pricelist where description IN $searcharray"; 
echo $sql;
$result = mysql_query($sql); 

  $num_results = mysql_num_rows($result);

  echo "<p>Number of records found: ".$num_results."</p>";

  for ($i=0; $i <$num_results; $i++)
  {
     $row = mysql_fetch_array($result);
     echo "<p><strong>".($i+1).". Item Number: ";
     echo stripslashes($row["item_number"]);
     echo "</strong><br>Description: ";
     echo stripslashes($row["description"]);
     echo "<br>Case Pack: ";
     echo stripslashes($row["case_pack"]);
     echo "<br>UPC Code: ";
     echo stripslashes($row["upc_code"]);
     echo "</p>";
  }

?>

</body>
</html>

bastien

there is a line

echo $sql;

what does it look like?

what are the errors if any?

sharapov

Unfortunately, it doesn't display any errors. When I submit the form "results.php" page turns out blank!! I think I messed up somewhere in the code. Can you take a look at it. I really appreciate your help.

sharapov

I tried my script on the different box and I got the following error:

Parse error: parse error, unexpected '\"' in c:\inetpub\wwwroot\secure\results.php on line 31

Aparently I turned off error notifications on my original machine, and that is why I didn't see any errors. But now it is fixed.

CAn you help me to find my mistake? Thanks.