How to bind parameters dynamically

PHP
1

I want to bind mysqli parameters dynamically using php. The problem I have is adapting my code to do this. I have seen a question which should be helpful to be able to solve this problem which is http://stackoverflow.com/questions/5100046/how-to-bind-mysqli-bind-param-arguments-dynamically-in-php but I dont know how to use that code to adapt it into my code. I am really struggling to understand it. It will be easier for me to see somebody adapt my code to fit the bill so then I can understand what is going on.

Can somebody help guide me what I need to do to be able to bind the parameters dynamically in my code? Do I just need to change the bind param method to a call_func_user_array or is it much more than that?

Below is my code:


    $questionquery = "SELECT q.QuestionContent FROM Question q WHERE ";
    
    $i=0;
    
    $whereArray = array();
    $orderByArray = array();
    $orderBySQL = "";
    $paramString = "";
    
    //loop through each term
    foreach ($terms as &$each) {
        $i++;
        //if only 1 term entered then perform this LIKE statement
        if ($i == 1){
            $questionquery .= "q.QuestionContent LIKE ? ";
        } else {
            //If more than 1 term then add an OR statement
            $questionquery .= "OR q.QuestionContent LIKE ? ";
            $orderBySQL .= ",";
        }
    
        $orderBySQL .= "IF(q.QuestionContent LIKE ? ,1,0)"; 
    
        $whereArray[] = "%" . $each . "%";
        $orderByArray[] = "%" . $each . "%"; 
    
        $paramString .= "ss";
    }  
    
    $questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY " . $orderBySQL; 
    $stmt=$mysqli->prepare($questionquery)or die($mysqli->error); ;  
    $stmt->bind_param($paramString, array_merge($whereArray, $orderByArray));
    	$stmt->execute();
    	$stmt->bind_result($dbQuestionContent); 
    	$questionnum = $stmt->num_rows();

Thank you and I appreciate your help :slight_smile:

2

If you print out $questionquery before you try to prepare it, you’ll see that you have some errors in your SQL.


SELECT q.QuestionContent FROM Question q WHERE q.QuestionContent LIKE ? OR q.QuestionContent LIKE ? OR q.QuestionContent LIKE ? GROUP BY q.QuestionId, q.SessionId ORDER BY IF(q.QuestionContent LIKE ? ,1,0),IF(q.QuestionContent LIKE ? ,1,0),IF(q.QuestionContent LIKE ? ,1,0)

I’d fix those first, then see what happens.

I assigned some search terms (4) to mess around with. If you can provide an example of what the $terms array may look like, and any errors you receive when you run the script, it may be easier to help.