Print mysqli error

mysqli_stmt::$error

(PHP 5, PHP 7, PHP

mysqli_stmt::$error — mysqli_stmt_error — Returns a string description for last statement error

Description

Returns a string containing the error message for the most recently invoked statement function that can succeed or fail.

Parameters

Procedural style only: A mysqli_stmt object returned by mysqli_stmt_init() .

Return Values

A string that describes the error. An empty string if no error occurred.

Examples

Example #1 Object-oriented style

/* Open a connection */
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

$mysqli -> query ( «CREATE TABLE myCountry LIKE Country» );
$mysqli -> query ( «INSERT INTO myCountry SELECT * FROM Country» );

$query = «SELECT Name, Code FROM myCountry ORDER BY Name» ;
if ( $stmt = $mysqli -> prepare ( $query )) <

/* drop table */
$mysqli -> query ( «DROP TABLE myCountry» );

/* execute query */
$stmt -> execute ();

printf ( «Error: %s.n» , $stmt -> error );

/* close statement */
$stmt -> close ();
>

/* close connection */
$mysqli -> close ();
?>

Example #2 Procedural style

/* Open a connection */
$link = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

mysqli_query ( $link , «CREATE TABLE myCountry LIKE Country» );
mysqli_query ( $link , «INSERT INTO myCountry SELECT * FROM Country» );

$query = «SELECT Name, Code FROM myCountry ORDER BY Name» ;
if ( $stmt = mysqli_prepare ( $link , $query )) <

/* drop table */
mysqli_query ( $link , «DROP TABLE myCountry» );

/* execute query */
mysqli_stmt_execute ( $stmt );

printf ( «Error: %s.n» , mysqli_stmt_error ( $stmt ));

/* close statement */
mysqli_stmt_close ( $stmt );
>

/* close connection */
mysqli_close ( $link );
?>

The above examples will output:

See Also

• mysqli_stmt_errno() — Returns the error code for the most recent statement call
• mysqli_stmt_sqlstate() — Returns SQLSTATE error from previous statement operation

Источник

mysqli::$error_list

(PHP 5 >= 5.4.0, PHP 7, PHP

mysqli::$error_list — mysqli_error_list — Returns a list of errors from the last command executed

Description

Returns a array of errors for the most recent MySQLi function call that can succeed or fail.

Parameters

Procedural style only: A mysqli object returned by mysqli_connect() or mysqli_init()

Return Values

A list of errors, each as an associative array containing the errno, error, and sqlstate.

Examples

Example #1 $mysqli->error_list example

= new mysqli ( «localhost» , «nobody» , «» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

if (! $mysqli -> query ( «SET a=1» )) <
print_r ( $mysqli -> error_list );
>

/* close connection */
$mysqli -> close ();
?>

= mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

if (! mysqli_query ( $link , «SET a=1» )) <
print_r ( mysqli_error_list ( $link ));
>

/* close connection */
mysqli_close ( $link );
?>

The above examples will output:

See Also

• mysqli_connect_errno() — Returns the error code from last connect call
• mysqli_connect_error() — Returns a description of the last connection error
• mysqli_error() — Returns a string description of the last error
• mysqli_sqlstate() — Returns the SQLSTATE error from previous MySQL operation

Источник

PHP MySQL query with error printing

How to write SQL using PHP to handle the data in MySQL database? In any database driven script we have to update, add, modify, data in the tables. By using PHP we can do all this using different functions available in PHP. We will start with very basic function, which will execute any query written in sql and can be applied to MySQL database.

Structured Query Language or popularly known as SQL is an universal language to handle database. An introduction and different types of sql command like select, insert, update etc you will get in the sql section of this site. There are some advance SQL commands like left join, linking of tables etc to study. If you are not comfortable with SQL any time you can refer the materials in sql section.

There are three steps involved in this process.

1. Connection to database
2. Build the query and execute
3. Display the data

Connecting database and executing Query

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

PHP Functions & SQL

Let us start with the function required to execute one query in PHP. Once you have connection established then we can execute sql command by using PHP function mysql_query(). Here is the syntax of the function.

Let us first write the query and store in a variable. We will write a query to create table. We have stored the sql create query in a variable $query and we will pass this as a parameter to the function like below. The above command will execute php_mysqli() the query ( stored in variable $query) and we can check the status of the query ( successful or not ) by checking the value of the variable $rt. $rt will be true if the query is successfully executed or it will return false. We will use php if command to check the status of the query.
So from the above line we can know that the query has worked or failed. But we will not come to know about the error if the database has some error and the query has failed. To get the error message we have to use another function mysqli_error() to print the error message returned by MySQL database after executing the query. Here it is how to print the error message. The above line will print the error returned by mysql database if the query fails to execute. You can read more on mysql error here.

The complete code is available below. Before executing the above code we must connect to mysql database by using mysqli connection string.

Источник

Error message displaying after execution of a query

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. In our student table there is no field as new_field. Here here is the message we will get

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.

In the above code there is an error in sql , there is no column by name no_name. The output will be So to get correct result change the sql part like this . 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 Here is the complete code. 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.

Источник

mysqli::$error — Возвращает строку с описанием последней ошибки

mysqli_error

mysqli::$error — mysqli_error — Возвращает строку с описанием последней ошибки

Описание

Возвращает сообщение об ошибке последнего вызова функции MySQLi, который может успешно выполниться или провалиться.

Список параметров

Только для процедурного стиля: Идентификатор соединения, полученный с помощью mysqli_connect() или mysqli_init()

Возвращаемые значения

Строка с описанием ошибки. Пустая строка, если ошибки нет.

Примеры

Пример #1 Пример с $mysqli->error

= new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( $mysqli -> connect_errno ) <
printf ( «Connect failed: %sn» , $mysqli -> connect_error );
exit();
>

if (! $mysqli -> query ( «SET a=1» )) <
printf ( «Errormessage: %sn» , $mysqli -> error );
>

/* close connection */
$mysqli -> close ();
?>

= mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

/* check connection */
if ( mysqli_connect_errno ()) <
printf ( «Connect failed: %sn» , mysqli_connect_error ());
exit();
>

if (! mysqli_query ( $link , «SET a=1» )) <
printf ( «Errormessage: %sn» , mysqli_error ( $link ));
>

/* close connection */
mysqli_close ( $link );
?>

Результат выполнения данных примеров:

Смотрите также

• mysqli_connect_errno() — Возвращает код ошибки последней попытки соединения
• mysqli_connect_error() — Возвращает описание последней ошибки подключения
• mysqli_errno() — Возвращает код ошибки последнего вызова функции
• mysqli_sqlstate() — Возвращает код состояния SQLSTATE последней MySQL операции

Источник

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

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.