php mysql prepared statement bind param error

Question

i have been stuck on this error. Appreciate any help on this:

this function is part of a longer code working with ajax. Ajax has no problem reaching into the function, retrieving post data. code returns results with $message and it gets back as to ajax, and data is retrieved as response.message

Error happens the moment it runs into the bind_param.

Tried commenting the codes from bind_param down and ajax returns test messages just fine.

When un-commented bind_param, even with if bind_param fails send message 'fail', else send message 'pass'. nothing gets into the $message.

any ideas to why this happens?

code:

function edit_Loc_Name($connection){//67
    $new_loc_name = mysqli_real_escape_string($connection, $_POST['location_editloc_name']);
    $projid = mysqli_real_escape_string($connection, $_POST['projid']);
    $loc_id = mysqli_real_escape_string($connection, $_POST['edit_loc_id']);
    $checklocname = checkLocationLoc($projid,$new_loc_name,$connection);
    if ($checklocname === "Duplicate location."){
        $message = "Duplicate location.";
    }else if($checklocname === "Location okay"){
        $stmt = $connection->prepare("UPDATE projectlocation SET locname = ? WHERE id = ?");
        if($stmt === false){
            $message = "Ajax err:67 1";$stmt->close();
        }else{
            $stmt->bind_param('si',$new_loc_name,$loc_id);
            $rc = $stmt->execute();
            if($rc === false){
                $message = "Ajax err:67 3";$stmt->close();
            }else{
                $message = "Location updated.";$stmt->close();
            }
        }
    }else{
        $message = "Ajax err:67 5";
    }
    $connection->close();
    return $message;
}

`mysqli_real_escape_string(` is not needed if you use prepared statements — Jens, Aug 02 '17 at 08:48
nothing happens, i did a message check before the bind_param and commented out the bind_param it is fine. right after i un-commented the bind param, nothing happens — NewProgrammer, Aug 02 '17 at 08:49
yes i checked the POST parameters, and both required values are there. — NewProgrammer, Aug 02 '17 at 08:52
To help people answer your question, you'll need to be more specific about the error. Please [edit] your post to incorporate the exact errors you get from your [mcve] (preferably using copy+paste to avoid transcription errors). — Toby Speight, Aug 02 '17 at 09:01
add these two lines in top of the page ini_set('display_errors','On'); error_reporting(E_ALL); and know the error . — JYoThI, Aug 02 '17 at 09:03
thanks toby, i'll redo the question. will lose the escape strings and see if it helps, but i dont think that's the issue. — NewProgrammer, Aug 02 '17 at 09:04

score 2 · Answer 1 · 2017-08-02T11:53:52.290

2

You are passing the data to real_escape_string (as commented above, it is not needed if you bind later).

This function returns a string, so your $loc_id is a string now.

But in the binding:

$stmt->bind_param('si',$new_loc_name,$loc_id);

You declare the data as si (string, integer) instead of ss.

Try to fix this.

Update:

As an example, I ran this script in my localhost, and it is working properly:

    $connection=mysqli_connect("localhost", "root", "", "my-db");

function edit_Loc_Name($connection){
$loc_id = 1;
$checklocname = "Location okay";    

if ($checklocname === "Duplicate location."){
    $message = "Duplicate location.";
}else if($checklocname === "Location okay"){
    $stmt=$connection->prepare("select * from my-table where id=?");
    if($stmt === false){
        $message = "Ajax err:67 1";//$stmt->close();
    }else{
        $stmt->bind_param('i',$loc_id);
        $rc = $stmt->execute();
        if($rc === false){
            $message = "Ajax err:67 3";//$stmt->close();
        }else{
            $message = "Location updated.";//$stmt->close();
        }
    }
}else{
    $message = "Ajax err:67 5";
}
$connection->close();
return $message;
}
echo edit_Loc_Name($connection)

edited Aug 02 '17 at 11:53

answered Aug 02 '17 at 09:08

@JYoThI but he's treating his `loc_id` as a string, thefore what Giacomo is saying instead of `si` he should use `ss` – Masivuye Cokile Aug 02 '17 at 09:26
loc_id is intended to be an integer. i have removed the real escape string and its still the same problem. – NewProgrammer Aug 02 '17 at 09:56
Still assuming that the error comes from there, try to cast the POST value: (int)$_POST['edit_loc_id'] But overall, try to print the error! – Aug 02 '17 at 10:15
Yes, been trying with $message = $stmt->error it's not showing anything, not sure if it's because of bind Parma that caused it not to show or there's just nothing – NewProgrammer Aug 02 '17 at 10:32
You are calling the function via ajax, right? What can you see in the console? Do you have ph errors activated? https://stackoverflow.com/questions/5438060/showing-all-errors-and-warnings – Aug 02 '17 at 10:37
Yes I have php errors activated, nothing is showing. Ajax has returned nothing. Only happens when bind param is involved. Other similar functions with bind param works just fine. – NewProgrammer Aug 02 '17 at 10:40
I just found 1 minute to test it locally, I'm quite sure you don't have errors active. I update my answer with a new error I noticed – Aug 02 '17 at 11:49
no, still the same issue. i'm gonna separate this portion out to see if its some size limitation – NewProgrammer Aug 02 '17 at 16:27

score 0 · Answer 2 · answered Aug 02 '17 at 16:54

During multiple function calls, $connection was closed.

checkLocationLoc($projid,$new_loc_name,$connection) had closed the $connection.

Therefore all $connection related methods are not responding.

Once the $connection->close(); is removed, all is working well.

Sorry for the trouble guys. It's a painstaking lesson to me.

php mysql prepared statement bind param error

2 Answers2