SELECT statement with fetch_array in mysqli prepared statements

Viewed 86 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

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
}

Saturday, November 26, 2022

cfpfwf11-a4adb84ef5d9

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();
?>

Monday, December 12, 2022

exit_curt0y-d464e750632f

<?php
$sql = new mysqli('127.0.0.1','root','Qwert12345','plot_io_db');
//echo $sql->query('Select * From players');
?>

It will work. Just remove port from localhost (127.0.0.1)

Tuesday, August 30, 2022

noel_yap

You should use

mysqli_insert_id($link);

Because of this note on the PHP manual you referred us to

mysqli_stmt_insert_id

It should be noted that using mysqli_stmt->insert_id will not result in a unique ID being returned for each execution of a prepared insert statement. In practice, it appears that the first insertion ID is returned. If you are performing multiple inserts with the same prepared statement (one invocation of mysqli_stmt::prepare and multiple invocations of mysqli_stmt::execute() for a given statement), and need to keep the unique ID for each insert, use mysqli_connection->insert_id.

Friday, August 12, 2022

rohan-patel

mysqli_connect("","username" ,"password","databasename");//Server name cannot be NULL

use loaclhost for server name(In Loacl)

<?php
    $con = mysqli_connect("localhost","username" ,"password","databasename");

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

Or can use MySQLi Procedural

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $con = mysqli_connect($servername, $username, $password);

    // Check connection
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
?>

EDIT 01

$servername = "localhost";
$username = "root";
$password = "";

Monday, October 10, 2022

arkadiusz_nowak

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

mysqli

SELECT statement with fetch_array in mysqli prepared statements

Answers

php,mysql,mysqli