PHP stmt prepare fails but there are no errors

Question

I am trying to prepare a mysqli query, but it fails silently without giving any error.

 $db_hostname  = "test.com";
 $db_database   = "dbname";
 $db_username  = "db_user";
 $db_password   = "password";
 $db = new mysqli($db_hostname,$db_username,$db_password,$db_database);

 $q = "INSERT INTO Members (`wp_users_ID`,`MemberID`,`Status`,`MiddleName`,`Nickname`,`Prefix`,`Suffix`,`HomeAddress`,`City`,`State`,`Zip`,`ExtendedZip`,`BadAddress`,`SpouseFirstName`,`SpouseMiddleName`,`HomePhone`,`CellPhone`,`WorkPhone`,`WorkPhoneExt`,`OfficePhone`,`OfficePhoneExt`,`Pager`,`Fax`,`Company`,`CompanyType`,`OfficeAddress`,`OfficeAddress2`,`OfficeCity`,`OfficeState`,`OfficeZip`,`OTYPECO`,`OSTAG`,`UPCODE`,`Region`,`Department`,`Classification`,`Retired`,`Industry`,`Comments`,`Officer`,`OfficerType`,`OfficerTitle`,`OUNIT`,`ReceiveEMagazine`,`CD`,`SD`,`AD`,`isOrganization`,`DEL`,`Dues`,`DataSource`) VALUES ((?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?));";
 $stmt = $db->prepare($q);
 if ( false === $stmt ) {
      echo "<pre>";
      print_r( $db );
      echo "</pre>";
      mysqli_report(MYSQLI_REPORT_ALL);
      echo mysqli_error();
      }

The only part that actually shows anything is print_r( $db ):

 mysqli Object
 (
      [affected_rows] => -1
      [client_info] => 5.1.73
      [client_version] => 50173
      [connect_errno] => 0
      [connect_error] => 
      [errno] => 0
      [error] => 
      [error_list] => Array
      (
      )
      [field_count] => 1
      [host_info] => dbhost.com via TCP/IP
      [info] => 
      [insert_id] => 919910
      [server_info] => 5.1.73-log
      [server_version] => 50173
      [stat] => Uptime: 1924325  Threads: 8  Questions: 642600129  Slow queries: 28158  Opens: 24168750  Flush tables: 1  Open tables: 403  Queries per second avg: 333.935
      [sqlstate] => 00000
      [protocol_version] => 10
      [thread_id] => 9939810
      [warning_count] => 0
 )

Does anyone see anything that would cause this? Without any errors, it's difficult to see what is wrong... I tried copying and pasting the resulting query directly into phpmyadmin and it ran just fine (after manually substituting the question marks with test values).

Thanks!

UPDATE

It appears that since adding mysqli_report(MYSQLI_REPORT_ALL); to the top of the page, a query ABOVE the insert query is failing now, though still no error is given. This one is failing on execute:

 echo "1";
 $idDataSources = "";
 echo "2";
 $q = "SELECT idDataSources FROM DataSources WHERE `description`=(?);";
 echo "3";
 $stmt = $db->prepare($q);
 echo "4";
 $stmt->bind_param('s',$description);
 echo "5";
 $description = "File - 01/10/2015";
 echo "6";
 $stmt->execute() or die( mysqli_stmt_error( $stmt ) );
 echo "7";
 $stmt->bind_result($idDataSources);
 echo "8";
 $stmt->fetch();
 echo "9";
 unset($params);

OUTPUT:

 123456

It gets to $stmt->execute() and fails. Once again, I tried outputting the error, but nothing shows up. This is really baffling. I'm wondering if I should revert back to the old mysql (non object oriented) method ... it was insecure, but at least it worked consistently and showed errors when something was wrong.

UPDATE 2

Well, I just rewrote the entire script using mysql (non object oriented) instead of mysqli ... works like a dream. I wish I could switch to the newer standards, but with random glitches and poor error reporting like this, it sure is difficult. I'll shelf the "better" version until I can figure out why it fails.

UPDATE 3

I noticed an interesting behavior with mysqli. Elsewhere in the same code I have two queries running through STMT one after the other. This was failing every once in a while. The failures were not consistent as I could submit identical data 50 times and out of those, it might fail 20 times... same data, same function.

In an attempt to identify exactly where the script was erroring out, I put in echo commands between each statement in both queries, just spitting out a single number to see where the count stops - turns out that with the unrelated commands, it slowed STMT down just enough that it works consistently. This lead me to wonder if maybe the STMT connection is not properly closing.

$q = "";
$stmt = $this->db->prepare( "SELECT ID FROM Members WHERE MemberID='5' LIMIT 1;" );
$stmt->execute();
$stmt->store_result();
if ( $stmt->num_rows > 0 ) {
    $q = "UPDATE Members SET Name='Test' WHERE MemberID=(?) LIMIT 1;";
    }
$stmt->close();

// here if we continue, it has a chance of erroring out. However, 
// if we run just the following command instead, everything works perfect.
//  
// mysql_query( "UPDATE Members SET Name='Test' WHERE MemberID='5' LIMIT 1;" );

