Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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?

share|improve this question
 
When you start writing unit tests, you will see how bad singletons / static methods are. Avoid that as much as possible. –  CBergau 2 days ago
 
I see. But which would be a good way to manage SQL functions in an MVC based application? –  Robert 2 days ago
 
my advice is just forget static functions when you working with database. –  gogagubi 2 days ago
 
@Robert , you probably would benefit from applying this approach instead. –  tereško 2 days ago
add comment

2 Answers

up vote -1 down vote

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.

share|improve this answer
add comment
up vote -1 down vote

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.

share|improve this answer
 
Have you ever heard about open/closed principle? –  tereško 2 days ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.