How to Display Errors Using MySQLi Error Functions
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
string $mysqli->error;
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.