if ( $q != "" ) {
    $stmt = $this->db->prepare($q);
    $stmt->bind_param('i',$params['ID']);
    $params['ID'] = 5;
    $stmt->execute();
    $stmt->close();
    unset($params);
    }

Can anybody explain this behavior? It doesn't seem like they should ever be conflicting since I am using the close() command before starting a new query, and it DOES work SOME of the time... seems bizarre.

Racemose

You don't actually execute the statement – Coprolite

Yeah, it doesn't get that far. It gets to the line $stmt = $db->prepare($q); and $stmt returns false – Racemose

duplicate? #23765928 – Crowther

@Crowther - I've added the display_errors, track_errors, html_errors flags, as well as setting error_reporting(E_ALL) and mysqli_report(MYSQL_REPORT_ALL) but still no errors show up. It's sort of like mysqli is just wandering away part way through the meal without saying a word. – Racemose

Does this answer your question? mysqli_fetch_assoc() expects parameter / Call to a member function bind_param() errors. How to get the actual mysql error and fix it? – Erund

Answer 1

Here is a slightly adapted example script from php.net with error handling:

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
   echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT idDataSources FROM DataSources WHERE `description`=(?)"))) {
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind and execute */
$description = "File - 01/10/2015";
if (!$stmt->bind_param('s', $description)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

/* explicit close recommended */
$stmt->close();
?>

Please note that either $mysqli or $stmt can hold the error description.

Crowther

This should be very helpful, but there is still no output whatsoever. I'm not sure if it makes a difference, but this particular project is hosted at 1&1 shared hosting. I added error reporting as you showed above to every single step of the process, but there are still NO errors, NO feedback, just a blank page staring back at me, even if I completely isolate the query into a standalone page. – Racemose

Strange - after removing the error checks, now the code works... Literally, all I did was test (failed), added error reporting, tested (failed again), reverted the code to original, and tested one last time... and now it succeeds. Same code I started with. Is mysqli just unreliable? – Racemose

mysqli is very reliable and I don't believe in magic :). It should work with all the error-checks in place. If not, I highly recommend to track down the cause. – Crowther

In my case, I was trying to insert NULL in a column that was not allowed to be NULL. The error reporting made that clear quickly. +1 – Millham

Does not work, it still gives no error message other than 0 – Andresandresen

This is still an issue. I am experiencing the same thing and have tried everything. Still not sure why the object method is so fickle. – Aesculapius

Answer 2

I had this same issue. The problem was that I was resusing the same mysqli connection with multiple prepared statements. After each execute statement, I was sure to include the explicit close command:

$stmt->close();

This stopped the error message being suppressed and I could then see the error message in $mysqli->error.

Tumult

no way to "clean" it instead of closing?. Opening and closing the connection multiple times seems wastely. – Overgrowth

This was the solution (as mentioned deep in the original question) to my problem. Confusing as all get out. – Extrovert

Answer 3

In your initial post;

$q = "INSERT INTO Members (`wp_users_ID`,`MemberID`,`Status`,`MiddleName`,`Nickname`,`Prefix`,`Suffix`,`HomeAddress`,`City`,`State`,`Zip`,`ExtendedZip`,`BadAddress`,`SpouseFirstName`,`SpouseMiddleName`,`HomePhone`,`CellPhone`,`WorkPhone`,`WorkPhoneExt`,`OfficePhone`,`OfficePhoneExt`,`Pager`,`Fax`,`Company`,`CompanyType`,`OfficeAddress`,`OfficeAddress2`,`OfficeCity`,`OfficeState`,`OfficeZip`,`OTYPECO`,`OSTAG`,`UPCODE`,`Region`,`Department`,`Classification`,`Retired`,`Industry`,`Comments`,`Officer`,`OfficerType`,`OfficerTitle`,`OUNIT`,`ReceiveEMagazine`,`CD`,`SD`,`AD`,`isOrganization`,`DEL`,`Dues`,`DataSource`) VALUES ((?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?));";

I would change that to;

$q = "INSERT INTO Members (`wp_users_ID`,`MemberID`,`Status`,`MiddleName`,`Nickname`,`Prefix`,`Suffix`,`HomeAddress`,`City`,`State`,`Zip`,`ExtendedZip`,`BadAddress`,`SpouseFirstName`,`SpouseMiddleName`,`HomePhone`,`CellPhone`,`WorkPhone`,`WorkPhoneExt`,`OfficePhone`,`OfficePhoneExt`,`Pager`,`Fax`,`Company`,`CompanyType`,`OfficeAddress`,`OfficeAddress2`,`OfficeCity`,`OfficeState`,`OfficeZip`,`OTYPECO`,`OSTAG`,`UPCODE`,`Region`,`Department`,`Classification`,`Retired`,`Industry`,`Comments`,`Officer`,`OfficerType`,`OfficerTitle`,`OUNIT`,`ReceiveEMagazine`,`CD`,`SD`,`AD`,`isOrganization`,`DEL`,`Dues`,`DataSource`) VALUES (?, ?, ..., ?, ?)";

I removed that one semicolon you had within the double quotes of your MySQL query. Also I removed the parenthesis from around the question marks. And of course the ellipsis(...) inside the values is just so I don't have to type out all your question marks(you should put them back in your code).

Monosome

All these modifications are irrelevant – Babiche