Need Help on Searching recordset using multiple search terms

Discussion in 'Web Design and Development' started by TheCoupe, Feb 15, 2010.

  1. macrumors member

    Joined:
    Mar 4, 2009
    Location:
    Northern Ireland
    #1
    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:
    Code:
    $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 :)
     
  2. macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #2
    Maybe you can post the actual query for us to look at?

    PHP:
    echo $query_salesRS;
    exit();
     
  3. thread starter macrumors member

    Joined:
    Mar 4, 2009
    Location:
    Northern Ireland
    #3
    Hi, do you mean the actual SELECT code or the results without any WHERE statements being used?
     
  4. macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #4
    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.

    PHP:
    $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);
     
  5. thread starter macrumors member

    Joined:
    Mar 4, 2009
    Location:
    Northern Ireland
    #5
    That statement returns:
    PHP:
    SELECT aani_sales.sale_idaani_sales.sale_makeaani_sales.sale_modelaani_sales.sale_customeraani_sales.sale_yearaani_sales.sale_priceaani_sales.sale_titleaani_sales.sale_descriptionaani_sales.sale_mileageaani_sales.sale_bodyaani_sales.sale_fuelaani_sales.sale_gearboxaani_sales.sale_engineaani_sales.sale_locationcar_makes.car_makecar_models.car_modelaani_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 = -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
     
  6. macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #6
    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...
     
  7. thread starter macrumors member

    Joined:
    Mar 4, 2009
    Location:
    Northern Ireland
    #7
    Thanks anyway for pointing me in the right direction.

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

    having tried this:
    PHP:
    SELECT aani_sales.sale_idaani_sales.sale_makeaani_sales.sale_modelaani_sales.sale_customeraani_sales.sale_year
    aani_sales.sale_priceaani_sales.sale_titleaani_sales.sale_descriptionaani_sales.sale_mileageaani_sales.sale_body
    aani_sales.sale_fuelaani_sales.sale_gearboxaani_sales.sale_engineaani_sales.sale_locationcar_makes.car_make
    car_models.car_modelaani_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 
    AND IF(aani_sales.sale_make <> NULL10) = 
    AND IF(aani_sales.sale_model <> NULL10) = 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 :)
     
  8. macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #8
    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...

    PHP:
    $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!
     
  9. thread starter macrumors member

    Joined:
    Mar 4, 2009
    Location:
    Northern Ireland
    #9
    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 :D
     

Share This Page