Php pdo check if error

Содержание

How to view query error in PDO PHP
Answer by Harley Ware
Answer by Kaiya Dean
Answer by Taylor Murray
Answer by Jada Stark
Syntax
Answer by Chloe Beck
Answer by Paxton Grant
PDO::errorCode
Examples
See Also
User Contributed Notes 3 notes

How to view query error in PDO PHP

You need to set the error mode attribute PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION.
And since you expect the exception to be thrown by the prepare() method you should disable the PDO::ATTR_EMULATE_PREPARES* feature. Otherwise the MySQL server doesn’t «see» the statement until it’s executed.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $pdo->prepare('INSERT INTO DoesNotExist (x) VALUES (?)'); > catch(Exception $e) < echo 'Exception ->'; var_dump($e->getMessage()); >

Exception -> string(91) "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'test.doesnotexist' doesn't exist"

*) and then there’s PDO::MYSQL_ATTR_DIRECT_QUERY — I must admit that I don’t understand the interaction of those two attributes (yet?), so I set them both, like

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array( PDO::ATTR_EMULATE_PREPARES=>false, PDO::MYSQL_ATTR_DIRECT_QUERY=>false, PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION ));

Answer by Harley Ware

PDO::errorInfo — Fetch extended error information associated with the last operation on the database handle , PDO::errorInfo() returns an array of error information about the last operation performed by this database handle. The array consists of at least the following fields: ,PDOStatement::errorInfo() — Fetch extended error information associated with the last operation on the statement handle,PDO::errorCode() — Fetch the SQLSTATE associated with the last operation on the database handle

PDO::errorInfo(): Array ( [0] => HY000 [1] => 1 [2] => near "bogus": syntax error )

Answer by Kaiya Dean

You need to set the error mode attribute PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION. And since you expect the exception to be thrown by the prepare() method you should disable the PDO::ATTR_EMULATE_PREPARES* feature. Otherwise the MySQL server doesn’t «see» the statement until it’s executed.,Exception handling in java,Python interview questions,Robotic Process Automation Training using UiPath

try < $db = new PDO("mysql:host=".HOST.";dbname=".DB, USER, PW); $st = $db->prepare("SELECT * FROM c6ode"); > catch (PDOException $e)< echo $e->getMessage(); >

Answer by Taylor Murray

In order to output the PDO error you will need to get the error info from the statement object. To implement this function simply add the or die code to the end of the $stmt->execute code.,Often it can be quite hard to debug SQL errors when using PHP since PHP will often throw a generic error that doesn’t really help you diagnose the situation. Other times you may not want to allow PHP errors to be shown. A quick way to debug PDO SQL errors is to use the or die function. “or die” works by executing the SQL query and if it fails to execute it will “die” and output the contents of the function. This is quite similar to a try catch block.,The code snippet above will dump the entire error onto the web page. Very useful in development, but not in production. Please be careful and use an internal logging system to keep track of database errors on a production website.,This site uses Akismet to reduce spam. Learn how your comment data is processed.

In order to output the PDO error you will need to get the error info from the statement object. To implement this function simply add the or die code to the end of the $stmt->execute code.

$stmt = $db->prepare($sql); $stmt->execute($array) or die(print_r($stmt->errorInfo(), true));

Answer by Jada Stark

The driver-specific error message.,The driver-specific error message. ,Starting with 5.9.0, the default behavior of PDO::errorInfo is to show additional ODBC errors, if they are available. For example:,The driver-specific error code.

Syntax

In this example, the name of the column is misspelled ( Cityx instead of City ), causing an error, which is then reported.

query($query); print $conn->errorCode(); echo "\n"; print_r ($conn->errorInfo()); ?>

When an exception occurs, the ODBC Driver may return more than one error to help diagnose problems. However, PDO::errorInfo always shows only the first error. In response to this bug report, PDO::errorInfo and PDOStatement::errorInfo have been updated to indicate that drivers should display at least the following three fields:

0 SQLSTATE error code (a five characters alphanumeric identifier defined in the ANSI SQL standard). 1 Driver specific error code. 2 Driver specific error message.

