PHP - Error while binding variable to the Prepared Statement

Question

Trying to bind a Variable $user_id to the prepared statement, the array is sent as an jsonarray to a java file. The below code works perfectly fine but it is without the binded parameter, where the value of qual_id is static.

if ($user_id->num_rows >= 1) {
        $mysql_qry = "select * from user_qualification where qual_id='1'";
        //$stmt = mysqli_stmt_init($conn);
        //$result = mysqli_stmt_prepare($stmt, $mysql_qry);
        //mysqli_stmt_bind_param($result, "i", $user_id);

       $result = mysqli_query($conn,$mysql_qry);
        //mysqli_stmt_execute($stmt);

        $data_item = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($data_item, 
            array('u_school'=>$row['school'],
            'u_hschool'=>$row['hschool'],
            'u_undergrad'=>$row['ugrad'],
            'u_grad'=>$row['grad'],
            'u_phd'=>$row['phd'],
            )
            );
        }

Below code contains error which is with the binded parameter:

if ($user_id->num_rows >= 1) {
    $mysql_qry = "select * from user_qualification where qual_id='?'";
    $stmt = mysqli_stmt_init($conn);
    $result = mysqli_stmt_prepare($stmt, $mysql_qry);
    mysqli_stmt_bind_param($result, "s", $user_id);

    //$result = mysqli_query($conn,$mysql_qry);
    mysqli_stmt_execute($stmt);

    $data_item = array();
    while($row = mysqli_fetch_assoc($result)){
        array_push($data_item, 
        array('u_school'=>$row['school'],
        'u_hschool'=>$row['hschool'],
        'u_undergrad'=>$row['ugrad'],
        'u_grad'=>$row['grad'],
        'u_phd'=>$row['phd'],
        )
        );
    }

Also the column qual_id is bigint.

Edit 1:
removed the quotations of placeholder.
$mysql_qry = "select * from user_qualification where qual_id=?";

Edit 2 :
changed from "s" to "i".
mysqli_stmt_bind_param($result, "i", $user_id);

Edit 3 :
var_dump of variable $user_id after the if statement
object(mysqli_result)#3 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) } []

Edit 4 :
php file

<?php
require "conn.php";
$user_name ="omx123"; //$_POST["user_name"];
if ($mysql_qry = $conn->prepare("Select id from UserLoginDetails where username=?")) {
    $mysql_qry->bind_param("s", $user_name);
    $mysql_qry->execute();
    $user_id = $mysql_qry->get_result();

    if ($user_id->num_rows >= 1) {
        var_dump($user_id);
        $mysql_qry = "select * from user_qualification where qual_id=?";
        $stmt = mysqli_stmt_init($conn);
        $result = mysqli_stmt_prepare($stmt, $mysql_qry);
        mysqli_stmt_bind_param($result, "i", $user_id);

        //$result = mysqli_query($conn,$mysql_qry);
        mysqli_stmt_execute($stmt);

        $data_item = array();
        while($row = mysqli_fetch_assoc($result)){
            array_push($data_item, 
            array('u_school'=>$row['school'],
            'u_hschool'=>$row['hschool'],
            'u_undergrad'=>$row['ugrad'],
            'u_grad'=>$row['grad'],
            'u_phd'=>$row['phd'],
            )
            );
        }
        echo json_encode($data_item);
    }
} 
$conn->close();
?>

Edit 5:
Thank you for your help user : dWinder
var_dump on $user_id :
int(1) []
But the array is still empty.

Edit 6:
Works perfectly fine now :D

$mysql_qry = "select * from user_qualification where qual_id=?";
         $stmt = mysqli_stmt_init($conn);
         mysqli_stmt_prepare($stmt, $mysql_qry);
         mysqli_stmt_bind_param($stmt, "i", $user_id_int);
         mysqli_stmt_execute($stmt);
         $result=mysqli_stmt_get_result($stmt);

I guess it is because you are binding int param as string, try to change it to mysqli_stmt_bind_param($result, "i", $user_id); — Andrii Filenko
– Andrii Filenko, Commented Feb 19, 2019 at 14:16
To explain, anything inside of quotes would be considered a string. So by quoting the question mark, it becomes a string containing a question mark and not a placeholder. — aynber
– aynber, Commented Feb 19, 2019 at 14:20
Removed the quotations of placeholder and changed the "s" to "i" in mysqli_stmt_bind_param but it still doesn't work. — Om panchal
– Om panchal, Commented Feb 19, 2019 at 14:25

dWinder · Accepted Answer · 2019-02-19 16:01:16Z

2

After you share the dump of the $user_id it seem it is mysql result. In order to extract you ID from there you should use mysqli_fetch_assoc.

if ($user_id->num_rows >= 1) {
    $row = mysqli_fetch_assoc($user_id)
    $user_id_int = $row["id"]; // or what ever you used to call it

    // now you can call the bind...
    $mysql_qry = "select * from user_qualification where qual_id=?";
    $stmt = mysqli_stmt_init($conn);
    $result = mysqli_stmt_prepare($stmt, $mysql_qry);
    mysqli_stmt_bind_param($result, "i", $user_id_int);

I assume you use query as: "SELECT id From ..." as when you get the $user_id

edited Feb 19, 2019 at 16:01

answered Feb 19, 2019 at 14:47

dWinder

11.7k3 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Om panchal Over a year ago

Thank you! this solved it, and I'm getting the value in var_dump as the $user_id value but the array is shown empty

dWinder Over a year ago

@Ompanchal Glad to help! If this post helped you consider marking it as accepted (the grey "v" at the left of the post) so other may use it

ADyson Over a year ago

typo: mysqli_stmt_bind_param($result, "i", $user_id); should be mysqli_stmt_bind_param($result, "i", $user_id_int); to make use of the new value. otherwise, good answer :-)

Collectives™ on Stack Overflow

PHP - Error while binding variable to the Prepared Statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related