MySQLi functions to run a query and checking for errors occcured on last function call

After connecting database successfully, the next important task in an application is performing the query on the connected database. This article provides the details about how we can run a query to a MySQL database using mysqli extension. The article also provides the details about different useful functions which can be used to track the errors occurred with the query. The list and details of these functions are given below :

1. mysqli_query()

mysqli_query() function is used to perform a query on the database.

Result :  With non-DML statements such as SELECT, DESCRIBE, EXPLAIN etc., mysqli_query() will return a mysqli_result object on success of these queries. While with other queries such as INSERT, UPDATE, DELETE it will return TRUE on success and FALSE on failure.

Syntax :

1.1 Procedural Style : mysqli_query($con, $query, $result_mode) 

Description :

$con : MySQLi link identifier returned by mysqli_connect() or mysqli_init(). (Required)

$query : Represents the query statement (SQL statement).(Required)

$result_mode : Represent the result mode. (Optional)

$result_mode can be one of the following :

a. MYSQLI_STORE_RESULT : This mode is used as default.

b. MYSQLI_USE_RESULT : This mode is used for retrieving large amount of data.

Example :

<?php

$con = @mysqli_connect($host_name, $username, $password, $database);

/* check connection errors */
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

$query = "CREATE DATABASE demo";

//execute query
if (mysqli_query($con, $query)) {
    echo "Database created successfully";
} else {
    echo "Error in creating database";
}

mysqli_close($con);

?>

Output : Database created successfully

1.2 Object Oriented Style : $con->query($query, $result_mode)

$query and $result_mode parameters are same as defined above.

Example :

<?php

// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

if ($con->connect_errno) {
    die('Connect Error: ' . $con->connect_error);
}

$query = "CREATE DATABASE demo_db";

//ececute query
if ($con->query($query)) {
    echo "Database created successfully";
}

// close connection
$con->close();

?>

Output : Database created successfully

2. mysqli_errorno()

This function returns the error code for the last function call with respect to mysqli link whether it succeed or fail. If no error occurred, it returns 0.

Syntax :

2.1 Procedural Style : mysqli_errno($con)

$con : Specifies the MySQLi link identifier. (Required)

Example :

<?php

//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors 
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

$query = "CREATE DATABASE demo_db";

//execute query and check for error
if (!mysqli_query($con, $query)) {
    echo "Error Code :". mysqli_errno($con);
}

//close connection
mysqli_close($con);

?>

Output : Error Code :1007

2.2 Object Oriented Style : $con->errno

Example :

<?php

//create connection
$con = @new mysqli($host_name, $username, $password, $database);

//check connection error
if ($con->connect_errno) {
    die('Connect Error: ' . $con->connect_error);
}

$query = "CREATE DATABASE demo_db";

//ececute query and check error
if (!$con->query($query)) {
    echo "Error Code : ". $con->errno;
}

//close connection
$con->close();

?>

Output : Error Code :1007

3. mysqli_error()

The function mysqli_error() is similar to mysqli_errno(). The only difference is, it returns the error message instead of error code for the most recent function call.

Result : Returns a string if any error occurred on recent function call and if no error occurred, it returns an empty string ("").

Syntax :

3.1 Procedural Style : mysqli_error($con)

$con : Specifies the MySQLi link identifier. (Required)

Example :

<?php
//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors 
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

$query = "CREATE DATABASE demo_db";

//execute query and check errors
if (!mysqli_query($con, $query)) {
    echo "Error : ". mysqli_error($con);
}

// close connection
mysqli_close($con);
?>

Output : Error : Can't create database 'demo_db'; database exists

3.2 Object Oriented Style : $con->mysqli->error

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

//check connection errors
if ($con->connect_errno) {
    die('Connect Error: ' . $con->connect_error);
}

$query = "CREATE DATABASE demo_db";

//execute query and check error
if (!$con->query($query)) {
    echo "Error : ". $con->error;
}

// close connection
$con->close();
?>

Output : Error : Can't create database 'demo_db'; database exists

4. mysqli_error_list()

Like above two functions, this function also returns a list of errors in the last function call.

Result : Returns a list of errors as an associative array containing errno (error code), error (error statement) and sqlstate.

Syntax :

4.1 Procedural Style : mysqli_error_list ($con)

$con : Specifies the MySQLi link identifier.(Required)

Example :

<?php
//create connection
$con = @mysqli_connect($host_name, $username, $password, $database);

// check connection errors 
if (!$con) {
  die('Connect Error: ' . mysqli_connect_errno());
}

$query = "CREATE DATABASE demo_db";

//execute query and check error
if (!mysqli_query($con, $query)) {
    echo "<pre>";
    print_r(mysqli_error_list($con));
    echo "</pre>";
}

// close connection
mysqli_close($con);
?>

Output :

Array
(
    [0] => Array
        (
            [errno] => 1007
            [sqlstate] => HY000
            [error] => Can't create database 'demo_db'; database exists
        )
)

4.2 Object Oriented Style : $con->error_list

Example :

<?php
// Create connection
$con = @new mysqli($host_name, $username, $password, $database);

if ($con->connect_errno) {
    die('Connect Error: ' . $con->connect_error);
}

$query = "CREATE DATABASE demo_db";

//execute query and check error
if (!$con->query($query)) {
    echo "<pre>";
    print_r($con->error_list);
    echo "</pre>";
}

// close connection
$con->close();
?>

Output :

Array
(
    [0] => Array
        (
            [errno] => 1007
            [sqlstate] => HY000
            [error] => Can't create database 'demo_db'; database exists
        )
)