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.
