Как включить вывод ошибок mysql

Приветствую всех читателей!  Сегодня я хочу вам рассказать про функции вывода ошибок, которые используются в MySQLi. Бывают такие моменты, что запрос к базе завершается ошибкой, и ошибка выводится на экран не в обычном виде. То есть ошибка ссылается просто на строку в файле php, в котором происходит этот запрос к базе. Вот как раз для вывода нормальной информации об ошибках и используются функции MySQLi.

И так приступим.

За предоставление информации об ошибках, в MySQLi используются функции mysqli_errno и mysqli_error. Но эти функции не используются для получения информации об ошибках, возникших в функции соединения с базой данных. То есть эти функции можно использовать только для выявления ошибок, которые возникают в выполнении запросов к базе, больше на http://makarou.com/mysqli-%E2%80%93-novaya-versiya-rasshireniya-mysql.

Синтаксис этих функций выглядит следующим образом:

mysqli_error($load);
mysqli_errno($load);

Как можно заметить, функции принимают только одно значение – это идентификатор соединения с базой данных. Функция mysqli_error – показывает запрос, который завершился ошибкой. А функция mysqli_errno – показывает код ошибки.

Если ошибка возникает в функции подключения к базе данных, то в MySQLi за это отвечают функции mysqli_connect_errno и mysqli_connect_error.

Синтаксис этих функций выглядит вот так:

mysqli_connect_errno();
mysqli_connect_error();

Эти функции не принимают параметров вообще.  Функция mysqli_connect_errno – показывает код ошибки. А функция mysqli_connect_error – показывают информацию об ошибке.

Вот небольшой пример как пользоваться этими функциями:

$load=mysqli_connect("localhost","username","pass","dbname", 3306);
if(!$load){
echo' Ошибка подключения к БД: '.mysqli_connect_error().' Код ошибки:'.mysqli_connect_errno();
exit;
}
$sql=mysqli_query("SELECT username FROM accaunt WHERE id='1'");
if(!$sql){
echo'Ошибка запроса: '.mysqli_error($load).' Код ошибки: '.mysqli_errno($load);
exit;
}
$result=mysqli_fetch_array($sql);
echo $result['username'];

Вот и все, что нужно знать о выводе ошибок. Ничего тут сложного даже и нет. Я всегда пользуюсь этими функциями при разработке проектов, так как это очень удобно. Сразу понимаешь, что и где случилось. На сегодня все.

Постовой

При ранжировании сайтов, поисковая система Google учитывает один фактор доступности сайта. Скорость отклика сайта зависит от расположения ДНС сервера и хостинг сайта. Можно смело заявить, что платное размещение сайтов, является неотъемлемой частью поисковой оптимизации.

php

1049: Unknown database 'nonexistentdb'
1146: Table 'kossu.nonexistenttable' doesn't exist

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

Really, it drive me crazy when I can’t detect what error that happens. I can’t handle it.

I managed to make a connection to MySQL, and check it out with:

$connection = mysqli_connect(HOST, USER, PASS, DB) or die('Could not connect!');
if($connection){
    echo 'It's connected!';
}

Yeah, that say connected. Then, when I try a query, it fails without error reporting. I’ve tried do this to check if it fails:

$query = "SELECT $field FROM users WHERE id = ".$_SESSION['user_id'];
$result = mysqli_query($dbc, $query);
if($result){
    echo 'Query OK';
}else{
    echo 'Query failed';
}

The browser said: Query failed. So, there’s an error in my query. Then I echoed the query out with this:

echo $query;
// Printed in the browser: SELECT firstname FROM users WHERE id = 1

Copy that value and use it in phpMyAdmin. It works. So, i guess an error occured in mysqli_query function. But i can’t get the error message and so i don’t know what’s going on. I’ve tried this:

$result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

and this:

if(!$result){
     echo mysqli_error($dbc);
}

Nothing happens. The browser just blank. Then, I tried to change this:

$result = mysqli_query($dbc, $query);

to this:

$result = mysqli_query($query);

Still nothing happens. What’s going on? How can I know what error occured?

I run the server in Debian with phpinfo(): PHP Version 5.4.36-0+deb7u3

1. PHP MySQLi Error Functions
2. Conclusion

MySQLi is a PHP function used to access the MySQL database server. You can use this extension if you have MySQL version 4.1.13 or above.

There are various MySQLi functions that you can use to perform different functions in PHP. In this article, we will learn MySQLi error functions.

We will also see how and where to use code examples and observe the outputs. For this tutorial, we will use MySQL version 8.0.27 and PHP version 7.4.1.

PHP MySQLi Error Functions

In this tutorial, we will learn about the following PHP MySQLi error functions:

• mysqli_error()
• mysqli_errno()
• mysqli_error_list()
• mysqli_connect_error()
• mysqli_connect_errno()

All these functions can be used in object-oriented style and procedural style. Let’s understand both syntax using the mysqli_error() function.

Syntax of mysqli_error() Function in Object Oriented Style

Syntax of mysqli_error() Function in Procedural Style

string mysqli_error ( mysqli $link )

