Solved

How can I get error message for failed parameter binding?

Posted on 2013-11-27
13
1,288 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 108

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 108

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
 

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 33

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 108

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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 108

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 33

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 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now