Search by multiple keywords

sharapov · Nov 7, 2002

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&quot

;

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

mysql_select_db("catalog&quot

;
$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>

sleipnir214 · Nov 7, 2002

I would imagine it's because of how you construct your query.

With the code you've posted, the only way you can get multiple column names into the where clause of the query is to put both names in the field "searchtype". But that would construct a string like:

"select * from cars where firstfield, secondfield like '%mysearchterm%'"

However, this is not a valid SQL statement. To search for a single term across multiple fields, you need to built an SQL statement of the form:

"select * from cars where firstfield like '%mysearchterm%' and secondfield like '%mysearchterm%'"

Unfortunately, your script does not contain code which would trap an error which would be generated by such an ill-formed query. It simply reports that you didn't get any rows back.

Here's my standard advice for debugging MySQL database-connection code:

[ul][li]Trap every error it is possible to trap, and get in the habit of doing it every time. Make sure that you print out every error that MySQL presents to your code. This is the easiest way to know exactly where your code went wrong[/li][li]Verify that your query is well-formed. Print it out to the screen and examine it by hand. Does it look right to you?[/li][li]Cut that query from your browser and paste it into your favorite MySQL admin tool, then execute it. What does MySQL have to say about it?[/li][/ul]

______________________________________________________________________
TANSTAAFL!

sharapov · Nov 7, 2002

I don't think you understood what I am trying to do. Lets say in the description of a car under color I have "red, green, blue, yellow, etc." If I search for "red" for example, it will give me all resuls that have word "red" in it. But if I search "red yellow" for example it doesn't give me any results. This is because red and yellow don't appear next to each other in the database. However if I search for red green I will get all the results that have red green next to each other. However, what I am trying to do is to type any combination of words in any order and let my search find it in the database.

Here is how I constracted my search form (if that of any help)

<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 value="model">Model
<option value="volor">Color
<option value="year">Year
<option value="price">Price
</select>
<br>
Enter Search Term:<br>
<input name="searchterm" type=text>
<br>
<input type=submit value="Search">
</form>

</body>
</html>

sleipnir214 · Nov 7, 2002

To perform a case-insensitive single-word-term search that assumes you are always looking for <term> or <term>, convert any incidences of one or more non-word characters to "|". Then construct your query as:

SELECT * FROM cars WHERE lower(".$searchtype.&quot

regex '".strtolower($searchterm)."'"

If you select "model", and enter "Buick Ford", then it should produce a query like"

SELECT * FROM cars where lower(model) regex buick|ford

______________________________________________________________________
TANSTAAFL!

jimoblak · Nov 7, 2002

Here are my two cents (which may be the wrong currency for your locale): I just did the same thing but cheated and left a note to the user to enter only one term to get the most results.

What I will eventually do when I have the time (and what you probably need to do as well) is to break up the string wherever there is a space, then use the broken portions in an array.
http://www.php.net/manual/en/function.strtok.php

...But I got hung up on how to pass that array into the query... Does one create several queries or can the array of several search words fit into a single query?

jimoblak · Nov 7, 2002

I hit submit moments after sleipnir214 posted last - - it seems sleipnir214 has the answer.

Ignore my previous rambling.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Search by multiple keywords

sharapov

MIS

sleipnir214

Programmer

sharapov

MIS

sleipnir214

Programmer

jimoblak

Instructor

jimoblak

Instructor

Similar threads

Part and Inventory Search

Sponsor