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.

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.

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.