Php pdo показать ошибки

Вернуться к: PDO

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

Вернуться к: PDO

Answer by Edwin Bryan

Meta Stack Overflow

,

Stack Overflow
Public questions & answers

,Thanks for contributing an answer to Stack Overflow!,Stack Overflow em Português

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.

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
    $pdo->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());
}

prints (in my case)

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

array PDO::errorInfo();  

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

<?php  
$conn = new PDO( "sqlsrv:server=(local) ; Database = AdventureWorks ", "");  
$query = "SELECT * FROM Person.Address where Cityx = 'Essen'";  
  
$conn->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:

<?php  
try {
    $conn = new PDO("sqlsrv:server=$server;", $uid, $pwd);
    $conn->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:

<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $users = DB::table('users')->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:

$user = DB::table('users')->find(3);

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:

<?php

$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
		FROM publishers';

$statement = $pdo->query($sql);

// get all publishers
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);

if ($publishers) {
	// show the publishers
	foreach ($publishers as $publisher) {
		echo $publisher['name'] . '<br>';
	}
}
Code language: HTML, XML (xml)

Output:

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:

<?php

// show the publishers
if ($publishers) {
	foreach ($publishers as $publisher) {
		echo $publisher['name'] . '<br>';
	}
}Code language: HTML, XML (xml)

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

<?php

$publisher_id = 1;

// connect to the database and select the publisher
$pdo = require 'connect.php';

$sql = 'SELECT publisher_id, name 
		FROM publishers
        WHERE publisher_id = :publisher_id';

$statement = $pdo->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:

<?php

$publisher_id = 1;Code language: HTML, XML (xml)

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)

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.

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));

The critical part of this code is the call to the method “errorInfo on the stmt class. There are lots of different types of SQL errors that can happen. A lot of time time, if you are developing code, the cause will be the query you have written. This method will allow you to dump the entire error response that is returned from the DB.

Log PDO and SQL errors internally

This should go without saying, but I will say it anyway. You should never output SQL or PDO errors onto the page in a live scenario. These error messages can expose highly sensitive information about your web site, DB and or server. If this information gets into the wrong hands, you could find yourself with a hacked website.

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.