Get pdo error message

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

PDO предлагает на выбор 3 стратегии обработки ошибок в зависимости от вашего
стиля разработки приложений.

• PDO::ERRMODE_SILENT

  Это режим по умолчанию. PDO просто предоставит вам код ошибки, который
  можно получить методами PDO::errorCode() и
  PDO::errorInfo(). Эти методы реализованы как в объектах
  запросов так и в объектах баз данных. Если ошибка вызвана во время выполнения
  кода объекта запроса, нужно вызвать метод
  PDOStatement::errorCode() или
  PDOStatement::errorInfo() этого объекта. Если ошибка
  вызова объекта базы данных, нужно вызвать аналогичные методы у этого объекта.

• PDO::ERRMODE_WARNING

  Помимо задания кода ошибки PDO выдаст обычное E_WARNING сообщение. Это может
  быть полезно при отладке или тестировании, когда нужно видеть, что произошло,
  но не нужно прерывать работу приложения.

• PDO::ERRMODE_EXCEPTION

  Помимо задания кода ошибки PDO будет выбрасывать исключение
  PDOException, свойства которого будут отражать
  код ошибки и ее описание. Этот режим также полезен при отладке, так как
  сразу известно, где в программе произошла ошибка. Это позволяет быстро
  локализовать и решить проблему. (Не забывайте, что если исключение
  является причиной завершения работы скрипта, все активные транзакции
  будут откачены.)

  Режим исключений также полезен, так как дает возможность структурировать
  обработку ошибок более тщательно, нежели с обычными PHP предупреждениями, а
  также с меньшей вложенностью кода, чем в случае работы в тихом режиме с
  явной проверкой возвращаемых значений при каждом обращении к базе данных.

  Подробнее об исключениях в PHP см. в разделе Исключения.

PDO стандартизирован для работы со строковыми кодами ошибок SQL-92 SQLSTATE.
Отдельные PDO драйверы могут задавать соответствия своих собственных кодов
кодам SQLSTATE. Метод PDO::errorCode() возвращает одиночный
SQLSTATE код. Если необходима специфичная информация об ошибке, PDO предлагает
метод PDO::errorInfo(), который возвращает массив, содержащий
SQLSTATE код, код ошибки драйвера, а также строку ошибки драйвера.

Замечание:

Метод PDO::__construct() будет всегда бросать исключение PDOException,
если соединение оборвалось, независимо от установленного значения PDO::ATTR_ERRMODE.
Непойманные исключения фатальны.

Warning: PDO::query(): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'test.wrongtable' doesn't exist in
/tmp/pdo_test.php on line 18

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

We can display error message in case of an error generated by MySQL query. This meaning full error message gives idea one the problem or bugs in the script. We can print the error message by using mysql function mysql_error(). This function returns the error message associated with most recently executed query. So it is a good idea to check for error before going to next query. We can even add the error number returned by mysql to this error message. This way we can generate a meaningful detail on the bugs on the script.

$querry = mysql_query("SELECT new_field FROM student");

echo "Error message = ".mysql_error();

In our student table there is no field as new_field. Here here is the message we will get

Error message = Unknown column 'new_field' in 'field list'

Note that mysql_error() is deprecated as of PHP 5.5.0 so better to avoid using this function. So if you are using PDO then we can use errorInfo() to display the returned error message from MySQL

Here is a sample code in PHP using PDO to display record and on failure to display error message.

require 'config-pdo.php'; // database connection string 
$pdo=$dbo->prepare('Select no_name from student');
if($pdo->execute()){
echo 'Success<br>';
$row = $pdo->fetch(PDO::FETCH_OBJ);
echo "Name : $row->name ";
}else{
print_r($pdo->errorInfo());
}

In the above code there is an error in sql , there is no column by name no_name. The output will be

Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'no_name' in 'field list' )

So to get correct result change the sql part like this .

$pdo=$dbo->prepare('Select * from student');

With this you will get desired output.

Handling PDO errors

If such error occurs what is to be done ? We have three options to handle in case of errors.

We can stop the execution of the script. ( Fatal error : stop execution of code )
We can display warning message ( Warning only, display message and no stoppage of execution )
Remain silent ( continue to execute and display error message if required )

Setting the PDO error handling attribute.

We can use setAttribute to tell how to handle the PDO errors.
Here is a sample

$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);   // gives warning only

Here is the complete code.

require 'config-pdo.php'; // database connection string 
//$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // generates fatal error
//$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);   // gives warning only
$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);  // remain silent 
 
$pdo=$dbo->prepare('Select no_name from student');
if($pdo->execute()){
echo 'Success<br>';
$row = $pdo->fetch(PDO::FETCH_OBJ);
echo "Name : $row->name ";
}else{
print_r($pdo->errorInfo());
}

Now we have given all the three options but commented two and set the attribute to Silent. You can change the commented status and see how the script is behaving in different setAttribute values.

It is clear that the above code will generate error message. We can store the error message in a database or we can post ( by mail ) to the programmer about these details.

Store the error message generated in an sql query or send email >>

In this tutorial we will learn about PHP PDO.

What is PDO?

PDO (PHP Data Objects) is a PHP extension used to access and work with different types of databases like MySQL, Oracle etc by writing same code.

It acts as a data abstraction layer that helps in issuing query and fetch data regardless of database.

How to connect to a database using PDO?

We call the PDO constructor to create database connection.

//constants
define('DB_DSN', 'mysql:host=localhost;dbname=mydb');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root123');

try {
	//create pdo connection object
	$pdocon = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
} catch (PDOException $e) {
	printf("Error: " . $e->getMessage());
	die();
}

In the above code PDO constructor takes three arguments namely dsn, username and password.

