Search problems using multiple fields

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • WesternCanuk
    New Member
    • Nov 2007
    • 2

    #1

    Search problems using multiple fields

    Hello all. i'm new to mysql and a but of advice would be fantastic. I"m building a PHP website where i'm have problems getting my search query to work. I have built the search using AND and OR but this process is very limited. Here is the background. The user will be passing 4 variables from one page to another.

    buildingname
    buildingblock
    projectnumber
    date

    Table to search in is called paintinfo, and has 17 diff fields within it. 4 of the fields corrispond to the above variables.

    now if i search using the AND i get bad results because the code does not check to see if the variable is blank or not. I'm hoping there is a way to check to see if the variable is blank then do not use that variable. If the field has info in it then build the query using that variable. i've been searching the forums for an answer but cant seem to find one. If i did miss it posted somewhere else please point me in the right direction. Thanx all in advance for your time.
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    To see if the variable is balnk or not you can use a number of methods e.g.

    [PHP]$query = "SELECT title FROM table WHERE 1=1 ";
    if( isset($_POST["variable1"]) && trim($_POST["variable1"]) != "" )
    {
    $query.= " AND field1='". mysql_real_esca pe_string($_POS T["variable1"])."'";
    }[/PHP]
    Checkout the related PHP functions mysql_real_esca pe_string(), isset(), trim() and empty().

    If you still face any problem, post back here with your code.

    Comment

    • WesternCanuk
      New Member
      • Nov 2007
      • 2

      #3
      ok i think i'm more confused then ever. I have read your links and just do not understand. Here is where i'm at.

      Ok this code grabs my 2 user input fields from the linked page

      [code=php]
      $colname_Record set1 = "-1";
      if (isset($_POST['roomnumber'])) {
      $colname_Record set1 = $_POST['roomnumber'];
      $colname_Record set2 = "-1";
      if (isset($_POST['buildingname'])) {
      $colname_Record set1 = $_POST['buildingname'];
      [/code]

      next i want to build my query. The below code will only check one of the fields to see if there is a match. I would like it to check both fields for a match but only if there is info entered from the linked page.

      [code=php]
      mysql_select_db ($database_dsSA IT, $dsSAIT);
      $query_Recordse t1 = sprintf("SELECT * FROM paintinfo WHERE roomnumber = %s", GetSQLValueStri ng($colname_Rec ordset1, "text"));
      [/code]

      This is where i'm lost. I need some code to check for a blank input field from the linked page and determin whether it should be included to build the query. Thanx again all.

      Comment

      Working...