PHP optional query building

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by andylpsx, Jul 21, 2014.

  1. andylpsx

    andylpsx New Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    3
    I am currently working on an online website which houses a database inside that has 50-75 fields. I have what I believe may be a somewhat rudimentary question. Assuming we want to query the Database how would one go about making it so it builds a query statement using the fields that are not blank for instance, let's say I have 8 fields:

    First name
    Last Name
    Phone
    City
    State
    Address
    Marital status
    Occupation

    Now let's say I had 10,000 and I want to make the following query

    First name: Joe
    Last Name:
    Phone:
    City:
    State: New York
    Address:
    Marital status:
    Occupation: Engineer

    How would I build a query statement that would select All Joe's that live New York and are Engineers? Let assume 3 people. How would I go about building that Query. I am using PHP:pDO to query my SQL. I know how to build queries and get the information over but I don't want to be that guy that rights 1000 different cases for every possible case like only First name and city are filled in or last name and state and so on and so forth. I know there is an easier way but I am not the most experienced scripter. I don't currently have any code because I am just starting on the HTML skeleton but I figured before I get to PHP I'd get some ideas. I am not asking for full code for all this only what this principle is called or how most sites do this. Is there a way I can store different parts of the query in PHP variables like

    Code:
    $s = "blah" WHERE "blah"
    and then build the entire statement using variables. I doubted this method because I thought that it would be impossible to build a query statement with variables, I may be wrong but I am pretty sure it can't be done that way.

    Any help would be much appreciated.
     
  2. descalzo

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    Are you saying that you want to have a web based form that will query the database?
    That you will have a form with 50-75 fields?
    That you want the script to use just those fields filled out?
     
  3. andylpsx

    andylpsx New Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    3
    Yes, a form that will query the database and the database has around 50 to 75 fields. We are not usually looking for one specific person but for multiple people that meet certain criteria.
     
  4. descalzo

    descalzo Grim Squeaker Community Support

    Messages:
    9,375
    Likes Received:
    327
    Trophy Points:
    83
    A rough idea --

    PHP:
    <!DOCTYPE html>
    <html>
    <head><title>Test</title>
    </head>
    <body>

    Hello. Please fill out some of the fields below<br/>


    <div>
    <form action="" method="POST">
    <input type="text" name="name" /> Name<br />
    <input type="text" name="occupation"/>Occupation<br />
    <input type="text" name="city"/>City<br />
    <input type="text" name="state"/>State<br />
    <input type="submit" />

    </form>
    </div>

    <?php

    $whereClause 
    "WHERE ";

    foreach( 
    $_POST as $k => $v ){

      if( 
    $v != "" ){

        
    $whereClause $whereClause $k "='" $v "' AND ";

      }


    }

    echo 
    "WHERE clause built from your entries: " .  $whereClause " 1 = 1";



    ?>
    </body>
    </html>
    NOTE: does not check for SQL injection, etc. Just to give you some starting ideas.
     
    • Like Like x 1
    • Dislike Dislike x 1
  5. andylpsx

    andylpsx New Member

    Messages:
    29
    Likes Received:
    1
    Trophy Points:
    3

    This work flawlessly but can you explain or send me a doc on how this works: foreach( $_POST as $k => $v ){
    I ran it and it works but I want to know why and how, if you don't mind.


    EDIT: I am having a problem when trying to query the database I get this error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''SELECT * FROM VolunDB WHERE fname='andy' AND 1 = 1'' at line 1' in /home/petrzilk/public_html/Database/testSearch.php:14 Stack trace: #0 /home/petrzilk/public_html/Database/testSearch.php(14): PDO->prepare(''SELECT * FROM ...') #1 {main} thrown in /home/petrzilk/public_html/Database/testSearch.php on line 14

    my code is:
    PHP:
    <?php

    $whereClause 
    "WHERE ";

    foreach(
    $_POST as $k => $v ){

      if( 
    $v != "" ){
        
    $whereClause $whereClause $k "='" $v "' AND ";
      }
    }
    $db = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8''petrzilk_dbAdmin''***********'); // Connecting to Database
       
    $db->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION); // Error statement
       
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARESfalse);
       
    $query $db->prepare("'SELECT * FROM VolunDB " $whereClause " 1 = 1'");
       
    $query->execute();
       
    $result $query->fetch(PDO::FETCH_OBJ);
    ?>
     
    Last edited: Aug 4, 2014
  6. Skizzerz

    Skizzerz Contributors Staff Member Contributors

    Messages:
    2,929
    Likes Received:
    117
    Trophy Points:
    63
    @descalzo's code does not check for SQL injection, which if your form is accessible from the web is an absolute necessity. Since you are using PDO, read up on prepared statements and placeholders.
     

Share This Page