The DB_DSN holds the Data Source Name which contains two items, a database driver name and the other is the database name. DB_USERNAME and DB_PASSWORD holds the username and password of the database respectively.

Throughout this tutorial we will use $pdocon as PDO object.

How to get PDO error code?

We use the errorCode() method to get the error code when error occurs. These error codes are standard SQLSTATE codes.

$pdocon->errorCode();

How to get PDO error message?

We use the errorInfo() method to get the error message when error occurs. This method will return an array. This array consists of three elements. The 0th element represents the SQLSTATE code. The 2nd element contains the database driver specific error code. And the 3rd element contains the database driver specific error message.

$pdocon->errorInfo();

Types of query execution

There are basically three type of query executions.

Query execution with no result set: Query like INSERT, UPDATE and DELETE returns no result set and only tells us about the number of row affected.

Query execution with result set: Query like SELECT generally returns a result set and also the number of rows returned.

Query executed multiple times: For instance, running INSERT query multiple time by passing different data.

INSERT, UPDATE and DELETE data

To insert, update and delete data we use the exec() method and it returns the number of rows affected.

In the following example we execute INSERT query.

//query
$query = sprintf("INSERT INTO student (`studentid`, `name`, `branch`) VALUES ('s001', 'Yusuf', 'CSE')");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Inserted Rows: %d", $affectedRows);		//this will print "Inserted Rows: 1"

In the following example we execute UPDATE query.

//query
$query = sprintf("UPDATE student SET name = 'Alice' WHERE studentid = 's2016'");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Updated Rows: %d", $affectedRows);		//this will print "Updated Rows: 1"

In the following example we execute DELETE query.

//query
$query = sprintf("DELETE FROM student WHERE studentid = 's101'");

//execute the query
$affectedRows = $pdocon->exec($query);

//number of rows affected
printf("Deleted Rows: %d", $affectedRows);		//this will print "Deleted Rows: 1"

SELECT data

To select data we use the query() method which returns a result set as PDOStatement object. To know about the total number of rows returned we use the rowCount() method.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute query
$result = $pdocon->query($query);

//total rows returned
printf("Selected Rows: %d", $result->rowCount());

//loop through the results
foreach ($result as $row) {
	printf("StudentID: %s Name: %s Branch: %s", $row['studentid'], $row['name'], $row['branch']);
}

Prepared Statement

We use prepared statement when we know that a same query will be executed multiple times with only change in parameters. For example, the INSERT query for a student table will always have the same template and only the insert data will change.

How to create a prepared statement?

We use the prepare() method to create a prepared statement. It is used to ready a query for execution.

$stmt = $pdocon->prepare($query);

How to execute a prepared statement?

We use the execute() method to execute a prepared statement. This method takes an array argument containing the placeholder and values. We will see that in the following example.

How to get the total number of affected rows?

To get the total number of affected rows we use rowCount() method.

UPDATE data using prepared statement

In the following example we execute an UPDATE query which has two placeholders :name and :studentid. When we execute the prepared statement we call the execute() method with an array containing the placeholders and values.

//query
$query = sprintf("UPDATE student SET name = :name WHERE studentid = :studentid");

//prepare the statement
$stmt = $pdocon->prepare($query);

//execute the prepared statement
$stmt->execute(array(
	":name" => "Alice",
	":studentid" => "s101"
));

//affected rows
printf("Updated Rows: %d", $stmt->rowCount());

Bind parameters

This is another way to assign value to the placeholders in the query. We use the bindParam() and pass the placeholder, value and the datatype.

Following are the list of some of the data types that can be passed to the bindParam() method.

• PDO::PARAM_BOOL for boolean data
• PDO::PARAM_INT for integer data
• PDO::PARAM_STR for string data
• PDO::PARAN_NULL for null data

$stmt->bindParam(":name", $name, PDO::PARAM_STR);

UPDATE data using the bind parameter

//query
$query = sprintf("UPDATE student SET name = :name WHERE studentid = :studentid");

//prepare the statement
$stmt = $pdocon->prepare($query);

//bind parameter
$name = "Alice";
$studentid = "101";
$stmt->bindParam(":name", $name, PDO::PARAM_STR);
$stmt->bindParam(":studentid", $studentid, PDO::PARAM_STR);

//execute the prepared statement
$stmt->execute();

//affected rows
printf("Updated Rows: %d", $stmt->rowCount());

Retrieve data

We can use two methods fetch() and fetchAll() to retrieve data.

In the following example we are using the fetch() methods to retrieve data from the student table. We are passing the argument PDO::FETCH_ASSOC to the fetch() method. This will make the method return result as an associative array.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute the query
$stmt = $pdocon->query($query);

//returned rows
printf("Selected Rows: %d", $stmt->rowCount());

//loop through the result
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	printf("StudentID: %s Name: %s Branch %s", $row['studentid'], $row['name'], $row['branch']);
}

In the following example we are using the fetchAll() method and passing the argument PDO::FETCH_ASSOC to get associative array as result.

//query
$query = sprintf("SELECT studentid, name, branch FROM student LIMIT 0,10");

//execute the query
$stmt = $pdocon->query($query);

//returned rows
printf("Selected Rows: %d", $stmt->rowCount());

//get the result
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//loop through the result
foreach ($result as $row) {
	printf("StudentID: %s Name: %s Branch %s", $row['studentid'], $row['name'], $row['branch']);
}

How to begin a transaction?

To begin a transaction we use beginTransaction() method.

$pdocon->beginTransaction();

How to commit a transaction?

To commit a transaction we use the commit() method.

$pdocon->commit();

How to rollback?

To perform rollback we use the rollback() method.

$pdo->rollback();