如何使用工厂类查询数据库来提高效率

Question

I learned PHP with Robin Nixon's book and his examples used functions like this to query MySQL database

<?php
require 'config.php';
function queryMysql($query) {
    global $connection; // accessible from config.php
    $result = $connection->query($query);
    if (!$result) die($connection->error);
    return $result;
}
?>

config.php

<?php
$dbhost  = 'localhost';
$dbname  = 'database_name';
$dbuser  = 'database_username';
$dbpass  = 'database_password';

$connection = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($connection->connect_error) die($connection->connect_error);

// some other codes
?>

then this will normally follow anywhere in your code where you want to query the database $result=queryMysql($query); This is what I've been using all this while and I recently learnt that using a factory class is better and should save you stress as your website grows and also if you happen to later to use multiple types of databases.

I came across an article that suggested database should be queried this way (modified a bit to suit what am doing)

MYSQL class

<?php
class MYSQL
{
    private $host, $database_name, $username, $password;

    public function setHost($host) {
        $this->host = $host; // localhost
    }

    public function setDatabase($database_name) {
        $this->database_name = $database_name; // database name
    }

    public function setUsermame($username) {
        $this->username = $username; // db username
    }

    public function setPassword($password) {
        $this->password = $password; // db password
    }

    public function connect () {
        return $connection = new mysqli($this->host, $this->username, $this->password, $this->database_name);
        if ($connection->connect_error) 
            // die($connection->connect_error);
            false;
        return $connection;
    }
}
?>

FACTORY class

<?php
class ConnectionFactory
{
    private $driver = null;

    public function setDriver($driver) {
        $this->driver = $driver;
    }

    public function connect($host, $username, $password, $database_name) {
        switch($this->driver) {
            case 'mysql':
                $DB = new MYSQL();
            break;
            default:
                $DB = new MYSQL();
        }

        $DB->setHost($host);
        $DB->setDatabase($database_name);
        $DB->setUsermame($username);
        $DB->setPassword($password);
        return $DB->connect();
    }
}
?>

Now to make query

<?php
$db_factory = new ConnectionFactory();
$db_factory->setDriver('mysql');
$connection = $db_factory->connect('localhost', 'database_username', 'database_password', 'database_name');
if ($connection->connect_error) 
    die($connection->connect_error);

$query = "SELECT * FROM users";
$result = $connection->query($query);
if (!$result) 
    die($connection->error);
// echo '<pre>' , print_r($result), '</pre>';
?>

I want to know really, how does the factory class above improve call to the database. I read that it helps if you may want to connect to different databases in the future. If that's it, wouldn't it be same if I created different functions with names to specify what database they called like this

function queryMysql() {
    // code here
}
function queryPostgreSQL() {
    // code here
}

And with the factory class above, I feel (I know am wrong) it's more stressful accessing the database because it requires you to keep entering host, username, password and database name each time you try to access the database. What are the real advantages? Thanks

Answer 1

The goal of the Factory class isn't necessarily to improve efficiency -- rather it is to improve code legibility, organization and eliminate potential redundancy.

Specifically, you are creating a ConnectionFactory which allows you to create separate database factories. If you look in the class, you can handle multiple cases i.e. MySQL. For your scenario, the benefits might not be immediately apparent but imagine if you had to hook into different databases that required different drivers -- sure you could still do it but in a huge application it would be ridiculously confusing and impractical to maintain.

If you read the article, you can see they mention this where they mention how you could have an application that has to hook into MySQL, PostgresSQL, and/or SQLite. Specifically, they mention

Generally [specifying each config for each database] works but it creates problems in rather big applications. Above code has these problems:

• Should you add more database types, code would keep on growing making it complex
• The code is hard coded with database type names
• Whole process becomes tedious