Prepared statements and bind_param error handling

Question

In the following php code with prepared statements is intentionally caused an error to test bind_param and execute, adding $bikes.

In the execute function the error information is returned; however, in bind_param, although an error occurs, it does not return any error information.

How to get error information in bind_param?

$sqlQuery = "INSERT INTO the_cars (cars) VALUES (?)";

if($statement = $con->prepare($sqlQuery)){

    if(!$statement->bind_param("s", $cars, $bikes)){ //bikes should not be here
        $errors = $statement->error; //error is empty
    };

    if(!$statement->execute()){
        $errors1 = $statement->error; // error: No data supplied for parameters in prepared statement
    };

}else{    
    $errors = $con->error;
}

EDITED:

The PHP manual seems to suggest that in bind_param the error should be handled. See the following part of the text: Example #3 INSERT prepared once, executed multiple times » /* Prepared statement, stage 2: bind and execute */

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

This also seems to be the position advocated in some posts. For example:

MySQLi prepared statements error reporting

However, I made several attempts, and I was never able to get a description of a statement error in bind_param.

I think you must check it for yourself. – Sysix Sep 10 '17 at 20:37 — Sysix, Sep 10 '17 at 20:37

Funk Forty Niner · Accepted Answer · 2017-09-10T22:07:13.827

3

You can't get an error (nor have you gotten one) out of something that hasn't yet been executed, therefore the second conditional statement won't throw an error. You need to check if the execution was successful and not the against the bind_param() method.

Then the third (conditional statement) won't theoretically thrown an error because of what you have in your query that would theoretically be considered as being a valid (query) statement.

What you need to do is to remove the if(!$statement) statement from the bind, but keep it in the execution part.

You will then receive an error.

Your first conditional statement for if($statement = $con->prepare($sqlQuery)) is valid, so the else for it won't throw an error since it hasn't been executed.

Consult the following reference manuals on PHP.net on how to query/check for errors properly and don't try to reinvent what wasn't intended to throw errors in the first place:

In short, error handling is done on the query (and its execution) and not on the binding.

Consult the manual on bind_param():

http://php.net/manual/en/mysqli-stmt.bind-param.php

There is no mention or examples of error handling on that method.

edited Sep 10 '17 at 22:07

answered Sep 10 '17 at 21:51

Funk Forty Niner

74,450
15
68
141

Thanks Fred. Since I could not write everything in the comment I edited my post. My question is centered on bind_param. Everything points in the sense that your opinion is correct (opinion with which I agree). That is, although there may be a failure when executing bind_param only after the execute the statement error is triggered.Prepare return a connection error and execute return a statement error. – jose Sep 11 '17 at 00:17
You're welcome @josei I'm curious to know though, why you would want to check if the binding first failed. As for the link you included in the edit https://stackoverflow.com/questions/2552545/mysqli-prepared-statements-error-reporting - that is correct. You would first need to check if both variables contained value, otherwise don't prepare or try to bind. The original answer I posted should be what you would need to concentrate on, and not something that isn't possible to do with binding; it's always the variables' existance and the right bind parameter such as an `i` or `s` or other. – Funk Forty Niner Sep 11 '17 at 00:24
I agree. For me it was certain that I should treat the errors in the prepared statements; that is, prepare and execute. Due to the above links, I was wondering if there were any specific errors that could occur in bind_param and that I was not seeing well. That's one of the reasons for my post. Your opinion helps me to be more certain of the appropriate way of handling the errors of the prepared statements. Thanks again. – jose Sep 11 '17 at 00:37
@josei The only thing I can foresee that would actually fail in a bind is either that the type used was incorrect, say if you had an `int` type of column and using `s` rather than `i`, and/or that one of the variables didn't match in type also. Say if you had `i` but used a string. However, using an `s` for even an integer value used *but* the column type is a string, that would not fail, strangely enough, but the inverse would. – Funk Forty Niner Sep 11 '17 at 00:41
I had tried to replace 's' with 'i' but did not fire any error; neither bind_param nor execute. However, adding another variable in bind_param, as in my example, triggers an error in execute.In case some unexpected error is triggered in bind_param I am considering handling this error with a custom message. – jose Sep 11 '17 at 00:49
@josei I'd like to help you more on solving what it is you are wanting to achieve. Let's see if someone else will pick up on your question and hopefully solve it or shed more light on the subject. – Funk Forty Niner Sep 11 '17 at 01:04

Prepared statements and bind_param error handling

1 Answers1