Solved

How can I get error message for failed parameter binding?

Posted on 2013-11-27
13
1,532 Views
Last Modified: 2013-11-30
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
Comment
Question by:SAbboushi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39682397
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
 

Author Comment

by:SAbboushi
ID: 39682438
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39682629
Maybe instead of echo you could try var_dump().  Echo can produce null outputs when var_dump() has information to share.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:SAbboushi
ID: 39682712
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
 
LVL 34

Expert Comment

by:Slick812
ID: 39682777
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39684248
mysqli_stmt_errno($Statement)  returns 0 and mysqli_stmt_error($Statement) returns ""
Then in that case there has not been any error!
0
 

Author Comment

by:SAbboushi
ID: 39684259
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39684394
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
 

Author Comment

by:SAbboushi
ID: 39685637
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
 
LVL 34

Accepted Solution

by:
Slick812 earned 250 total points
ID: 39685657
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
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39685797
@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
 

Author Comment

by:SAbboushi
ID: 39687492
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
 

Author Comment

by:SAbboushi
ID: 39687532
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

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question