Select Statement with Fetch_Array in MySQLi Prepared Statements

SELECT statement with fetch_array in mysqli prepared statements

you are trying to fetch the results by

$result = $stmt->execute();

which is not the case. as execute will return you only a boolean value.

do it like.

$stmt = $db->prepare("SELECT category_id FROM post_items Where user_id = ?");
$stmt->bind_param('i', $userid);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
        //result is in row
}

PHP Prepared Statements. $stmt, fetch_array()

1: No.

3: Fetching arrays is usually done by while($qRow = $randomQQuery->fetch_array()) { }. I'd go with $row = $randomQQuery->fetch_row();.

mysqli_fetch_array(), prepared statement, and LIKE statement

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

MySQLi - Fetch Array and Prepared Statement

Read this below link:-

Mysqli abstraction, fetching arrays from prepared statements

The call_user_func_array(...) function just calls the bindParam or bind_result methods on the $query object with the given array, as if you had provided each element of the array as a method argument.

You may want to check the SQL statement you are having the problem with, with the code below. I've rewritten it a bit in order to make it fully testable, since the original code depends on the statement class in your abstraction layer.

fetch_array with prepared statement? PHP MYSQL?

You don't need fetch_array in this case.

If you want to use get the data from the query, you need to use bind_result and fetch after calling execute.

Mysqli query with bind params using fetch_array

Returning an associative array from a prepared statement you can follow up the procedure like this as follows.

<?php
$category = $_POST['category'];
$sql = "select id, name from items where category = ?";
$stmt = $connection->prepare($sql);
$stmt->bind_param('s', $category);
if($stmt->execute())
{
$result = $stmt->get_result();
$a  = $result->fetch_array(MYSQLI_ASSOC); // this does work :)
}
else
{
    error_log ("Didn't work");
}
?>

You can use while loop for printing up the value over from the associative array.

while($a = $result->fetch_array(MYSQLI_ASSOC))
{
    echo 'Id: '. $a['id'];
    echo '<br>';
    echo 'Name: '.$a['name'];
}

Output:

Id: 1

Name: Example

---

Related Topics