Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1660
  • Last Modified:

How can I get error message for failed parameter binding?

The call below works fine.  When I add an extra parameter to force an error to test my error handling, the expected error is output on the web page:
Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement

But I can't get the error into a variable in my code; my echo statement below displays:
MYSQL QUERY BINDING Failed: Error (0): ''

How can I reference the error info in my code?!

Thanks-
Sam

$Result = call_user_func_array('mysqli_stmt_bind_param',
 array_merge(array($Statement),
 array($StatementVariablesTypes),
 ReturnVariablesAsReferences($StatementVariables))); // Returns True or False

echo "MYSQL QUERY BINDING Failed:\r\n" 
 . "Error (" . mysqli_stmt_errno($Statement) . "): '" . mysqli_stmt_error($Statement) . "'";

Open in new window

0
SAbboushi
Asked:
SAbboushi
  • 6
  • 5
  • 2
2 Solutions
 
Ray PaseurCommented:
There are some good examples showing how to use MySQLi in this article, complete with error visualization.  I can't really understand your code, but I know the code in the article works correctly.  You probably want to use the object-oriented version of MySQLi for easiest notation and extensibility.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
SAbboushiAuthor Commented:
Thanks Ray -

Hopefully someone else can help me with my code.

I believe the 2 relevant pieces are this:
1) $Statement is a valid prepared query as evidenced by an object having been returned by
$Statement = mysqli_prepare ($MySQL, $StatementBase);

Open in new window

2) mysqli_stmt_errno($Statement)  returns 0 and mysqli_stmt_error($Statement) returns ""

I don't understand why I can't access the error information (in #2 above)
0
 
Ray PaseurCommented:
Maybe instead of echo you could try var_dump().  Echo can produce null outputs when var_dump() has information to share.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
SAbboushiAuthor Commented:
How would I do that?  The problem seems to be with a function call (e.g. mysqli_stmt_errno($Statement)), not a variable...

I can't get the error into a variable in my code
0
 
Slick812Commented:
greetings SAbboushi, you are trying to access a mysqli STATEMENT error, but your mysqli_stmt_bind_param( ) is already in a valid statement context, so there is no statement error, the mysqli_stmt_bind_param( ) is not as some other mysqli functions like mysqli_prepare( ), and also it may not trigger offsets (errors) as you would expect since there are 3 parameters in the mysqli_stmt_bind_param( ), many times I beleive I have found those errors with
echo mysqli_error($MySQL);
when mysqli_stmt_bind_param( )  returns false.
Hope this helps. But you might  should have an end to code progression if the mysqli_stmt_bind_param( )  returns false.
0
 
Ray PaseurCommented:
mysqli_stmt_errno($Statement)  returns 0 and mysqli_stmt_error($Statement) returns ""
Then in that case there has not been any error!
0
 
SAbboushiAuthor Commented:
Slick812: Wow... I was hoping that was the problem... but no joy.  Still same result.

Ray:
When I add an extra parameter to force an error to test my error handling, the expected error is output on the web page:
Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
0
 
Ray PaseurCommented:
Could you please create the SSCCE that we can install and run on our own servers to demonstrate and explore the issue?  I feel like I cannot see the same things you're seeing and I'd like to be able to offer you something more than blind guesses.  Thanks, ~Ray
0
 
SAbboushiAuthor Commented:
Thanks - but all I need is for someone to show me how to access the error message when a bind statement fails (i.e. mysqli_stmt_bind_param( )  returns false)... I suggest that my code is not consequential to providing the correct answer to my question.

Warning: mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement
0
 
Slick812Commented:
SAbboushi, , I see your problem exactly, and I remember struggling with many error checking returns for the mysqli functions-methods, I reviewed some of my code pages for my early learning of the mysqli setups, and I saw some attempts I made for this "Warning: mysqli_stmt_bind_param():", but I could not find anywhere that I successfully got the error-warning number or message when the mysqli_stmt_bind_param(): crapped out on me. Although I have a note that says -
//unable to find any examples for error-warning in $stm->bind_param
so it may not be implemented yet ? ? But I do have it plainly stated -
// if $stm->bind_param fails NO CODE for the query works past this, as execute() fails.
There is something you can try, and it did not work for me then, but PHP has been updated since then, you can see if for statement -
mysqli_stmt_get_warnings($stmt);  or $stmt->get_warnings($stmt);
can help you, but these may also not be implemented yet ? ? I am not sure what these may return, and array or object I would guess? ?
there is also a a higher level get warning as -
$mysqli->get_warnings( );
but this returns the TYPE of mysqli_warning, which I could not find anything about, and object I guess, but it's difficult to tell things that the PHP developers are still working on in the mysqli building process, if you are interested the PHP code is open source, and you can find some bug reports about this sort of thing , acording to my notes, I just gave up on that warning for that , and just echo something like "ERROR - bind_param( ) Failed", but fortunately in development I always get the PHP "Warning: mysqli_stmt_bind_param():" and can review and fix the problem.

just a note - I found that using the -
call_user_func_array('mysqli_stmt_bind_param'
was less than "sure thing" (it does work for most situations), what I did for a "variety of binds references" was to make an array of the VALUES as-
$aryIn = array('krazy@outhouse.cam', 'smith', 2);
then I get the count of that array
$cnt = count($aryIn);

then I do a switch -
$fail = false;
  switch($cnt){
    case 1: $fail =$this->stmt->bind_param('s', $aryIn[0]);break;
    case 2: $fail =$this->stmt->bind_param('ss', $aryIn[0],$aryIn[1]);break;
    case 3: $fail =$this->stmt->bind_param('sss', $aryIn[0],$aryIn[1],$aryIn[2]);break;
    case 4: $fail =$this->stmt->bind_param('ssss', $aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3]);break;
    case 5: $fail =$this->stmt->bind_param('sssss', $aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3],$aryIn[4]);break;
    case 6: $fail =$this->stmt->bind_param('ssssss', $aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3],$aryIn[4],$aryIn[5]);break;
    case 7: $fail =$this->stmt->bind_param('sssssss',$aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3],$aryIn[4],$aryIn[5],$aryIn[6]);break;
    case 8: $fail =$this->stmt->bind_param('ssssssss',$aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3],$aryIn[4],$aryIn[5],$aryIn[6],$aryIn[7]);break;
    case 9: $fail =$this->stmt->bind_param('sssssssss', $aryIn[0],$aryIn[1],$aryIn[2],$aryIn[3],$aryIn[4],$aryIn[5],$aryIn[6],$aryIn[7],$aryIn[8]);break;

  }
