Replace get_result() with bind_result()

PHP
1

I am trying to replace a get_result() query with bind_result() as my host won’t allow mysqlnd.

I have looked at a bunch of questions and answers here but haven’t been able to get any of them to work for me.

Here is what I was running on my local host. (I am using Slim Framework)

in DBHandler.php

    /**
 * Fetching all user contacts
 * @param String $user_id id of the user
 */


public function getAllUsercontacts($user_id) {
    $stmt = $this->conn->prepare("SELECT t.* FROM contacts t, user_contacts ut WHERE t.id = ut.contact_id AND ut.user_id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $contacts = $stmt->get_result();
    $stmt->close();
    return $contacts;
}

in index.php


/**
 * Listing all contacts of particual user
 * method GET
 * url /contacts          
 */
$app->get('/contacts', 'authenticate', function() {
        global $user_id;
        $response = array();
        $db = new DbHandler();

        // fetching all user contacts
        $result = $db->getAllUsercontacts($user_id);

        $response["error"] = false;
        $response["contacts"] = array();

        // looping through result and preparing contacts array
       while ($contact = $result->fetch_assoc()) {
            $tmp = array();
            $tmp["id"] = $contact["id"];
            $tmp["contactname"] = $contact["contactname"];
            $tmp["contactemail"] = $contact["contactemail"];
            $tmp["contactaddress"] = $contact["contactaddress"];
            $tmp["contactnotes"] = $contact["contactnotes"];
            $tmp["createdAt"] = $contact["created_at"];
            array_push($response["contacts"], $tmp);
        }

        echoRespnse(200, $response);
    });

This all runs great on my PC in XAMPP, but not on my shared server. I know in bind_result() you need the field names and they are the following,

id

created_at

contactname

contactemail

contactaddress

contactnotes

I have tried a few things that I found but none of them worked for me

Thanks in advance for any help!

2

fetch_assoc and fetch_array won’t work on stmt lines as those pertain to ->query() or _query(). You have to use fetch() and you’ll have to also append appropriate variables to bind_result based on the column and you can then reference those variables within the while loop.

3

Currently, the code I posted works great on my PC.
I am just trying to get the same result without using $stmt->get_result(); as my shared host doesn’t allow mysqlnd.

I have tried changing the database function to this

public function getAllUsercontacts($user_id) {
        $stmt = $this->conn->prepare("SELECT id, contactname, contactemail, contactaddress, contactnotes, created_at FROM company WHERE user_id = ?");
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        /* Store the result (to get properties) */
        $stmt->store_result();

        /* Get the number of rows */
        $num_of_rows = $stmt->num_rows;

        /* Bind the result to variables */
        $contacts = $stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);
    return $coontacts;
        /* free results */
        $stmt->free_result();
}

But when I run that all I get is this error “Fatal error: Uncaught Error: Call to a member function bind_param() on boolean”
and it says the error is in line 284 which is this line in the code above “$stmt->bind_param(“i”, $user_id);”

Am I doing something obviously wrong?

4

Nothing I see so far is wrong. Look for typos. Make sure that both the columns you are specifying and your database columns do match up. Make sure that the table is spelled correctly. One mis-spelled letter such as mkl instead of mlk can cause this error. Then make sure that all those columns do actually exist in your database. Specifying a column that doesn’t exist may also cause the problem. Also, make sure that there is at least 1 data to test. If the table is empty, this also could be the problem. I don’t believe it would, but it could.

5

I think it may be a binary vs. decimal issue (which hopefully type casting can resolve).

http://php.net/manual/en/mysqlnd.incompatibilities.php

1 Like
6

Well, I am at a loss to know what to do :expressionless:, I can send anyone who wants it the complete code with a SQL export of my database.

I am fairly good at PHP, not a pro by any means though lol, so I am completely lost as to what this means or how to apply it.[quote=“Mittineague, post:5, topic:253263”]
I think it may be a binary vs. decimal issue (which hopefully type casting can resolve).
[/quote]

Thanks for all your help so far guys,
I’m pretty sure 101% percent of my errors right now are in between the keyboard and the back of my chair.

7

I think that means that it’s failed to run the prepare() statement for some reason, so $stmt now contains a Boolean false, so you cannot run bind_param() against it.

I found a note elsewhere that might help narrow it down:

In his case one of the column names was mis-spelled.

1 Like
8

@droopsnoot I added mysqli_report in :slight_smile:

Helped me find something that should have jumped out in my face lol.

so I corrected my database function (I hope)

    /**
     * Fetching all user contacts
     * @param String $user_id id of the user
     */
    
    public function getAllUsercontacts($user_id) {
        $stmt = $this->conn->prepare("SELECT t.id, t.contactname, t.contactemail, t.contactaddress, t.contactnotes, t.created_at FROM contacts t, user_contacts WHERE user_id = ?");
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        /* Store the result (to get properties) */
        $stmt->store_result();

        /* Get the number of rows */
        $num_of_rows = $stmt->num_rows;

        /* Bind the result to variables */
        $contacts = $stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);
    return $contacts;
        /* free results */
        $stmt->free_result();
}

