Mysqli_fetch_array (), prepared statement and LIKE statement

Question

I am trying to use prepared mysqli commands with querying a LIKE statement and wildcard statements. After debugging is sprinkled with echo expressions all over the code, I see that the while while statement is not executed. You see what I'm doing wrong here?

This is my first question on this forum, so I apologize if this is not a good question; I spent 6 hours trying to get the prepared section of the instructions of my code to work, and I can not find threads that affect my question that do not go completely over my head (for example, How can I put the results of a prepared MySQLi statement in an associative array? ). The two closest I found were:

Using wildcards in a prepared statement is MySQLi and Combine prepared PHP statuses with LIKE .

Here is the relevant snippet of my code:

//set up and execute queries $titleQuery = "SELECT keyframeurl, videoid, title, creationyear, sound, color, duration, genre FROM openvideo WHERE title LIKE CONCAT ('%', ?, '%') ORDER BY $order"; if($stmt = mysqli_prepare($db, $titleQuery)){ //bind parameters mysqli_stmt_bind_param($stmt, 's', $trimmedTitleSearch); //execute query mysqli_stmt_execute($stmt); //bind results mysqli_stmt_bind_result($stmt, $keyframeurl, $videoid, $title, $year, $sound, $color, $duration, $genre); //store result so num rows can be counted $result = mysqli_stmt_store_result($stmt); //fetch results while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) { echo "<tr>"; echo "<td><a href=\"".$row['keyframeurl']."\">".$row['videoid']."</a></td>"; echo "<td>" . $row['title'] . "</td>"; echo "<td>" . $row['year'] . "</td>"; echo "<td>" . $row['sound'] . "</td>"; echo "<td>" . $row['color'] . "</td>"; echo "<td>" . $row['duration'] . "</td>"; echo "<td>" . $row['genre'] . "</td>"; echo "</tr>"; } } else { // Error printf("Prepared Statement Error: %s\n", $db->error); } 

Thanks for any advice!

Answer 1

You mix 2 styles of getting results. Either use the ugly bind_result method (and then get your data with fetch() ), or try using get_result() - so you can use fetch_array() (not guaranteed).

In any case, just get rid of all this mess and use PDO.

 $titleQuery = "SELECT keyframeurl, videoid, title, creationyear, sound, color, duration, genre FROM openvideo WHERE title LIKE CONCAT ('%', ?, '%') ORDER BY $order"; $stmt = $pdo->prepare($titleQuery); $stmt->execute(array($trimmedTitleSearch)); $data = $stmt->fetchAll(); foreach ($data as $row ) { // the rest is the same as yours 

I hope you correctly deactivated your $ order variable. It would be best to add it via placeholder, so you need a library that allows this SafeMysql , for example:

 $sql = "SELECT * FROM openvideo WHERE title LIKE CONCAT ?s ORDER BY ?n"; $data = $db->getAll($sql,"%$trimmedTitleSearch%", $order); foreach ($data as $row ) { // the rest is the same as yours 

Pay attention to the amount of code and compare with loading the raw API calls you are currently using.

Answer 2

@Your Common Sense. You see that allenbell_nc does not need "... just get rid of all this mess and use PDO," as you suggested. Just because you got the wrong idea about the mysqli extension with prepared statements, this does not mean that others should do away with this at the slightest hint of trouble, rather than performing deep, but painful research. After all, this is what stackoverflow is, isn't it? well researched answers ..

@allenbell_nc - To answer your question, I don’t think your problem is with the use of Wildcards and other things. The problem is your line of code where you are trying to use mysqli_fetch_array() . This is most likely to cause an error complaining about parameter 1 ($ result), since mysqli_stmt_store_result() used when you want to later find the number of rows returned from the query, so it returns a boolean value (true or false) and NOT a set of results.

INSTEAD, use mysqli_stmt_bind_result() after calling the call, then call mysqli_stmt_fetch() in the while state before finally using array_push() in the while condition tag, which helps you save and subsequently display the contents of your ASSOCIATIVE array.

QUICK EXAMPLE (Idea presented by Mr. Carson MacDonald @ [http://www.ioncannon.net/programming/889/php-mysqli-and-multiple-prepared-statements/] [1]):

...
$ comments = array ();

  $comment_stmt->bind_param('i', $post_id); if ($comment_stmt->execute()) { $comment_stmt->bind_result($user_id); while ($comment_stmt->fetch()) { array_push($comments, array('user_id' => $user_id)); //Now you can go ahead and make use of $comments however you want, whether as stored in an $_SESSION array variable or just to echo it out! As Demonstrated Below: $_SESSION = $comments; echo $_SESSION['user_id']; } } 

...

I hope this helps, good luck, I asked for the first time, as this is also my first answer - to your project.

Answer 3

  echo "<td>" . $row['color'] . "</td>"; echo "<td>" . $row['duration'] . "</td>"; echo "<td>" . $row['genre'] . "</td>"; echo "</tr>"; while ($row = mysqli_fetch_array($stmt, MYSQL_ASSOC)) 

Or while ($row = mysqli_stmt_fetch($stmt))

Edit:

  mysqli_stmt_bind_result($stmt, $keyframeurl, $videoid, $title, $year, $sound, $color, $duration, $genre); //store result so num rows can be counted $result = mysqli_stmt_store_result($stmt); //fetch results while (mysqli_stmt_fetch($stmt)) { echo "<tr>"; echo "<td><a href=\"".$keyframeurl."\">".$videoid."</a></td>"; echo "<td>" . $title . "</td>"; echo "<td>" . $year . "</td>"; echo "<td>" . $sound . "</td>"; echo "<td>" . $color . "</td>"; echo "<td>" . $duration . "</td>"; echo "<td>" . $genre. "</td>"; echo "</tr>"; }