Need Help on Searching recordset using multiple search terms

[TheCoupe]

Hi, I am trying to build a search query which allows selection of multiple fields, saletype(hidden field), Make, Model, Keyword, Min Price, Max Price etc but I'm having a headache of a time trying to make it work the way I want.

Basically, if no items in the form are changed, I want the search to just pull in all rows from the db that have a saletype of n, but if the user selects a make, then for it to pull in all records for saletype = n AND Make = somemake etc.

If I use AND statements in the WHERE clause, and don't make any selections in the form, this returns 0 results unless all fields are selected, and if I use OR statements, then it doesn't return the desired results.

I've just came across the MATCH statement, but never used it before... is this what I should be using?

My code for the db is as follows:

$colname_salesRS = "-1";
if (isset($_POST['saletype'])) {
  $colname_salesRS = (get_magic_quotes_gpc()) ? $_POST['saletype'] : addslashes($_POST['saletype']);
}
$colname2_salesRS = "-1";
if (isset($_POST['select'])) {
  $colname2_salesRS = (get_magic_quotes_gpc()) ? $_POST['select'] : addslashes($_POST['select']);
}
$colname3_salesRS = "-1";
if (isset($_POST['select1'])) {
  $colname3_salesRS = (get_magic_quotes_gpc()) ? $_POST['select1'] : addslashes($_POST['select1']);
}
mysql_select_db($database_dbname, $searchdb);
$query_salesRS = sprintf("SELECT aani_sales.sale_id, aani_sales.sale_make, aani_sales.sale_model, aani_sales.sale_customer, aani_sales.sale_year, aani_sales.sale_price, aani_sales.sale_title, aani_sales.sale_description, aani_sales.sale_mileage, aani_sales.sale_body, aani_sales.sale_fuel, aani_sales.sale_gearbox, aani_sales.sale_engine, aani_sales.sale_location, car_makes.car_make, car_models.car_model, aani_sale_pics.pic_image FROM aani_sales LEFT JOIN car_makes ON aani_sales.sale_make = car_makes.car_id LEFT JOIN car_models ON aani_sales.sale_model = car_models.car_mod_id LEFT JOIN aani_sale_pics ON aani_sales.sale_id = aani_sale_pics.sale_id WHERE aani_sales.sale_type = %s AND aani_sales.sale_make = %s AND aani_sales.sale_model = %s GROUP BY aani_sales.sale_id ORDER BY aani_sales.sale_price DESC", GetSQLValueString($colname_salesRS, "int"),GetSQLValueString($colname2_salesRS, "text"),GetSQLValueString($colname3_salesRS, "text"));
$salesRS = mysql_query($query_salesRS, $autoadsni) or die(mysql_error());
$row_salesRS = mysql_fetch_assoc($salesRS);
$totalRows_salesRS = mysql_num_rows($salesRS);

Has anyone any idea on how I could make this work?

Many Thanks

 

[designguy79]

Maybe you can post the actual query for us to look at?

echo $query_salesRS;
exit();

 

[TheCoupe]

Maybe you can post the actual query for us to look at?

echo $query_salesRS;
exit();

Hi, do you mean the actual SELECT code or the results without any WHERE statements being used?

 

[designguy79]

Don't need the HTML code, just what is being sent to the database server, in your case, the string $query_salesRS

Change your code snippet to what I have below, run it, and post the query that is "built" by your sprintf command.

$colname_salesRS = "-1";
if (isset($_POST['saletype'])) {
  $colname_salesRS = (get_magic_quotes_gpc()) ? $_POST['saletype'] : addslashes($_POST['saletype']);
}
$colname2_salesRS = "-1";
if (isset($_POST['select'])) {
  $colname2_salesRS = (get_magic_quotes_gpc()) ? $_POST['select'] : addslashes($_POST['select']);
}
$colname3_salesRS = "-1";
if (isset($_POST['select1'])) {
  $colname3_salesRS = (get_magic_quotes_gpc()) ? $_POST['select1'] : addslashes($_POST['select1']);
}
mysql_select_db($database_dbname, $searchdb);
$query_salesRS = sprintf("SELECT aani_sales.sale_id, aani_sales.sale_make, aani_sales.sale_model, aani_sales.sale_customer, aani_sales.sale_year, aani_sales.sale_price, aani_sales.sale_title, aani_sales.sale_description, aani_sales.sale_mileage, aani_sales.sale_body, aani_sales.sale_fuel, aani_sales.sale_gearbox, aani_sales.sale_engine, aani_sales.sale_location, car_makes.car_make, car_models.car_model, aani_sale_pics.pic_image FROM aani_sales LEFT JOIN car_makes ON aani_sales.sale_make = car_makes.car_id LEFT JOIN car_models ON aani_sales.sale_model = car_models.car_mod_id LEFT JOIN aani_sale_pics ON aani_sales.sale_id = aani_sale_pics.sale_id WHERE aani_sales.sale_type = %s AND aani_sales.sale_make = %s AND aani_sales.sale_model = %s GROUP BY aani_sales.sale_id ORDER BY aani_sales.sale_price DESC", GetSQLValueString($colname_salesRS, "int"),GetSQLValueString($colname2_salesRS, "text"),GetSQLValueString($colname3_salesRS, "text"));
echo '<hr>The query being sent is: <pre>'.$query_salesRS;
exit();
$salesRS = mysql_query($query_salesRS, $autoadsni) or die(mysql_error());
$row_salesRS = mysql_fetch_assoc($salesRS);
$totalRows_salesRS = mysql_num_rows($salesRS);

 