That got rid of this error “Fatal error: Uncaught Error: Call to a member function bind_param() on boolean”
But now I get this error “Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean”

Which is referring to the code in my index.php

/**
 * Listing all contacts of particual user
 * method GET
 * url /contacts          
 */

$app->get('/contacts', 'authenticate', function() {
            global $user_id;
            $response = array();
            $db = new DbHandler();

            // fetching all user contacts
            $result = $db->getAllUsercontacts($user_id);

            $response["error"] = false;
            $response["contacts"] = array();

            // looping through result and preparing contacts array
           while ($contact = $result->fetch_assoc()) {
                $tmp = array();
                $tmp["id"] = $contact["id"];
                $tmp["contactname"] = $contact["contactname"];
                $tmp["contactemail"] = $contact["contactemail"];
                $tmp["contactaddress"] = $contact["contactaddress"];
                $tmp["contactnotes"] = $contact["contactnotes"];
                $tmp["createdAt"] = $contact["created_at"];
                array_push($response["contacts"], $tmp);
            }

            echoRespnse(200, $response);
        });

Is what @spaceshiptrooper said happening?

9

Yes, but you should be using the while loop when you’re trying to pull data. You shouldn’t use the while loop after. You might get an error even if you correct the different mysqli_* syntax. I still also don’t get what you are doing in your SQL statement. It doesn’t even look correct here

FROM contacts t, user_contacts WHERE user_id = ?

If you aren’t grouping any other tables to this SQL statement, I recommend just keeping the columns and tables as their respective names like the below.

SELECT id, contactname, contactemail, contactaddress, contactnotes, created_at FROM contacts WHERE user_id = ?

I don’t use frameworks because I write my own stuff, but I would suggest doing something like this.

index.php

$app->get('/contacts', 'authenticate', function() {

    global $user_id;
    $response = array();
    $db = new DbHandler();

    // fetching all user contacts
    $result = $this->getAllUsercontacts($user_id);

    // Check to see if the final result returns false
    if($result == false) {

        // 'false' was returned from the results
        // Return an error of 'true'?
        // I don't use slim so correct these statements if necessary
        $response['error'] = true;

        echoRespnse(404, $response); // echo the response of 404?

    } else {

        $response['error'] = false;
        $response['contacts'] = array();

        print('<pre>'); // Use pre for prettying the array outputs
        var_dump($result); // Dump the result from $result. If you see something that doesn't belong there, correct it.
        print('</pre>'); // Use pre for prettying the array outputs

        // Only need to add the results from $result
        // You don't need to create another new array for the 2nd argument because
        // the returned result from $result is already in an array and should already contain data
        array_push($response['contacts'], $result);

        print('<pre>'); // Use pre for prettying the array outputs
        var_dump($response); // Just dump the result again to make sure that our results get added to the $response variable
        print('</pre>'); // Use pre for prettying the array outputs

        echoRespnse(200, $response); // echo the response of 200

    }

}

db

/**
 * Fetching all user contacts
 * @param String $user_id id of the user
 */
    
public function getAllUsercontacts($user_id) {

    $sql = "SELECT id, contactname, contactemail, contactaddress, contactnotes, created_at FROM contacts WHERE user_id = ?"; // SQL Statement
    $stmt = $this->conn->prepare($sql); // Prepare the SQL Statement
    $stmt->bind_param('i', $user_id); // Bind the placeholder with the correct data type from the SQL Statement
    $stmt->execute(); // Execute the prepared statement
    $stmt->store_result(); // Store the prepared statement for later checking

    // Check to make sure if any data is returned
    if($stmt->num_rows) {

        // Create and append variables to the appropriate columns
        $stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);

        // Create a while loop
        while($stmt->fetch()) {

            // Create an array with keys and reference the variables we just created in bind_result()
            // This is an alternative way to get_result()
            $contacts = array(
                'id' => $id,
                'contactname' => $contactname,
                'contactemail' => $contactemail,
                'contactaddress' => $contactaddress,
                'contactnotes' => $contactnotes,
                'created_at' => $created_at
            );

            return $contacts; // Return the $contacts array

        }

    } else {

        return false; // Return false because the data could not be found.

    }

}
1 Like
10

This won’t work:

while ($contact = $result->fetch_assoc()) {

because in that code, $result is the result of these two lines:

$contacts = $stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);
return $contacts;

and $contacts is a Boolean value, hence you can’t call fetch_assoc() on it. You should return $stmt instead, if you want to go on with the code you have. But I think you need to use fetch() because, as you’ve bound the results to a series of variables, the concept of whether you return the results in an indexed or associative array is probably not relevant. I also don’t know how your code will deal with the fact that you’ve bound the results to a set of variables that don’t exist in the calling code, only inside the function.

