[RESOLVED] How to make a dynamic Prepared Statement for SELECT?
Results 1 to 3 of 3

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

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    69

    resolved [RESOLVED] 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
    Senior Member
    Join Date
    Mar 2009
    Location
    Canada
    Posts
    794
    This user-contributed comment in the PHP manual addresses dynamic variables when using prepared statements. It's not exact but I think you can utilize it to your needs.
    Prison of Mirrors
    Declare variables, not war.

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    69
    Thanks for the reply. Much appreciated.

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
  •