Results 1 to 2 of 2

Thread: [RESOLVED] How to make this a dynamic Prepared Statement for SELECT?

  1. #1
    Join Date
    Feb 2013

    resolved [RESOLVED] How to make this 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);
    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 ****//

    //**** Bind the results ****//

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


    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
    Join Date
    Feb 2013
    Switching gears and going with PDO instead.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center

Recent Articles