how to bind parameters correctly in example below in mysqli?

Posted by user1421767 on Stack Overflow See other posts from Stack Overflow or by user1421767
Published on 2012-06-22T07:46:59Z Indexed on 2012/06/22 9:16 UTC
Read the original article Hit count: 179

Filed under:
|

In old mysql code, I had a query below which worked perfectly which is below:

$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';

$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);

$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType, 
  FROM Answer an 
  INNER JOIN Question q ON q.AnswerId = an.AnswerId
  JOIN Reply r ON q.ReplyId = r.ReplyId 
  JOIN Option_Table o ON q.OptionId = o.OptionId 

                 WHERE ";

    foreach ($terms as $each) {     
        $i++;         

        if ($i == 1){         
            $questionquery .= "q.QuestionContent LIKE `%$each%` ";     
            } else {         
                $questionquery .= "OR q.QuestionContent LIKE `%$each%` ";    
                 } 
                 }  

                 $questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {     
                     $i++;      

        if ($i != 1)         
        $questionquery .= "+";     
        $questionquery .= "IF(q.QuestionContent LIKE `%$each%` ,1,0)"; 
        } 

        $questionquery .= " DESC ";

But since that old mysql is fading away that people are saying to use PDO or mysqli (Can't use PDO because of version of php I have currently got), I tried changing my code to mysqli, but this is giving me problems. In the code below I have left out the bind_params command, my question is that how do I bind the parameters in the query below? It needs to be able to bind multiple $each because the user is able to type in multiple terms, and each $each is classed as a term.

Below is current mysqli code on the same query:

     $questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';

        $searchquestion = $questioncontent;
        $terms = explode(" ", $searchquestion);

        $questionquery = "
        SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType, 
          FROM Answer an 
          INNER JOIN Question q ON q.AnswerId = an.AnswerId
          JOIN Reply r ON q.ReplyId = r.ReplyId 
          JOIN Option_Table o ON q.OptionId = o.OptionId 

                         WHERE ";

    foreach ($terms as $each) {     
                $i++;         

                if ($i == 1){         
  $questionquery .= "q.QuestionContent LIKE ? ";     
                    } else {         
  $questionquery .= "OR q.QuestionContent LIKE ? ";    
                         } 
                         }  

 $questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {     
                             $i++;      

                if ($i != 1)         
                $questionquery .= "+";     
                $questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)"; 
                } 

                $questionquery .= " DESC ";



            $stmt=$mysqli->prepare($questionquery);      
            $stmt->execute();
            $stmt->bind_result($dbQuestionId,$dbQuestionContent,$dbOptionType,$dbAnswer,$dbReplyType); 
            $questionnum = $stmt->num_rows();

© Stack Overflow or respective owner

Related posts about php

Related posts about mysqli