PHP MySQL Object Optimization

Question

I've been taking help from this site by years now, but I have never asked, so this is my first question here. This is a theoretic question, I would like to know if I'm thinking in the right way. First of all, sorry for my English.

I was thinking if I could simplify my existing MySQL object. For the connection, I use the singleton pattern to ensure that my app connect only one time during script execution. So, in every class, when I want to use MySQL, a get the instance.

    $db = db::getInstance();
    $result = $db->query("SELECT * FROM data;");

the $result is a dbResult class, on which I can use loops (while($row = $result->nextRow()) {...}), jump to row number, etc...

after all things are done, then I $result->free(); the result class.

Question 1.

Why not return an associative array instead of the dbResult class? I could use a foreach on it. But what about a result with 1.000.000 rows?

Question 2.

Do I have to get the instance every time I want to use SQL?

    class db{
    ...
    private static $instance;
    ...
    public static function query($_query){
        if (!self::$instance){ 
            self::$instance = new db(); //first we need to connect...
            self::query($_query);
        }
        else{
            //execute query, then load result in array, 
            //or in case of insert, return insert_id
            return $return_array;
        }
    }

In this case, I can simply call a query from anywhere in my code without instantiating the db object.

    $result = db::query("SELECT * FROM data;");
    ...
    //or insert
    db::query("INSERT INTO test VALUES ('test_value');");

Would be this a bad practice?

When you start writing unit tests, you will see how bad singletons / static methods are. Avoid that as much as possible. — CBergau, Jan 09 '14 at 20:23
I see. But which would be a good way to manage SQL functions in an MVC based application? — robi, Jan 09 '14 at 20:26
my advice is just forget static functions when you working with database. — gogagubi, Jan 09 '14 at 20:32
@Robert , you probably would benefit from applying [this approach](http://stackoverflow.com/a/11369679/727208) instead. — tereško, Jan 09 '14 at 20:59

score -1 · Answer 1 · answered Jan 09 '14 at 20:28

-1

Have a look at Doctrine 2 for example. It's a big project, but when u master it, its damn awesome :)

If this is too big for you, refactor your class to not use singleton pattern implementation and/or static methods.

answered Jan 09 '14 at 20:28

CBergau

636
2
10
26

score -1 · Answer 2 · answered Jan 09 '14 at 22:02

Here's what I suggest:

Create a Database class.

<?php
class Database {
    private $db;
    private $host;
    private $username;
    private $password;

    // Should probably not put your DB credentials here, just the Databases, but its just here for this purpose
    const DB_HOST = '';
    const DB_USERNAME = '';
    const DB_PASSWORD = '';

    // Just an example of having multiple databases, so you can just Database::DB_1 when you need to use them
    const DB_1 = '';
    const DB_2 = '';
    const DB_3 = '';


    public function __construct($db = self::DB_1, $host = self::DB_HOST, $username = self::DB_USERNAME, $password = self::DB_PASSWORD) {
        $this->db = $db;
        $this->host = $host;
        $this->username = $username;
        $this->password = $password; 
        $this->db = $db;
    }

    // So if you have different databases, you can create different functions to connect to them via PDO drivers
    public function connectToMySQL($db = null) {
        $dsn = is_string($db) ? 'mysql:dbname='.$db.';host='$this->host : 'msql:dbname='.$this->db.';host='$this->host;

        try {
            return new PDO($dsn, $this->username, $this->password);
        } catch (PDOException $E) {
            echo 'Connection error: '.$E->getMessage();
            exit();
        }
}

To use this you would just:

/*
 * Remeber how we defined it? you can leave the parameters blank, or pass in things
 */
$Database = new Database(); 
/* 
 * A PDO database object that connects to your database automaticaly.  
 * You can also passin Database::DB_2 sand stuff if you hav multiple databases.
 * is a PDO object, so to use it, just look up PHP's PDO tutorials
 */
$PDO = $Database->connectToMySQL();

/*
 *  Then to end the connection, it's just as simple as setting it to null
 */
$PDO = null;

This way, you create 1 Database object that can generate MANY connections, it's like a Factory class.

I believe this why is most versatile, but I'm always open to suggestions as well.

Have you ever heard about [open/closed principle](http://en.wikipedia.org/wiki/Open/closed_principle)? — tereško, Jan 10 '14 at 05:18

PHP MySQL Object Optimization

Question 1.

Question 2.

2 Answers2