if (!$fail) {$this->Aerror = 'ERROR: for AryExecute(), stmt->bind_param Failed, $aryIn was incorrectly formated'; return false;}

Open in new window

I found that the the TYPE definitions in bind_param( ) (first parameter as "sss") was poorly implemented, and there was NO TYPE checking by PHP at all, none, all it does is do the usual default PHP TYPE conversion for ALL of the bind variables, I found that if I use the string TYPE as 's' for any and all variable types it would always work! ! as PHP will attempt to convert the PHP variable type to the MYSQLI TABLE TYPE returned in the back send from the mysqli engine. So I can use integers, strings, floats in the input variables, and use 'sss' for the TYPE definitions , and it always works!

you can also define the string query so that the number of values match the query string with something like -

$question = '?';// default is for ONE in Array
if($cnt > 1) $question=str_pad($question,($cnt*2)-1,',?');

$query = str_replace('%?%', $question, $query);

where query is something like -
$query = 'SELECT id, uname, firstName FROM examp WHERE lastName IN (%?%)';

but this is NOT a universal replacement.
0
 
Ray PaseurCommented:
@SAbboushi: If you can't take the time to create the example, I don't know why anyone else would take the time to create the example.  It's your question, not ours, and it's always easier to get help if you show us the isolated test case that enables us to duplicate the issue.  For a starting point, you could copy the last code snippet from this article:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Adapting the last code snippet from that article, I've found the following way to get the warning message.  The MySQLi extension, especially with prepared statements has its issues.  As but one example, the exception handler does not appear to work correctly.

When bind_param() method fails, there is no error message in the objects.  PHP throws a warning, and that's your only indication of failure.  I will leave it up to you to decide whether this is a bug or a feature.  Install this script with your own credentials and run it.   It will work correctly.  Then go to line 120 and change 'ss' to 'sss' and run it again.  You will be able to see the information available to you in the output of the var_dump() statements at the end.

<?php // RAY_EE_mysqli_prepare_example_fail.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-param.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.execute.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.store-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.fetch.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    die($err);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' IN '
    . __FILE__
    . ' LINE '
    . __LINE__
    ;
    trigger_error($err, E_USER_ERROR);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// WE CAN CATCH THE WARNING IN THE OUTPUT BUFFER
$buffer = NULL;
ob_start();

// BIND INPUT VARIABLES TO THE QUERY WILL FAIL WITH 'sss'
$res = $ins->bind_param('ss', $person['fname'], $person['lname']);
if (!$res)
{
    $buffer = trim(ob_get_clean());
}

// SHOW THE VARIABLES
var_dump($res);
var_dump($ins);
var_dump($mysqli);
var_dump($buffer);

Open in new window

0
 
SAbboushiAuthor Commented:
Slick812: Thanks so much for taking the time to go through your notes and post all that information.  Very helpful - I will try out some of your suggestions and post back.
0
 
SAbboushiAuthor Commented:
Ray - thanks for the information and taking the time to examine the problem.  Looks like you came to the same finding which prompted this post:
When bind_param() method fails, there is no error message in the objects.  PHP throws a warning, and that's your only indication of failure.

Slick812:

mysqli_stmt_get_warnings($stmt) was a good idea... I use that to check the syntax of the base statement before trying to bind the parameters.  So I don't believe it will help with bind failure.

I also tried your other suggestion:
$x=mysqli_get_warnings($MySQL );
It returned "0" after the bind failure.

Thanks Ray and Slick812 for your help... at least now I know I'm not missing anything.

Ray - I wish I had seen your article BEFORE I had spent a few days figuring out how do what you had laid out very nicely.  I like your coding style.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now