Starting with 5.9.0, the default behavior of PDO::errorInfo is to show additional ODBC errors, if they are available. For example:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SET NOCOUNT ON; USE $database; SELECT 1/0 AS col1"); $stmt->execute(); > catch (PDOException $e) < var_dump($e->errorInfo); > ?>

Running the above script should have thrown an exception, and the output is like this:

array(6) < [0]=>string(5) "01000" [1]=> int(5701) [2]=> string(91) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'tempdb'." [3]=> string(5) "22012" [4]=> int(8134) [5]=> string(87) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered." >

If the user prefers the previous way, a new configuration option pdo_sqlsrv.report_additional_errors can be used to turn it off. Simply add the following line in the beginning of any php script:

ini_set('pdo_sqlsrv.report_additional_errors', 0);

In this case, the error info shown will be like this, when running the same example script:

array(3) < [0]=>string(5) "01000" [1]=> int(5701) [2]=> string(91) "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'tempdb'." >

If necessary, the user may choose to add the following line to the php.ini file in order to turn off this feature in all their php scripts:

pdo_sqlsrv.report_additional_errors = 0

Beginning with 5.9.0, ODBC warnings will no longer be logged as errors. That is, error codes with prefix «01» are logged as warnings. In other words, if the user wants to log errors only, update the php.ini like this:

[pdo_sqlsrv] pdo_sqlsrv.log_severity = 1

Answer by Chloe Beck

To retrieve a single row by its id column value, use the find method:,The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings passed to the query builder as query bindings.,The whereNotBetween method verifies that a column’s value lies outside of two values:,The whereNull method verifies that the value of the given column is NULL:

You may use the table method provided by the DB facade to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:

get(); return view('user.index', ['users' => $users]); > >

The get method returns an Illuminate\Support\Collection instance containing the results of the query where each result is an instance of the PHP stdClass object. You may access each column’s value by accessing the column as a property of the object:

use Illuminate\Support\Facades\DB; $users = DB::table('users')->get(); foreach ($users as $user) < echo $user->name; >

If you just need to retrieve a single row from a database table, you may use the DB facade’s first method. This method will return a single stdClass object:

$user = DB::table('users')->where('name', 'John')->first(); return $user->email;

If you don’t need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

$email = DB::table('users')->where('name', 'John')->value('email');

To retrieve a single row by its id column value, use the find method:

Answer by Paxton Grant

The query() method returns a PDOStatement object. If an error occurs, the query() method returns false.,The query() method of a PDO object.,When a query doesn’t have any parameters, you can use the query() method. For example:,Use the query() method of an PDO object to execute a SELECT statement to query data from one or more tables.

When a query doesn’t have any parameters, you can use the query() method. For example:

.wp-block-code < border: 0; padding: 0; >.wp-block-code > div < overflow: auto; >.shcb-language < border: 0; clip: rect(1px, 1px, 1px, 1px); -webkit-clip-path: inset(50%); clip-path: inset(50%); height: 1px; margin: -1px; overflow: hidden; padding: 0; position: absolute; width: 1px; word-wrap: normal; word-break: normal; >.hljs < box-sizing: border-box; >.hljs.shcb-code-table < display: table; width: 100%; >.hljs.shcb-code-table > .shcb-loc < color: inherit; display: table-row; width: 100%; >.hljs.shcb-code-table .shcb-loc > span < display: table-cell; >.wp-block-code code.hljs:not(.shcb-wrap-lines) < white-space: pre; >.wp-block-code code.hljs.shcb-wrap-lines < white-space: pre-wrap; >.hljs.shcb-line-numbers < border-spacing: 0; counter-reset: line; >.hljs.shcb-line-numbers > .shcb-loc < counter-increment: line; >.hljs.shcb-line-numbers .shcb-loc > span < padding-left: 0.75em; >.hljs.shcb-line-numbers .shcb-loc::before < border-right: 1px solid #ddd; content: counter(line); display: table-cell; padding: 0 0.75em; text-align: right; -webkit-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; white-space: nowrap; width: 1%; >SELECT * FROM publishers;Code language: SQL (Structured Query Language) (sql)

The following illustrates how to query all rows from the publishers table in the bookdb database:

