Simple Search - - Not! - PHPBuilder Forums

Simple Search - - Not!

Anon

Hello, I am new to php and Mysql and I need help with this simple search.

I am using a basic html search form to call on my php results page but I can only us one value at a time. I need to produce a result when more than one value appears in the same reord.

if the search is for Ford

the results page shows all Fords if the search is for Trucks the results page shows all trucks. But, if the user selects Ford and selects Trucks I get a mysql error

Please help me

[deleted]

show some of the code

Anon

Here you go, Please don\'t laugh :

if (!$order) {
$result = mysql_query(\"SELECT FROM new WHERE Make=\'$Make\' and Category=\'$Category\' Order by Birthstamp DESC\",$conn);
} else {
$result = mysql_query(\"SELECT FROM new WHERE Make=\'$Make\' and Category=\'$Category\' Order by $order $sort\",$conn);
}

The way this code is now, if the user trys to search for \"Mazda & Convertible\" (Make & Category) good results all Mazda Convertibles display. However, it the user attempts to search only Make or Only Category. No results will display.

Thank you for you interest . . .

Anon

Hey Ron,
Don't Worry no one laughes at anybodies code cause we all do go thru the stage in where we are novices.

I work on PHP/Oracle.What I would suggest is that instead of Category=\'$Category\' in ure query why don't u try the like operator with some wild cards (% to be one)
it can be something like this
Category like \'%$Category%\' ..this will give u results for all the records that have the word mentioned in category i.e the search text box..
do this and let me know..Usually the LIKE is a prime neccesity in search programs
cheers
Ashay,
http://www.metrulez.f2s.com

[deleted]

That's because your query always includes both parameters.

Try building your query dynamically, based on the info you have.
Something like:

$sQuery = "SELECT * FROM new WHERE 1=1 ";
if (isset($Make))
{
$sQuery .= " AND Make = '$Make ";
};
if (isset($Category))
{
$sQuery .= " AND Category = '$Category ";
};
if (!order)
{
$sQuery .=" ORDER BY Birthstamp DESC";
}
else
{
$sQuery .=" ORDER BY $Order $Sort";
};
if (!$result = mysql_query($sQuery, $conn))
{
echo mysql_error($conn);
}
else
{
// Process results.
};

[deleted]

It's not quite that simple.
When you use "LIKE '%foo%'" your database stops using indexes and forces a sequential scan. That can take quite some time to finish.
If the category is chosen from a predefined set of possibilities, then don't use the wildcard '%' signs!

Anon

Vincent you are right about the predefined variables. However, I did not mention that my search also requires text input. That is my fault I was too vague. This is a small database that will never have more than 100 rows/cars in it at a time. I greatly appreciate all of the good advice and help you people offered and for now I ended up with this code and it works fine but I will keep trying to use a variation of Vincent's code because it makes more sense to me. It will take me a few days to figure out though as I am very new to php.

include ("config.inc.php");

if (!$order) {
$result = mysql_query("SELECT FROM new WHERE Body_style like '%$Body_style%' and Make like '%$Make%' and Category like '%$Category%' and Model like '%$Model%' and Year like '%$Year%' Order by Birthstamp DESC",$conn);
} else {
$result = mysql_query("SELECT FROM new WHERE Body_style like '%$Body_style%' and Make like '%$Make%' and Category like '%$Category%' and Model like '%$Model%' and Year like '%$Year%' Order by $order $sort",$conn);
}

$numrows=mysql_num_rows($result);

if ($myrow = mysql_fetch_array($result)) {

if (empty($order)) { $order=Birthstamp; }
if (empty($sort)) { $sort=DESC; }

if ($sort != "ASC")
$stk = "<a href=\"ssresults.php?sort=ASC&order=Stock_no&Category=$Category\">Stock No.</a>";
if ($sort != "DESC")
$stk = "<a href=\"ssresults.php?sort=DESC&order=Stock_no&Category=$Category\">Stock No.</a>";

if ($sort != "ASC")
$yr = "<a href=\"ssresults.php?sort=ASC&order=Year&Category=$Category\">Year</a>";
if ($sort != "DESC")
$yr = "<a href=\"ssresults.php?sort=DESC&order=Year&Category=$Category\">Year</a>";

if ($sort != "ASC")
$mk = "<a href=\"ssresults.php?sort=ASC&order=Make&Category=$Category\">Brand</a>";
if ($sort != "DESC")
$mk = "<a href=\"ssresults.php?sort=DESC&order=Make&Category=$Category\">Brand</a>";

if ($sort != "ASC")
$md = "<a href=\"ssresults.php?sort=ASC&order=Model&Category=$Category\">Model</a>";
if ($sort != "DESC")
$md = "<a href=\"ssresults.php?sort=DESC&order=Model&Category=$Category\">Model</a>";

if ($sort != "ASC")
$prc = "<a href=\"ssresults.php?sort=ASC&order=Price&Category=$Category\">Price Range</a>";
if ($sort != "DESC")
$prc = "<a href=\"ssresults.php?sort=DESC&order=Price&Category=$Category\">Price Range</a>";

I guess cut n paste can only get me so far now it is time to learn something.