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();
?>

How to connect MySQL database using MySQLi procedural function

Hello friends, my previous article "MySQLi - An improved and enhanced MySQL extension" provides an introduction about the MySQLi extension and its features. This article demonstrates how to create connection with a MySQL database using MySQLi extension. MySQLi provides two interfaces (Procedural and Object Oriented) for connecting and interacting with MySQL, this article demonstrate only about the procedural way to connect to MySQL server.

For connecting to MySQL server MySQLi extension provides mysqli_connect() function. The detail about mysqli_connect() is given below :

mysqli_connect()

mysqli_connect() is used to create a connection to MySQL server. On successful connection, it returns an object which represents connection for other mysqli_* functions and FALSE on failure.

Syntax : mysqli_connect(host_name, username, password, db_name, port, socket);

Description :

host_name : Specifies a host name or an IP address of MySQL server.
username : Specifies the MySQL Username
password : Specifies the MySQL Password
db_name : Specifies the default database to be used
port : Specifies the port number to connect to the MySQL server
socket : Specifies the socket name or named pipe

Description :

The mysqli_connect() function create a connection to MySQL Server specified by host_name. It can be either a host name or an IP address. If connection created successfully, the mysqli_connect() function returns an object which represents the connection to the database, and on failure function return FALSE.

The username and password parameters are used for connecting to the MySQL server. If the password field, leave blank or NULL, then server authenticates the user within those user records for which password is not set.

db_name parameter specifies the default database to be used for executing further queries.

The port parameter used to open the connection on specified port number, while the socket parameter specifies the name of socket or named pipe.

Below is the script to connect to MySQL server using mysqli_connect() :

<?php
$host_name = "localhost";
$username = "username";
$password = "password";
$database = "test";//database name

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

// Check connection
if (!$con) {
echo "Error: " . mysqli_connect_error(); die;
}
echo "Connection to database : ".$database." created successfully on server : ".mysqli_get_host_info($con);
?>

Output : Connection to database : test created successfully on server : Localhost via UNIX socket