JSON Response cant be used in php mysqli prepared statement

Question

So I am not really sure why I can't use the same variables that I am using in this echo statement for my prepared statement to insert the data to a mysql table. It gives me a boolean for the value. I am sure I am overlooking something but I can't seem to figure out what.

Should I store the values in a separate array and then pull them out one by one? Seems redundant...

ERROR: PHP Fatal error: Call to a member function bind_param() on boolean in functions/functions.php on line 3764

Not a duplicate of the suggestion I am getting a boolean on the variable

global $con;
require "sendgrid-php/set_api.php";
$apiKey = getenv('SENDGRID_API_KEY');
$sg = new \SendGrid($apiKey);

$response = $sg->client->contactdb()->lists()->get();
$json_response = json_decode($response->body(), true);

foreach($json_response as $key){
    foreach($key as $type=>$value){

        $cat_id = $value['id'];
        $cat_name = $value['name'];
        $rec_count = $value['recipient_count'];
        echo "<option value='$cat_id'>$cat_name ($rec_count)</option>";

        $catid = $cat_id;
        $qry2 = $con->prepare("SELECT sg_id from email_campaign_categories where sg_id = ? ");
        $qry2->bind_param("s", $catid);
        $catid = $cat_id;
        $qry2->execute();
        $qry2->store_result();
        if($qry2->num_rows < 1){
            $ins_qry = $con->prepare("INSERT into email_campaign_categories(name,sg_id) VALUES(?,?)");
            $ins_qry->bind_param("ss", $catid, $cat_name);
            $ins_qry->execute();
        }
    }
}

WORKING CODE:

global $con;
require "sendgrid-php/set_api.php";
$apiKey = getenv('SENDGRID_API_KEY');
$sg = new \SendGrid($apiKey);

$response = $sg->client->contactdb()->lists()->get();
$json_response = json_decode($response->body(), true);

foreach($json_response as $key){
    foreach($key as $type=>$value){

    $cat_id = $value['id'];
    $cat_name = $value['name'];
    $rec_count = $value['recipient_count'];
    echo "<option value='$cat_id'>$cat_name ($rec_count)</option>";

    $cat_id = $value['id'];
    $cat_name = $value['name'];
    $rec_count = $value['recipient_count'];
    $catid = $cat_id;
    $qry->free_result();
    $qry2 = $con->prepare("SELECT sg_id from email_campaign_categories where sg_id = ? ");
    echo $con->error;
    $qry2->bind_param("s", $catid);
    $qry2->execute();
    $qry2->store_result();
    if($qry2->num_rows < 1){
        $qry2->free_result();
        $ins_qry = $con->prepare("INSERT into email_campaign_categories(name,sg_id) VALUES(?,?)");
        $ins_qry->bind_param("ss", $catid, $catname);
        $catid = $cat_id;
        $catname = $cat_name;
        $ins_qry->execute();
    }
}

Looks like it means $con is a boolean. Your SQL connection attempt returned FALSE — sorak, Jan 27 '18 at 16:22
I am using the global $con which is working on the other functions called on the same page.... let me see if explicitly setting that fixes it. If so it may be a problem with the require/includes — James Buchert, Jan 27 '18 at 16:24
your functions are out of sequence/order for the SELECT statement. You're also not binding the result. — Funk Forty Niner, Jan 27 '18 at 16:29
So it turns out the global $con was the culprit, I will figure out why but that is definitely the issue. Explicitly setting it and freeing the result of $qry2 each time it loops solved the issue. — James Buchert, Jan 27 '18 at 16:32
@FunkFortyNiner I don't want to bind the result, just store it so that I can get the number of rows returned. — James Buchert, Jan 27 '18 at 16:36
Thank you @sorak I didn't even take into account the connection could be failing. I was including the con variable in several files and it was causing issues. Fixing that and freeing the results after each loop fixed the problem. — James Buchert, Jan 27 '18 at 16:37
You may not have a choice. See an example here from one of my answers https://stackoverflow.com/a/22253579/1415724 in the prepared statement example; that might help you figure out why yours is failing, in comparison with what you have now and mine. — Funk Forty Niner, Jan 27 '18 at 16:37
Oh, I see in your above comment that it was the connection after all. Can you show us what it is? — Funk Forty Niner, Jan 27 '18 at 16:38
fixed the order @FunkFortyNiner Also, I did not have to bind the result in order to get the number of rows returned. — James Buchert, Jan 27 '18 at 16:46

sorak · Accepted Answer · 2018-01-27T22:20:19.230

0

The error, "ERROR: PHP Fatal error: Call to a member function bind_param() on boolean in functions/functions.php on line 3764", tells us what to look for.

bind_param() is called on what is expected to be a query (prepared statement) object, but it is instead a boolean. This indicates that there is likely a problem with the $con entity, as the call to $con->prepare() returned FALSE.

edited Jan 27 '18 at 22:20

answered Jan 27 '18 at 16:38

sorak

2,627
2
15
24

Who thinks it's okay to downvote that? I helped him solve his problem. – sorak Jan 27 '18 at 16:39
Not sure but I upvoted and marked as the answer. Also, I included the fixed code. Note the $qry->free_result is from the include, it was throwing a command out of sync for each of the queries because of it as well as the duplicate connection include. – James Buchert Jan 27 '18 at 16:40
Because, it's a weak answer and only states that it's boolean. The OP should have provided us with the code for it and describe exactly why it failed. – Funk Forty Niner Jan 27 '18 at 16:41

JSON Response cant be used in php mysqli prepared statement

1 Answers1