mysqli prepared statement with while loop

Question

I am trying to do an extremely simple query using mysqli. It is driving me mad!

I just want $data to be an array of the values from the sql query.

This is my code...

$req = $app->request();
$hashtag = $req->get('hashtag');

require_once 'Slim/lib/database.php';

$db = connect_db();

$statement = $db->prepare("SELECT `content` FROM `posts` WHERE `content` LIKE ?");
$newhashtag = '%#' . $hashtag . '%';
$statement -> bind_param("s", $newhashtag);

$statement -> execute();

$statement -> bind_result($result);

while ( $row = mysqli_fetch_array($statement) ) {
    $data[] = $row;
}

print_r($data);

$statement -> close();

I just get an error mysqli_fetch_array() expects parameter 1 to be mysqli_result, null given and it doesn't make a difference using $result or $statement on the fetch_array

Answer 1

You can try this:

$statement = $db->prepare("SELECT `content` FROM `posts` WHERE `content` LIKE ?");
$newhashtag = "%#$hashtag%";
$statement->bind_param("s", $newhashtag);
$statement->execute();
$result = $statement->get_result();

while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}

This uses the get_result() function, which is used to get the result from a prepared statement.

This is initialised outside the while loop and assigned to a new variable, in this instance $result.
Then $result->fetch_assoc() is assigned to $row and can be accessed within the loop. Accessing each column as a key from the array such that $row["content"] would return the content from each row under that column

Answer 2

To get the result object from the prepared statement you can use get_result(). This object can then be iterated with a foreach loop.

$statement->execute();

$result = $statement->get_result();

foreach ($result as $row) {
    print_r($row);
}

If you need to fetch all rows into an array, you can use fetch_all().

$statement->execute();

$result = $statement->get_result();

$data = $result->fetch_all(MYSQLI_ASSOC);
print_r($data);

You can also fetch the data by binding each column to a variable. First, you specify a variable you want to be populated using bind_result() and then you call fetch() to populate that variable.

$statement->execute();

$statement->bind_result($content);

while ( $statement->fetch() ) {
    // Every time fetch is called a value from the next row will be inserted into $content
    $data[] = $content;
}

print_r($data);