[Dallasjmoore]

I'm looking for the best way to provide the best search results.

The database is much more complex, but lets keep it simple for this example.
Lets say I only have one table named "products" that has the following fields:
1. category
2. vehicle_make
3. vehicle_model
4. vehicle_year
3. product_name
4. product_description
5. price


The search form has 3 fields:
1. Category (list menu)
2. Vehicle Make (list menu)
3. Keyword (text field)

Here is quick example at: http://monsterbumpers.com/search7.php

Question 1: (Search Form)
Within the dropdown lists "category" and "vehicle make" I've created a select option of "Any" for any category or any make. What should I set the value for these two selections to allow a search through all categories and/or all vehicle makes?

Question 2: (Results Page)
How can I write a query to search by:
- a single variable (ex: search just by 'category' only),
- a combination of variables (ex: 'vehicle make' & 'keyword' only),
- or all of the variables ('category', 'vehicle make' , & 'keyword')

Question 3:
How can I order the results by relevance grouped by category and vehicle_make?

[Advertisements]

Why not just do it the easy way?

Make mutliple if statements because i guess you're using php. So..

if($_POST[category] != "" AND $_POST[keyword] == "")
{
$sql = "SELECT * FROM products WHERE vehicle_category='$_POST[category]'";
}
elseif($.......

Seems like a logical method?

[BirdieUK]

Why not just do it the easy way?

Make mutliple if statements because i guess you're using php. So..

if($_POST[category] != "" AND $_POST[keyword] == "")
{
$sql = "SELECT * FROM products WHERE vehicle_category='$_POST[category]'";
}
elseif($.......

Seems like a logical method?

[Dallasjmoore]

Thanks! I'm still having a little trouble with the correct PHP code. Could you elaborate on the syntax if I'm using the "get" method instead of "post"?

[BirdieUK]

Thanks! I'm still having a little trouble with the correct PHP code. Could you elaborate on the syntax if I'm using the "get" method instead of "post"?

Er, you simply just change $_GET to $_POST. Sorry i just typed it off my head.

Edited by BirdieUK, 13 July 2007 - 07:17 PM.

[Stryker250]

Tru this,or atleast your form and code should look something like this...

This is just the search box

<form method="get" action="search.php">
<p><input type="text" name="keywords" /></p>
<p><select name="searchFor">
<option value="any">Any</option>
<option value="cat">Category</option>
<option value="make">Vehicle Make</option>
</select></p>
<p><input type="submit" value=" Search " /></p>

Here is the php script (search.php)

$keyword = $_GET['keywords'];

if(isset($_GET['searchFor']) && $_GET['searchFor'] == 'cat'){
//The person is searching for a category

$where = "WHERE category LIKE '%$keyword%' ORDER BY price";

}else if(isset($_GET['searchFor'] && $_GET['searchFor'] == 'make'){
//The user is searching for a make

$where = "WHERE vehicle_make LIKE '%$keyword%' ORDER BY price";

}else if(isset($_GET['searchFor'] && $_GET['searchFor'] == 'any'){
//The user has chosen to search for any and all occurnces of his chosen keywords

$where = "WHERE category LIKE '%$keyword%' OR vehicle_make LIKE '%$keyword%' OR vehicle_model like '%$keyword%' OR 

vehicle_year LIKE '%$keyword%' OR product_name LIKE '%$keyword%' OR product_description LIKE '%$keyword%' ORDER BY price";
}

$query = "SELECT * FROM theTableName ".$where."";
$result = mysql_query($query);
while($info = mysql_fetch_assoc($result)){
$productName = $info['product_name']'
$productDescr = $info['produce_description'];
$price = $info['price'];

echo "<p>".$productName."<br />".$productDescr."<br />".$price."</p>";

}

/***
*  I used the LIKE option in the queries rather than the = sign because
*  the LIKE option searches for any occurance of the text / keywords in the
*  given table field, instead of looking for exact matches
***/

Hope that helps