How to check connection errors and change default database in MySQLi

My previous articles titled Connecting MySQL database with MySQLi Object Oriented style and How to connect MySQL database using the MySQLi procedural function demonstrate about different ways to connect to a MySQL database with PHP application. Those articles define the way through which an application connects to the database, but MySQLi also provides some other very useful function which can be used to check connection errors or to change the default database for performing queries. This article defines the list of these functions :

1. mysqli_connect_errorno() :

This function returns the error code occurred on the last connection call made by using mysqli_connect() function.

Result : The function returns an error code (a number) on failure, otherwise it returns 0 on successful connection.

Syntax :

1.1 Procedural Style : mysqli_connect_errno()

Example :

<?php
$con = mysqli_connect('hostname', 'username', 'password', 'dbname');

if (!$con) {
    die('Connection Error: ' . mysqli_connect_errno());
}
?>

Output : Connect Error: 1045

1.2 Object Oriented Style : $con->$connect_errno

Example :

<?php
$con = @new mysqli('hostname', 'username', 'password', 'dbname');

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

Output : Connect Error: 1045

 

2. mysqli_connect_error() :

This function is similar to mysqli_connect_errorno(), the only difference is, it returns the error description in the form of a string instead of error code for the last connection call made by mysqli_connect().

Result : This function returns error description, otherwise it returns NULL on successful connection.

Syntax :

2.1 Procedural Style : mysqli_connect_error ()

Example :

<?php
$con = @mysqli_connect('hostname', 'username', 'password', 'dbname');

if (!$con) {
    die('Connection Error: ' . mysqli_connect_error());
}
?>

Output : Connect Error: Access denied for user 'root'@'localhost' (using password: YES)

2.2 Object Oriented Style : $con->connect_error

Example :

<?php
$con = @new mysqli('hostname', 'username', 'password', 'dbname');
if ($con->connect_error) {
    die('Connection Error: ' . $con->connect_error);
}
?>

Output : Connect Error: Access denied for user 'root'@'localhost' (using password: YES)

Suggestion : It's better to use mysqli_connect_error() with PHP versions earlier to 5.2.9 and 5.3.0 to avoid compatibility issues.

Note : For mysqli_connect_errorno() and mysqli_connect_error(), I have used wrong password to check errors.

 

3. mysqli_select_db() :

This function is used to change the default database. It can also used to set default database, if it's not set using mysqli_connect().
 

Result :  This function returns True on success and False on failure.

Syntax :

3.1 Procedural Style : mysqli_select_db ($con, $dbname )

Description :

$con : MySQLi link identifier.(Required)

$dbname : Specifies database name.(Required)

Example :

<?php
$con = mysqli_connect("hostname", "username", "password", "dbname");

/* check connection */
if (!$con) {
    die('Connection Error: ' . mysqli_connect_error());
    }

/* print default database name */
if ($result1 = mysqli_query($con, "SELECT DATABASE()")) {
    $row = mysqli_fetch_row($result1);
    echo "Current Database : ".$row[0];
    echo "<br>";
}

/* select another database */
mysqli_select_db($con, "demo");

if ($result2 = mysqli_query($con, "SELECT DATABASE()")) {
    $row = mysqli_fetch_row($result2);
    echo "Current Database : ".$row[0];
}

?>

Output : Current Database : test
Current Database : demo

3.2 Object Oriented Style : $con->select_db($dbname)

$dbname: Specifies the name of database.

Example :

<?php
$con = new mysqli("hostname", "username", "password", "dbname");

/* check for connection */
if ($con->connect_errno) {
    die('Connection Error: ' . $con->connect_errno);
}

/* print default database name */
if ($result1 = $con->query("SELECT DATABASE()")) {
    $row = $result1->fetch_row();
    echo "Current Database :".$row[0];
    echo "<br>";
}

/* change database */
$con->select_db("demo");

if ($result2 = $con->query("SELECT DATABASE()")) {
    $row = $result2->fetch_row();
    echo "Current Database :".$row[0];
}

?>

Output : Current Database : test
Current Database : demo.

 

4. mysqli_close() :

This function is used to close a previously opened database connection.

Return : It returns TRUE on success and FALSE on failure.

Syntax :

4.1 Procedural Style : mysqli_close($con)

$con : Specifies MySQLi link.(Required)

Example :

<?php
$con = mysqli_connect("hostname", "username", "password", "dbname");
/*
* PHP code
*/
//---to close connection---
mysqli_close($con);
?>

4.2 Object Oriented Style : $con->close();

Example :

<?php
//--create connection---
$con = @new mysqli($host_name, $username, $password, $database);
/*
* PHP code
*/

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