Asked  7 Months ago    Answers:  5   Viewed   35 times

I always find it difficult to write MySQLi prepared statements, because many functions work differently than in the old way. Right now I am facing a problem regarding fetch_array().

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

 Answers

74

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
}
Wednesday, March 31, 2021
 
superfell
answered 7 Months ago
31

I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();

foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int); 

$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}

$result->free();
$data_res->close();
?>
Wednesday, March 31, 2021
 
hakre
answered 7 Months ago
14

First you create the statement very much like a normal statement you have made

$stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text)
SELECT c.id, ?
FROM categories AS c WHERE c.id = ?;");

Get the statement bound to the parameter 's' stands for string data and i for integer

$stmt->bind_param('si', $joke_text,$category_id);   // bind to the parameters

/* execute prepared statement */

$stmt->execute();
Saturday, May 29, 2021
 
Saxophlutist
answered 5 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share