[TheCoupe]

That statement returns:

SELECT aani_sales.sale_id, aani_sales.sale_make, aani_sales.sale_model, aani_sales.sale_customer, aani_sales.sale_year, aani_sales.sale_price, aani_sales.sale_title, aani_sales.sale_description, aani_sales.sale_mileage, aani_sales.sale_body, aani_sales.sale_fuel, aani_sales.sale_gearbox, aani_sales.sale_engine, aani_sales.sale_location, car_makes.car_make, car_models.car_model, aani_sale_pics.pic_image FROM aani_sales LEFT JOIN car_makes ON aani_sales.sale_make = car_makes.car_id LEFT JOIN car_models ON aani_sales.sale_model = car_models.car_mod_id LEFT JOIN aani_sale_pics ON aani_sales.sale_id = aani_sale_pics.sale_id WHERE aani_sales.sale_type = -1 AND aani_sales.sale_make = '-1' AND aani_sales.sale_model = '-1' GROUP BY aani_sales.sale_id ORDER BY aani_sales.sale_price DESC

 

[designguy79]

Without knowing the structure of your database, it looks to me like you need some "if" statements to test if any of the dropdown selects are not being picked (i.e., they are defaulting to "-1") and then not include them in your query.

Sorry, I don't have time (at the moment) to re-write your PHP... maybe somebody else can help at this point, otherwise I will check back in later...

 

[TheCoupe]

Thanks anyway for pointing me in the right direction.

I've been trying out the IF statements, and getting nowhere

having tried this:

SELECT aani_sales.sale_id, aani_sales.sale_make, aani_sales.sale_model, aani_sales.sale_customer, aani_sales.sale_year, 
aani_sales.sale_price, aani_sales.sale_title, aani_sales.sale_description, aani_sales.sale_mileage, aani_sales.sale_body, 
aani_sales.sale_fuel, aani_sales.sale_gearbox, aani_sales.sale_engine, aani_sales.sale_location, car_makes.car_make, 
car_models.car_model, aani_sale_pics.pic_image
FROM aani_sales 
LEFT JOIN car_makes ON aani_sales.sale_make = car_makes.car_id 
LEFT JOIN car_models ON aani_sales.sale_model = car_models.car_mod_id 
LEFT JOIN aani_sale_pics ON aani_sales.sale_id = aani_sale_pics.sale_id
WHERE aani_sales.sale_type = 2 
AND IF(aani_sales.sale_make <> NULL, 1, 0) = 0 
AND IF(aani_sales.sale_model <> NULL, 1, 0) = 0
GROUP BY aani_sales.sale_id
ORDER BY aani_sales.sale_price DESC

using DB Solo as a query builder, it works a treat, and when I replace the NULL values with numbers from within the database, it pulls all the info properly, though when used in my page, I'm getting the same results... either everything displays when no other form elements are selected, or I have to select both make and model form fields to get some sort of result.

Any further help or pointers on what I'm doing wrong would be greatly appreciated.

Thanks again in advance

 

[designguy79]

I should have to help you later tonight... I am in EST.

Sorry should have been more clear; the "if" statements in are PHP. Something like...

$query = 'SELECT rows FROM table WHERE 1=1 ';

if(isset($_POST['option1']) AND $_POST['option1']!='-1'))
   $query .= ' AND optioncolumn="'.mysql_escape_string($_POST['option1']).'" ';

if(isset($_POST['option2']) AND $_POST['option2']!='-1'))
   $query .= ' AND optioncolumn="'.mysql_escape_string($_POST['option2']).'" ';

// now run the query

Basically what you are doing is "building" a custom query (string) in PHP first before sending it to SQL. If I lost you on any of the above PHP code, I would highly recommend getting someone to help and/or reading some PHP tutorials.

I'll check in later, too!

 

[TheCoupe]

Just wanted to say a massive thank you for pointing me in the right direction... and also for not directly writing my code for me as, although it took a bit more time... I learnt by it.

Now all I've got to do is focus on a keyword search part and searching a location from the contents of a searchbox array lol, though thanks to your help, I've successfully completed 9 fields of the single search