Why is no records appearing when try to search for terms?

Question

Ok this is strange but I will explain what is going on.

In SQL lets say I want to find 2 terms (AAA, AAB) in the database using the LIKE statements, the query which is able to find the results of those terms is below:

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

So I know the SQL works. So what I want to do is include this query in MYSQLi. The only difference is that the user gets to enter in their terms in a search box and then submit the search box. So the user can enter in 1 term, 2 terms 3 terms etc, it could be any number of terms.

So below is the MYSQLi code I have created to be able to do this:

        <form action="previousquestions.php" method="get">
              <p>Search: <input type="text" name="questioncontent" value="<?php echo $questioncontent; ?>" onchange="return trim(this)" /></p>
              <p><input id="searchquestion" name="searchQuestion" type="submit" value="Search" /></p>
              </form>


        <?php 

        if (isset($_GET['searchQuestion'])) {

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

        $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); 
function makeValuesReferenced(&$arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 

}

call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced(array_merge((array)$paramString,$whereArray, $orderByArray)));

    $stmt->execute();
    $stmt->bind_result($dbQuestionContent); 
    $questionnum = $stmt->num_rows();
    echo $questionquery;
    echo $paramString;

//OUTPUT RECORDS

        if (empty($questioncontent)){
    echo "Please enter in a phrase in the text box in able to search for a question";
}

        else if($questionnum ==0){
    echo "<p>Sorry, No Questions were found from this Search</p>";
    }
    else{

            $output = "";
    $output .= "
        <table border='1' id='resulttbl'>
          <tr>
          <th class='questionth'>Question</th>
          </tr>
    ";
            while ($stmt->fetch()) {
    $output .= "
          <tr>
          <td class='questiontd'>{$dbQuestionContent['QuestionContent']}</td>
          </tr>";
            }
            $output .= "        </table>";

            echo $output;

      }
}
            ?>

The problem is that if the user enters in the correct term(s), it does not display the records from the database which contains those terms. It does not output anything. This doesn't make sense because lets say that I enter in 2 terms in the search box "AAA AAB", when I echo the query and parameters, it seems correct as it outputs this:

query output:

SELECT q.QuestionContent FROM Question q WHERE 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)

parameter output:

ssss

So my question is if the query is correct and number of paramaters are correct, what is happening that causes no records appear for a successful search?

At the moment I am getting a warning which is here:

Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of elements in type definition string doesn't match number of bind variables in ...... on line 87

what needs to happen to fix this warning?

whats this, the 10th time you posted this exact code or thirteenth? I have lost count. People have given you many suggestions and you continue to ignore them. If you are not getting the answers you want, maybe you should rethink your design — Kris, Jun 28 '12 at 01:36
@Kris ??? I have asked this question only once and posted this code only for the second time, are you sure you are not mistaking me for somebody else with a similar code? — user1394925, Jun 28 '12 at 01:51
Then why does this code look almost identical to http://stackoverflow.com/questions/11147669/search-bar-is-not-working-when-using-mysqli/11148835#11148835 I guess it is under a different username but come on.. — Kris, Jun 28 '12 at 02:10
@Kris haha, I think the reason somebody has posted the same code is because there are 3 of us in a group working together on this. I think one of our group members must also have a SO account and asked a question regarding the same code but it is not me :) — user1394925, Jun 28 '12 at 02:17
Oh ok. Sorry then.. I just recognized the code being on here atleast 7 times now — Kris, Jun 28 '12 at 02:21
@Kris I will tell my group members to only let me post questions on SO so that the same code does not keep appearing loads of time :). Btw Kris, do you know how can get rid of the warning. There is an answer below abount wildcards but I think I need to get rid of the warning as well. — user1394925, Jun 28 '12 at 02:27

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

The reason you are getting this error is because you are only passing in 1 element.. an array.

$stmt->bind_param($paramString, array_merge($whereArray, $orderByArray));

What it needs to look like is this

$stmt->bind_param($paramString, $param1, $param2, $param3 ...);

There needs to be a seperate param for each s in $paramString. Now obviously this is a bit more difficult since it can be a variable amount. So replace

$stmt->bind_param($paramString, );

With

$ref = array_merge((array)$paramString, $whereArray, $orderByArray);
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($ref));

function makeValuesReferenced(&$arr){ 
    $refs = array(); 
    foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
    return $refs; 

}

I haven't tested it, so unsure if it works. I got the solution from Pass by reference problem with PHP 5.3.1

edited May 23 '17 at 12:12

Community

1
1

answered Jun 28 '12 at 02:59

Kris

6,094
2
31
46

hi, Kris, I have tested your code you politely given me, when I replace the code with the one you have given me, it gives me a fatal error stating: `Fatal error: Call to undefined function makeValuesReferenced() in ... on line 87` – user1394925 Jun 28 '12 at 03:16
yes, i did I copied the code in the last code block in your answer and put it in my code. Did you want me to put the call_user_func_array in `$stmt->bind_param($paramString, ); ` or completly replace `$stmt->bind_param($paramString, ); ` with call_user_func_array? – user1394925 Jun 28 '12 at 03:24
COmpletely replace it. Remove the whole line $stmt->bind_param – Kris Jun 28 '12 at 03:27
I dont know how it could be an undefined function when you copied the function to your code. You copied the whole function makeValuesReferenced... right? Update your question with the new code please – Kris Jun 28 '12 at 03:32
Yep, I will post updated code in the question for you to see. It is very strange – user1394925 Jun 28 '12 at 03:34
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13136/discussion-between-kris-and-user1394925) – Kris Jun 28 '12 at 03:35

score 1 · Answer 2 · answered Jun 28 '12 at 04:44

Also looking at your code you have a few logic errors.

1)

$searchquestion = $questioncontent;

Should be

$searchquestion = $_GET['questioncontent'];

2)

if (empty($questioncontent)){

Should be

if (empty($searchquestion)){

3)

<td class='questiontd'>{$dbQuestionContent['QuestionContent']}</td>

should be

<td class='questiontd'>$dbQuestionContent</td>

Why is no records appearing when try to search for terms?

2 Answers2