PHP Implementation of Method to Capture SQL Statement Error in PDO

This example demonstrates the method of catching SQL statement errors in PDO implemented in PHP. Shared for everyone's reference, as follows:

Use the default mode-----PDO::ERRMODE_SILENT

Set the errorCode property of the PDOStatement object in the default mode, but do not perform any other operations.

For example:

Add data to the database through the prepare() and execute() methods, set the errorCode property of the PDOStatement object, manually check for errors in the code, the steps are as follows.

$dbms='mysql';//Database type
$dbName='admin';//The database being used
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database hostname
$dsn="mysql:host=$host;port=3306;dbname=$dbName";
$pdo=new PDO($dsn,$user,$pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$query="insert into user (username,password) values('admin')";//The SQL statement to be executed
$res=$pdo->prepare($query);
$res->execute();
$code=$res->errorCode();
echo $code.'<br>';
if($code==00000){//如果没有任何错误, errorCode() 返回的是: 00000 ，否则就会返回一些错误代码
echo "数据添加成功";
}else{
echo "数据库错误：
";
echo 'SQL Query:'.$query;
echo '<pre>';
var_dump($res->errorInfo());
echo '<pre>';
}

运行结果如下

21S01
数据库错误：
SQL Query:insert into user (username,password) values('admin')
array(3) {
 [0]=>
 string(5) "21S01"
 [1
 int(1136)
 [2
 string(47) "Column count doesn't match value count at row 1"
}

Use warning mode-----PDO::ERRMODE_WARNING

Warning mode will generate a PHP warning and set the errorCode attribute. If the warning mode is set, the program will continue to run as usual unless the error code is explicitly checked.

For example:

Set to warning mode, read data from the database through the prepare() and execute() methods, and complete the loop output of data through the while statement and fetch() method. Experience the execution of incorrect SQL statements after setting to warning mode.

$dbms='mysql';//Database type
$dbName='admin';//The database being used
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database hostname
$dsn="mysql:host=$host;port=3306;dbname=$dbName";
try {
$pdo = new PDO($dsn, $user, $pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//Set to warning mode
$query = "select * from userrr";//The SQL statement to be executed
$res = $pdo->prepare($query);//Prepare the query statement
$res->execute();
while ($result = $res->fetch(PDO::FETCH_ASSOC)) {//Output the query result set in a while loop and set the result set to return in the form of an associated array.
echo $result['id'] . " " . $result['username'] . " " . $result['password'];
  }
}catch(PDOException $e){
die("ERROR!:".$e->getMessage().'<br>');
}
echo "Continue continue continue continue continue continue continue";

The execution result is as follows:

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'admin.userrr' doesn't exist in D:\wampserver\www\test\test\index1.php on line 14
Continue continue continue continue continue continue continue

It can be seen that after setting the warning mode, if the SQL statement has an error, it will give a prompt message, but the program can still continue to execute.

Use exception mode----PDO::ERRMODE_EXCEPTION

The exception mode will create a PDOException and set the errorCode attribute, which can encapsulate the execution code into a try{}catch{} statement block. Uncaught exceptions will cause the script to break and display a stack trace to let the user know where the problem occurred.

For example:

Delete information from an erroneous data table

$dbms='mysql';//Database type
$dbName='admin';//The database being used
$user='root';//Database connection username
$pwd='password';//Database connection password
$host='localhost';//Database hostname
$dsn="mysql:host=$host;port=3306;dbname=$dbName";
try {
$pdo = new PDO($dsn, $user, $pwd);//Initialize a PDO object, which is to create a database connection object $pdo
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//Set the exception mode
$query = "delete from userrr where id="1";//The SQL statement to be executed
$res = $pdo->prepare($query);//Prepare the delete statement
$res->execute();
}catch(PDOException $e){
echo 'PDO Exception Caught: ';
echo "Error with the database:<br>";
echo 'SQL Query:'.$query;
echo '<pre>';
echo "ERROR:".$e;->getMessage().'<br>';
echo "Code:".$e;->getCode().'<br>';
echo "File:".$e;->getFile().'<br>';
echo "Line:".$e;->getLine().'<br>';
echo "Trace:".$e->getTraceAsString().'<br>';
echo '</pre>';
}

Run Result:

PDO Exception Caught: Error with the database:
SQL Query:delete from userrr where id=1
ERROR:SQLSTATE[42S02]: Base table or view not found: 1146 Table 'admin.userrr' doesn't exist
Code:42S02
File:D:\wampserver\www\test\test\index1.php
Line:14
Trace:#0 D:\wampserver\www\test\test\index1.php(14): PDOStatement->execute()
#1 {main}

Readers who are interested in more about PHP-related content can check the special topic of this site: PHP Database Operation Skills Summary Based on PDO, PHP+Oracle Database Program Design Skills Summary, PHP+MongoDB Database Operation Skills Summary, PHP Object-Oriented Program Design Tutorial, PHP String (string) Usage Summary, PHP+MySQL Database Operation Tutorial and PHP Common Database Operation Skills Summary

I hope the description in this article is helpful to everyone's PHP program design.

Statement: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, does not edit the content manually, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (When reporting, please replace # with @ and provide relevant evidence. Once verified, this site will immediately delete the infringing content.)