mysqli_fetch_array(), prepared statement, and LIKE statement

Question

I'm trying to use mysqli prepared statements with a LIKE statement query and wildcard operators. After debugging be sprinkling echo statements throughout the code, I can see that my while statement is not executing. Can you see what I'm doing wrong here?

This is my first time asking on this forum, so I apologize if this isn't a good question; I've spent 6 hours trying to get the prepared statement section of my code to work and I can't find any threads addressing my question that don't go completely over my head (e.g. How can I put the results of a MySQLi prepared statement into an associative array?). The two closest I found were:

Using wildcards in prepared statement - MySQLi and Combine PHP prepared statments with LIKE.

Here's the relevant excerpt 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!

I was using MySQLi before but turning it into arrays was quite a challenge. If you are just starting the program I would suggest learning PDO as it makes arrays and retrieving information SO much easier than MySQLi. MySQLi_STMT is just.... Incomplete it feels like it was abandoned half-way though. — Rujikin, Apr 06 '13 at 03:15
"I can see that my while statement is not executing" -- any error messages? — sectus, Apr 06 '13 at 03:27
yes, it isn't a good question. Just *watching* the code is not the way to go. Code have to be *run* instead. And you should split your goals, doing not all at once. Sort things out with prepared statements first and then go for LIKE or whatever else particular query. And yes - if you don't want to waste your time, [**move to PDO**](http://stackoverflow.com/tags/pdo/info) until not too late — Your Common Sense, Apr 06 '13 at 03:39
You are binding the result set, so you can set your `while` loop to use `mysqli_stmt_fetch($stmt)` and then use the variables the you bound for the results. Though, as everyone else said, get out now and use PDO ^^ — Jon, Apr 06 '13 at 03:40

Your Common Sense · Accepted Answer · 2013-04-06T04:08:33.960

You are mixing 2 styles of fetching results. Either use ugly bind_result way (and get your data using fetch() then), or try to use get_result() - so, you'll be able to use fetch_array() (not guaranteed though).

Anyway, just get rid of all that 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 properly sanitized your $order variable. The best way would be apparently to add it via placeholder, so, you will need a library that allows it, 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

Note the amount of code and compare with that load of raw API calls you are using at the moment

`get_result` is picky where it likes to work. Thank you for providing an actually good answer compared to the others here. — Jon, Apr 06 '13 at 04:08

score 0 · Answer 2 · answered Apr 15 '13 at 08:48

@Your Common Sense - You see there is no need for allenbell_nc to "... just get rid of all that mess and use PDO" like you inferred. Just because you've got an erroneous impression of the mysqli extension with prepared statements does not mean that others should do away with it at the slightest hint of trouble as opposed to carrying out indepth but painful research. After all, this is what stackoverflow is about, isn't it? well researched answers..

@allenbell_nc - To answer your question, I do not think your problem has to do with your use of Wild cards and stuff. The problem lies in your line of code where you attempt to make use of mysqli_fetch_array(). This will very likely throw an error complaining about parameter 1 ($result), because mysqli_stmt_store_result() is used in cases where you want to later find the number of rows returned from a query, so it therefore returns boolean(true or false) and NOT a result set.

INSTEAD, use mysqli_stmt_bind_result() after the execute call then call mysqli_stmt_fetch()in the while condition, before finally using array_push() in the while condition body which will help you to store and subsequently echo out the contents of your ASSOCIATIVE array.

A QUICK EXAMPLE(Idea provided by Mr. Carson McDonald @ [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'];
    }
  }

...

Hope that helps, goodluck to you - a first time asker, as this is also my first time answer - on your project.

this is just unusable approach. its good for silly example only but unusable for the real life application. what if you need not single $user_id but a row of 10 columns? what if you're creating an abstraction method and **don't know which values were returned at all?** — Your Common Sense, Apr 15 '13 at 09:23
the same thing with binding arbitrary number of variables into query. mysqli is not "erroneous". it's just unusable — Your Common Sense, Apr 15 '13 at 09:35

Amir · Answer 3 · 2013-04-06T04:14:50.803

            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>";   
        }

[`mysqli_stmt_fetch`](http://www.php.net/manual/en/mysqli-stmt.fetch.php) doesn't have a return value as is based off bound parameters. Please read the link. — Jon, Apr 06 '13 at 04:06

mysqli_fetch_array(), prepared statement, and LIKE statement

3 Answers3

Linked