#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question How to make a dynamic Prepared Statement for SELECT?


    Hi all,

    How would one make this "hard coded" prepared statement for select, into a dynamic one that may or may not have some of the variables present?

    PHP Code:
    //$connection made to db

    $hardCodedSTMT "SELECT Col1, Col2, Col3, Col4, Col5 FROM TABLE1 WHERE Col1 = ? AND Col2 = ? AND Col3 = ? AND Col4 = ? AND Col5 = ?";
    $hcSTMT mysqli_prepare($connection$hardCodedSTMT);
    mysqli_stmt_bind_param($hcSTMT"isiis"$var1$var2$var3$var4$var5); 
    mysqli_stmt_execute($hcSTMT);
    //etc. 
    How to make that dynamic in the sense that $var2 thru $var5 may be null?

    This is what I was thinking, but obviously there's a problem with the $variables and how to get them into a string (I think) instead of pointing to their actual values.
    PHP Code:
    $buildSTMT "SELECT Col1, Col2, Col3, Col4, Col5 FROM TABLE1 WHERE Col1 = ?"//$var1 will not be null.
    $parmTypes "i";
    $parmVariables "$var1";  //should this be an array instead of string, or var by reference or?  I dont want the value of $var1, but litterally the variable identified as $var1

    if ($var2 != "") {  //possible null
        
    $buildSTMT .= "AND Col2 = ?";
        
    $parmTypes .= "s"
        
    $parmVariables .= "$var2//how to add the variable and not it's value??
    }
    if (
    $var3 != "") { //possible null
        
    $buildSTMT .= "AND Col3 = ?";
        
    $parmTypes .= "i"
        
    $parmVariables .= "$var3//how to add the variable and not it's value??
    }
    if (
    $var4 != "") { //possible null
        
    $buildSTMT .= "AND Col4 = ?";
        
    $parmTypes .= "i";
        
    $parmVariables .= "$var4//how to add the variable and not it's value??
    }
    if (
    $var5 != "") { //possible null
        
    $buildSTMT .= "AND Col5 = ?";
        
    $parmTypes .= "s"
        
    $parmVariables .= "$var5//how to add the variable and not it's value??
    }


    //**** Prepare the full query statement. ****//    
    $stmt mysqli_prepare($connection$buildSTMT);

    //**** Bind the parameters to the statement ****//
    mysqli_stmt_bind_param($stmt$parmTypes$parmVariables); //$parmVariables needs to be $var1, $var2, $var3, $var4, $var5 (ex. if all 5 present).

    //**** Run the statement ****//
    mysqli_stmt_execute($stmt);

    //**** Bind the results ****//
    mysqli_stmt_bind_result($stmt$resultCol1$resultCol2$resultCol3$resultCol4$resultCol5);

    while (
    mysqli_stmt_fetch($stmt)) {
        
    //do something with results
    }

    mysqli_stmt_close($stmt);

    mysqli_close($connection); 
    I plan to make all those if statements and assignments in them, into a function. Also, will convert to OO later. As of now, all other code is still in procedural format.

    For now, I'm wondering how to get the "mixed" vars (& not their values) into the last part of mysqli_stmt_bind_param ?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    if you're bound to MySQLi with no chance to switch to the PDO extension, you gotta use a workaround: Collect the parameters in an array, and then call mysqli_stmt_bind_param() via call_user_func(), passing all arguments with an array.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Thanks for the reply.

    If I switch to PDO:

    1. Would I still need to do the workaround?
    and
    2. Are there other benefits to PDO, over mysqli?
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    PDO allows you to pass an array of values to the execute method and additionally allows for the use of named parameters, which will make your query far easier to read. For example:
    PHP Code:
    $sql "SELECT * FROM table1 WHERE id = :id";
    $st $db->prepare($sql);
    $st->execute([
        
    ':id' => 1,
    ]); 
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by we5inelgr
    2. Are there other benefits to PDO, over mysqli?
    Yes. First of all, PDO is not bound to MySQL. So if you ever decide to give up this toy DBMS and switch to a real system like PostgreSQL, you can do that without having to rewrite all your database code. PDO works with all database systems (unless you use special MySQL features in your queries, of course).

    Apart from that, PDO is generally more user-friendly. For example, prepared statements can have named parameters. And you can simply use foreach to loop through the result set of a prepared statement. You don't need this weird bind_result() + fetch() stuff.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Thanks for the reply. Much appreciated.

    Comments on this post

    • Jacques1 disagrees : Please don't close your threads. Often times, other users have additional comments and remarks. They can post them when you lock the topic.

IMN logo majestic logo threadwatch logo seochat tools logo