How to test MySQL connection in PHP and Laravel?

Question

I'm making a PHP application installer (something like Wordpress installation script) and I need to check mysql connection using host name, username, password and database provided by user during installation.

I'm using this code as a Laravel controller method to test connection:

public function TestDatabaseConnection(){
    try {
        $database_host = Config::get('config.database_host');
        $database_name = Config::get('config.database_name');
        $database_user = Config::get('config.database_user');
        $database_password = Config::get('config.database_password');

        $connection = mysqli_connect($database_host,$database_user,$database_password,$database_name);

        if (mysqli_connect_errno()){
                return false;
            } else {
                return true;
            }

    } catch (Exception $e) {

        return false;

    }
}

This code doesn't seem to properly test the connection. Function return value (true/false) doesn't depend whether user supplies db data at all, or if db data is correct/incorrect..

Fils /app/config/config.php contains the following array:

<?php return array('database_host' => 'localhost', 'database_name' => 'dbasename',    'database_user' => 'dbuser', 'database_password' => 'pass');

and it's being updated via form during installation process.

Is there any way to modify this code or maybe you have some other code suggestions?

Answer 1

Your question is:

How to test MySQL connection in PHP and Laravel?

But then you are setting up a standard PHP MySQLi connection like this:

$connection = mysqli_connect($database_host,$database_user,$database_password,$database_name);

Why would you do that? The whole purpose of using a framework is to work within the framework. And something that encompasses these two basic systems concepts:

• Read a configuration file.
• Establish a database connection.

Doing those things is something that pretty much every capable—and widely adopted—programming framework should be able to handle within it’s own structure & using it’s own methods.

So that said, looking at the Laravel documentation on “Basic Database Usage” shows the following. This is placed in your DB configuration file located in app/config/database.php.:

'mysql' => array(
    'read' => array(
        'host' => '192.168.1.1',
    ),
    'write' => array(
        'host' => '196.168.1.2'
    ),
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
),

The example has two distinct DB connections: One for read and the other for write, but that is not how most DB connections for simple projects work. So you can set this instead also using your settings:

'mysql' => array(
    'host'      => Config::get('config.database_host'),
    'driver'    => 'mysql',
    'database'  => Config::get('config.database_name'),
    'username'  => Config::get('config.database_user'),
    'password'  => Config::get('config.database_password'),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
),

Then to test that connection, you would just do this:

if(DB::connection()->getDatabaseName())
{
   echo "Yes! successfully connected to the DB: " . DB::connection()->getDatabaseName();
}

But that said you are also saying:

I'm making a PHP application installer…

Why reinvent the wheel when PHP build systems such as Phing exist?

Answer 2

You can simply check whether the connection is made or not using this:

if(DB::connection()) {
    // connection is made
}

Because you don't need to make connection manually. If the user provided right credentials in the app/config/database.php then the user will be able to query in the database but if you need to check the connection then given code above is able to check because if the connection is not made then an error will be thrown and on a valid connection the Illuminate\Database\MySqlConnection object will be returned. So, in this case it's also possible to use:

if(DB::connection() instanceof Illuminate\Database\MySqlConnection) {
    // connection is made
}

So, according to your example of TestDatabaseConnection method you can do something like this:

public function TestDatabaseConnection(){
    // Returns Illuminate\Database\MySqlConnection on successful
    // connection; otherwise an exception would be thrown if failed
    return DB::connection();
}

Answer 3

If you really want to catch the error of laravel db connection failure, you can define this:

 App::error(function(PDOException $exception, $code)
 {
     die('do what you want here');
 });

I defined it inside:

/app/start/global.php

you can define it where ever you like.