I don’t know mysqli though, so the above is guesswork mainly, and mainly about why you get that error message. The post above is probably way more useful at actually fixing the problem.

2 Likes
11

A Life Pro Tip: replace mysqli with PDO

public function getAllUsercontacts($user_id) {
    $stmt = $this->conn->prepare("SELECT t.* FROM contacts t, user_contacts ut WHERE t.id = ut.contact_id AND ut.user_id = ?");
    $stmt->execute([ $user_id]);
    return $stmt->fetchAll();
}

And then in your code

$db = new DbHandler();
$response["error"] = false;
$response["contacts"] = $db->getAllUsercontacts($user_id);
echoRespnse(200, $response);

Now compare this microscopic amount of code with that struggle you all having for several days with mysqli

2 Likes
12

That code tells me this
Message: mysqli_stmt::execute() expects exactly 0 parameters, 1 given

13

That’s still the wrong syntax for PDO. You still have to bind the place holders together which you failed to demonstrate in that “1 line” of code. Simplicity only gets you so far. What happens when the data doesn’t exist? OP would still have to not only switch over to a PDO connection, but correct their SQL statement which I believe is causing the problem. When I did it on my end using the snippet I posted, no errors, no white page, no failed attempts. Works exactly how it’s intended, but whatever the OP is doing to the snippet I posted, it’s not on me. Everything up to this point has worked great for me and I am seeing results.

Your code is only mutual exclusive.

1 Like
14

@spaceshiptrooper

Your code works awesome!, I am just having to tweak the results a bit.
I am grouping from multiple tables, and my SQL statement was not correct.
it should have been this,

SELECT t.id, t.contactname, t.contactemail, t.contactaddress, t.contactnotes, t.created_at FROM contacts t, user_contacts ut WHERE t.id = ut.contact_id AND ut.user_id = ?

Anyways, Thanks so much for all your help with this!

1 Like
15

@spaceshiptrooper

I am not sure if it is just me but it seems like the loop is not running right.
On my old code I could get mutiple results like this

{
“error”: false,
“contacts”: [
{
“id”: 3,
“contactname”: “test worked”,
“contactemail”: “test@email.worked”,
“contactaddress”: “123 xzy Ave, Letter Town Worked Country”,
“contactnotes”: “test worked”,
“createdAt”: “2017-02-07 15:31:23”
},
{
“id”: 4,
“contactname”: “test”,
“contactemail”: “test@email.com”,
“contactaddress”: “123 xzy Ave, Letter Town”,
“contactnotes”: “haha”,
“createdAt”: “2017-02-07 16:31:19”
}
]
}

But with the code you made I only get one result like this,
{
“error”: false,
“contacts”: [
{
“id”: 3,
“contactname”: “test worked”,
“contactemail”: “test@email.worked”,
“contactaddress”: “123 xzy Ave, Letter Town Worked Country”,
“contactnotes”: “test worked”,
“createdAt”: “2017-02-07 15:31:23”
}
]
}

Is there something obvious that I am missing to make it return multiple results?

16

There’s a slight logical error to the code :slight_smile:

Replace this:

// Create and append variables to the appropriate columns
$stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);

// Create a while loop
while($stmt->fetch()) {

    // Create an array with keys and reference the variables we just created in bind_result()
    // This is an alternative way to get_result()
    $contacts = array(
        'id' => $id,
        'contactname' => $contactname,
        'contactemail' => $contactemail,
        'contactaddress' => $contactaddress,
        'contactnotes' => $contactnotes,
        'created_at' => $created_at
    );

    return $contacts; // Return the $contacts array
}

with this:

// Create and append variables to the appropriate columns
$stmt->bind_result($id, $contactname, $contactemail, $contactaddress, $contactnotes, $created_at);

$contacts = array();

// Create a while loop
while($stmt->fetch()) {

    // Create an array with keys and reference the variables we just created in bind_result()
    // This is an alternative way to get_result()
    $contacts[] = array(
        'id' => $id,
        'contactname' => $contactname,
        'contactemail' => $contactemail,
        'contactaddress' => $contactaddress,
        'contactnotes' => $contactnotes,
        'created_at' => $created_at
    );
}
return $contacts; // Return the $contacts array

The first code unfortunately returns only one row of data. Inside the fetch() function, it fetches the first row of data, creates the array and returns it immediately before the function actually completes fetching all data.

In the lower code, I’ve created the contacts array outside the while loop, filled it with all the fetched data during the fetch() function and returned it on loop completion.

2 Likes
17

Thanks! It works :weee:

18

That’s still the wrong syntax for PDO

A Life Pro Tip: let your comments out only when you have an idea what are you talking about.

19

Sorry, dude, it’s not a ready-made code on a golden plate which you came here for.
But a suggestion for a programmer, in case we have anyone of this kind around.

21

A Life Pro Tip: only answer if you’re going to explain how something works

Your answer was only code and said nothing about how it could/should work or anything about how to implement it.
So it really wasn’t an answer at all.