PHP PDO. error number '00000' when query is correct
The PDO error code 00000
means that everything works fine. The reason you're hitting the error-checking code is that $sql3
is returning 0 (no rows were effected) and PHP evaluates that to false. Try explicitly checking for a return false;
if($sql3 === false)
PDO SQL-state 00000 but still error?
It is because $pdo->errorInfo()
refers to the last statement that was successfully executed. Since $sql->execute()
returns false, then it cannot refer to that statement (either to nothing or to the query before).
As to why $sql->execute()
returns false, I don't know... either there is a problem with your $params
array or with your database connection.
PDO::errorCode — Fetch the SQLSTATE associated with the last operation on the database handle
Note: The PHP manual (http://php.net/manual/en/pdo.errorinfo.php) does not define exactly what "last operation on the database handle" means, but if there was an issue with binding parameters, that error would have occurred inside PDO and without any interaction with the database. It is safe to say that if $pdo->execute()
returns true
, that $pdo->errorInfo()
is valid. If $pdo->execute()
returns false
, the behavior of $pdo->errorInfo()
is not explicitly clear from the documentation. If I recall correctly from my experience, execute returns true
, even if MySQL returned an error, returns false
if no operation was done. Since the documentation is not specific, it might be db driver specific.
This answer reflects practical experience as of when it was written in September 2012. As a user has pointed out, the documentation does not explicitly reaffirm this interpretation. It also may only reflect the particular database driver implementation, but it should always be true that if $pdo->execute()
returns true
, that $pdo->errorInfo()
is valid.
You might also want to set PDO::ERRMODE_EXCEPTION in your connect sequence. Exception handling makes it unnecessary to check and query the error.
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
PDO not inserting - error code 00000
PDO is reported not to fill the errorInfo
property in certain circumstances.
Instead, you have to make it throw an exception, which is the most reliable way to get the error message. To do so, in your constructor, add this line
$this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
Also note that your class is a genuine example of all the mistakes one could make writing a PDO wrapper. I compiled the most popular mistakes in an article, Your first database wrapper's childhood diseases and your class contains every single one of them.
PDO error code always 00000 even when there is an error
I tested this code with PHP 7.1.23:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$sth = $pdo->prepare('select now() and this is a bad SQL where a - b from c');
if ($sth === false) {
echo "error on prepare()\n";
print_r($pdo->errorInfo());
}
if ($sth->execute() === false) {
echo "error on execute()\n";
print_r($sth->errorInfo());
}
Output:
error on execute()
Array
(
[0] => 42000
[1] => 1064
[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a bad SQL where a - b from c' at line 1
)
Then I tested the same code, except after disabling emulated prepare:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Output:
error on prepare()
Array
(
[0] => 42000
[1] => 1064
[2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a bad SQL where a - b from c' at line 1
)
Fatal error: Uncaught Error: Call to a member function execute() on boolean
Moral of the story:
When using emulated prepared statements,
prepare()
is a no-op, and the error is delayed untilexecute()
. I recommend disabling emulated prepare, unless you use a database that doesn't support prepared statements (I don't know of any current version of any RDBMS product that can't do real prepared statements).When checking for an error on prepare(), use
$pdo->errorInfo()
.When checking for an error on execute(), use
$stmt->errorInfo()
.
Why does this PDO statement silently fail?
TL;DR
- Always have set
PDO::ATTR_ERRMODE
toPDO::ERRMODE_EXCEPTION
in your PDO connection code. It will let the database tell you what the actual problem is, be it with query, server, database or whatever. Also, make sure you can see PHP errors in general. - Always replace every PHP variable in the SQL query with a question mark, and execute the query using prepared statement. It will help to avoid syntax errors of all sorts.
Explanation
Sometimes your PDO code produces an error like Call to a member function execute()
or similar. Or even without any error but the query doesn't work all the same. It means that your query failed to execute.
Every time a query fails, MySQL has an error message that explains the reason. Unfortunately, by default such errors are not transferred to PHP, and all you have is a silence or a cryptic error message mentioned above. Hence it is very important to configure PHP and PDO to report you MySQL errors. And once you get the error message, it will be a no-brainer to fix the issue.
In order to get the detailed information about the problem, either put the following line in your code right after connect
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
(where $dbh
is the name of your PDO instance variable) or - better - add this parameter as a connection option. After that all database errors will be translated into PDO exceptions which, if left alone, would act just as regular PHP errors.
After getting the error message, you have to read and comprehend it. It sounds too obvious, but learners often overlook the meaning of the error message. Yet most of time it explains the problem pretty straightforward:
- Say, if it says that a particular table doesn't exist, you have to check spelling, typos, letter case. Also you have to make sure that your PHP script connects to a correct database
- Or, if it says there is an error in the SQL syntax, then you have to examine your SQL. And the problem spot is right before the query part cited in the error message.
You have to also trust the error message. If it says that number of tokens doesn't match the number of bound variables then it is so. Same goes for absent tables or columns. Given the choice, whether it's your own mistake or the error message is wrong, always stick to the former. Again it sounds condescending, but hundreds of questions on this very site prove this advice extremely useful.
Note that in order to see PDO errors, you have to be able to see PHP errors in general. To do so, you have to configure PHP depends on the site environment:
on a development server it is very handy to have errors right on the screen, for which displaying errors have to be turned on:
error_reporting(E_ALL);
ini_set('display_errors',1);while on a live site, all errors have to be logged, but never shown to the client. For this, configure PHP this way:
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);
Note that error_reporting
should be set to E_ALL
all the time.
Also note that despite the common delusion, no try-catch have to be used for the error reporting. PHP will report you PDO errors already, and in a way better form. An uncaught exception is very good for development, yet if you want to show a customized error page, still don't use try catch for this, but just set a custom error handler. In a nutshell, you don't have to treat PDO errors as something special but regard them as any other error in your code.
P.S.
Sometimes there is no error but no results either. Then it means, there is no data to match your criteria. So you have to admit this fact, even if you can swear the data and the criteria are all right. They are not. You have to check them again. I've short answer that would help you to pinpoint the matching issue, Having issue with matching rows in the database using PDO. Just follow this instruction, and the linked tutorial step by step and either have your problem solved or have an answerable question for Stack Overflow.
PDO error message?
Try this instead:
print_r($sth->errorInfo());
Add this before your prepare:
$this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
This will change the PDO error reporting type and cause it to emit a warning whenever there is a PDO error. It should help you track it down, although your errorInfo should have bet set.
How can I get an error when running multiple queries with PDO?
I found the answer in using a prepared statement. After looping through all rowsets, I can check if the last query executed caused an error using $stmt->errorInfo()
.
$db = new PDO("mysql:host=localhost;dbname=test", 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);
$sql = "
DELETE FROM car;
INSERT INTO car(name, type) SELECT name, from FROM vehicle;
";
$stmt = $db->prepare($sql);
$stmt->execute();
$i = 0;
do {
$i++;
} while ($stmt->nextRowset());
$error = $stmt->errorInfo();
if ($error[0] != "00000") {
echo "Query $i failed: " . $error[2];
die();
}
Related Topics
Is This the Most Efficient Way to Get and Remove First Line in File
How to Decode a JSON String with Several Objects in PHP
Comparing Passwords with Crypt() in PHP
Convert Number into Xx.Xx Million Format
Bulletin Board - Database Optimisation
How to Move an Array Element with a Known Key to the End of an Array in PHP
Mp4 Plays When Accessed Directly, But Not When Read Through PHP, on iOS
Remove Null, False, and '' - But Not 0 - from a PHP Array
How to Insert into MySQL Using a Prepared Statement with PHP
How to Read Xml File from Url Using PHP
PHP - Create Simple Animated Gif from Two Jpeg Images
PHP Switch Case Statement to Handle Ranges
How to Send an Email Using PHP at Windows Azure
Phpexcel Download Using Ajax Call
Best Way to Check for Positive Integer (Php)
Difference Between Set_Time_Limit() and Ini_Set('Max_Execution_Time', ...)