mysqli_error() Function in MySQL

This function is used to output the last error description for the most recent function call (if there is any). It is used when you want to know whether the SQL query has an error or not.

mysqli_error() returns the error description and empty string if there is no error. See the following example.

Example Code Using Procedural Style:

<?php
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "person";

    $connection = mysqli_connect($host, $username, $password, $database) 
    or die("Connection Failed"); 

    $sql = "SELECT * FROM teacher";
    $result = mysqli_query($connection, $sql);
    $error_message = mysqli_error($connection);

    if($error_message == ""){
        echo "No error related to SQL query.";
    }else{
        echo "Query Failed: ".$error_message;
    }
    mysqli_close($connection);
?>

The code given above tries to make the connection using $host, $username, $password, $database variables and save this connection into the $connection variable.

mysqli_error() function will take this connection variable $connection as a parameter and check if there is any error caused by the recent MySQLi function call which is mysqli_query($connection, $sql) here.

Output:

Now, change the table name in the SQL query from teacher to person and observe the output given below.

Output:

We, as a developer, can easily understand that there is no person table in the person database (this is what it means in the above error).

Keep the table name changed and replace the line $error_message = mysqli_error($connection); with $error_message = $connection->error; to practice and understand the object oriented style using MySQLi error function.

mysqli_errno() Function in MySQL

mysqli_errno() works the same as mysqli_error() does, but it will return the error code instead of the error description.

Write the following code to practice and understand. You may have noticed that we use a procedural style to practice this function.

<?php
     $host = "localhost";
     $username = "root";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed"); 

     $sql = "SELECT * FROM person";
     $result = mysqli_query($connection, $sql);
     $error_message = mysqli_errno($connection);

     if($error_message == ""){
     	echo "No error related to SQL query.";
     }else{
     	echo "Query Failed: ".$error_message;
     }
    mysqli_close($connection);
?>

The code given above will show the following output where you will see a number as an error code.

Output:

The question is, why do we use this function to show the numbers only? Because if you want to print a user-friendly error message (custom message), you can use this error code in if-else statements.

See the following code and its output below.

<?php
     $host = "localhost";
     $username = "root";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed"); 

     $sql = "SELECT * FROM person";
     $result = mysqli_query($connection, $sql);
     $error_message = mysqli_errno($connection);

     if($error_message == 1146){
     	echo "You are trying to read the data from a table which doesn't exist in your 			database "."'".$database."'";
     }
    mysqli_close($connection);
?>

Output:

mysqli_error_list() Function in MySQL

This function is very useful for knowing the error code, SQL state, and error description because this function returns an array containing all the necessary information.

Example Code:

<?php
     $host = "localhost";
     $username = "root";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed"); 

     $sql = "SELECT * FROM person";
     $result = mysqli_query($connection, $sql);
     print_r(mysqli_error_list($connection));
     mysqli_close($connection);
?>

Output:

mysqli_connect_error() Function in MySQL

mysqli_connect_error() returns the error description from the last connection if there is any. Although, the die() function also tell about the unsuccessful connection but mysqli_connect_error() returns the error that we can understand easily.

Write the following code first, see its output, and then we’ll compare it with the output produced by mysqli_connect_error().

<?php
     $host = "localhost";
     $username = "root";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed"); 

     $sql = "SELECT * FROM person";
     $result = mysqli_query($connection, $sql);
     $error_message = mysqli_error($connection);

     if($error_message != ""){
     	echo "Query Failed: ".$error_message;
      }
    mysqli_close($connection);
?>

Output:

See the output given above; you can see that the error we can understand is somewhere in the middle.

Imagine, if you have 2 or 3 errors, it would not be easy to find out. Now, use the mysqli_connect_error() and see the difference using the following code and output.

<?php
     $host = "localhost";
     $username = "newroot";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed: ".mysqli_connect_error()); 

     $sql = "SELECT * FROM teacher";
     $result = mysqli_query($connection, $sql);
     $error_message = mysqli_error($connection);

     if($error_message != ""){
     	echo "SQL Query Failed: ".$error_message;
    }
    mysqli_close($connection);
?>

Output:

The above output clearly says that there is no user named newroot, which does not allow you to access the database.

mysqli_connect_errno() Function in MySQL

This function behaves like mysqli_connect_error() but displays the error code rather than the error message. We can use this error code to write custom error messages.

Example Code:

<?php
     $host = "localhost";
     $username = "newroot";
     $password = "";
     $database = "person";

     $connection = mysqli_connect($host, $username, $password, $database) 
     or die("Connection Failed: ".mysqli_connect_errno()); 

     $sql = "SELECT * FROM teacher";
     $result = mysqli_query($connection, $sql);
     $error_message = mysqli_error($connection);

     if($error_message != ""){
	     echo "SQL Query Failed: ".$error_message;
     }
     mysqli_close($connection);
?>

Output:

Conclusion

Considering all the discussion and examples, we have concluded two main categories. The first category shows the errors about SQL queries and the other about database connections.

Depending on the project needs, we can print the error message or the error code in each category.

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