query($sql); // get all publishers $publishers = $statement->fetchAll(PDO::FETCH_ASSOC); if ($publishers) < // show the publishers foreach ($publishers as $publisher) < echo $publisher['name'] . '
'; > > Code language: HTML, XML (xml)

McGraw-Hill Education Penguin/Random House Hachette Book Group Harper Collins Simon and Schuster

First, create a database connection to the bookdb database:

$pdo = require 'connect.php';Code language: PHP (php)

Second, define an SQL SELECT statement to select all rows from publishers table:

$sql = 'SELECT publisher_id, name FROM publishers';Code language: PHP (php)

Third, run the query by calling the query() method of the PDO object:

$statement = $pdo->query($sql);Code language: PHP (php)

Fourth, fetch all data from the result set:

$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);Code language: PHP (php)

Finally, iterate over the result set and show the array’s element:

'; > >Code language: HTML, XML (xml)

The following example illustrates how to use a prepared statement to query data from a table:

prepare($sql); $statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT); $statement->execute(); $publisher = $statement->fetch(PDO::FETCH_ASSOC); if ($publisher) < echo $publisher['publisher_id'] . '.' . $publisher['name']; >else < echo "The publisher with id $publisher_id was not found."; >Code language: HTML, XML (xml)

First, define a publisher id. In practice, you may get it from the query string:

Second, use the connect.php to connect to the bookdb database and return a new instance of the PDO object:

$pdo = require 'connect.php';Code language: PHP (php)

Third, construct an SQL SELECT statement with a named placeholder ( :publisher_id )

$sql = 'SELECT publisher_id, name FROM publishers WHERE publisher_id = :publisher_id';Code language: PHP (php)

Fourth, bind the value of the id to the prepared statement:

$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);Code language: PHP (php)

Fifth, execute the prepared statement:

$statement->execute();Code language: PHP (php)

Sixth, fetch a row from the result set into an associative array:

$publisher = $statement->fetch(PDO::FETCH_ASSOC);Code language: PHP (php)

Finally, show the publisher information or an error message:

if ($publisher) < echo $publisher['publisher_id'] . '.' . $publisher['name']; >else < echo "The publisher with id $publisher_id was not found."; >Code language: PHP (php)

Источник

PDO::errorCode

Returns an SQLSTATE, a five characters alphanumeric identifier defined in the ANSI SQL-92 standard. Briefly, an SQLSTATE consists of a two characters class value followed by a three characters subclass value. A class value of 01 indicates a warning and is accompanied by a return code of SQL_SUCCESS_WITH_INFO. Class values other than ’01’, except for the class ‘IM’, indicate an error. The class ‘IM’ is specific to warnings and errors that derive from the implementation of PDO (or perhaps ODBC, if you’re using the ODBC driver) itself. The subclass value ‘000’ in any class indicates that there is no subclass for that SQLSTATE.

PDO::errorCode() only retrieves error codes for operations performed directly on the database handle. If you create a PDOStatement object through PDO::prepare() or PDO::query() and invoke an error on the statement handle, PDO::errorCode() will not reflect that error. You must call PDOStatement::errorCode() to return the error code for an operation performed on a particular statement handle.

Returns null if no operation has been run on the database handle.

Examples

Example #1 Retrieving an SQLSTATE code

/* Provoke an error — the BONES table does not exist */
$dbh -> exec ( «INSERT INTO bones(skull) VALUES (‘lucy’)» );

echo «\nPDO::errorCode(): » , $dbh -> errorCode ();
?>

The above example will output:

User Contributed Notes 3 notes

Use the following Code to let PDO throw Exceptions (PDOException) on Error.

$pdo = new PDO ( whatever );
$pdo -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION );
try <
$pdo -> exec ( «QUERY WITH SYNTAX ERROR» );
> catch ( PDOException $e ) <
if ( $e -> getCode () == ‘2A000’ )
echo «Syntax Error: » . $e -> getMessage ();
>
?>

Bye,
Matthias

This reading says that «Returns NULL if no operation has been run on the database handle».

When I tested it based on pdo-mysql, I got 00000, not NULL!