php - Trying to build a static database class that I can access from any function outside of the class

Question

UPDATED: Changed code to the now working class code using PDO instead of deprecated methods

The original question was answered and the problem is solved. Phil brought up using PDO instead of traditional SQL so seeing as this class is in its infancy, I have decided to start the migration process.

class db
{
    private static $connection;
    const __DB__HOST__      = __DB__HOST__;
    const __DB_USERNAME__   = __DB_USERNAME__;
    const __DB_PASSWORD__   = __DB_PASSWORD__;
    const __DB_NAME__       = __DB_NAME__;


    private static function getConnection() {
        if (self::$connection === null) {

            $dsn = sprintf("mysql:dbname=".__DB_NAME__.";host=".__DB_HOST__, __DB_USERNAME__, __DB_PASSWORD__,"charset=utf8" );

            self::$connection = new PDO($dsn, self::__DB_USERNAME__, self::__DB_PASSWORD__, array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            ));
        }
        return self::$connection;
     }

    //Make the query
    private static function dbQuery($sql_string) {

        $conn = self::getConnection();

        $sth = $conn->prepare($sql_string);
        $sth->execute();

        return $sth;
    }

    //Return all results from sqlquery in array
    public static function dbDataArray($sql_string){

        $data = self::dbQuery($sql_string)->fetchAll();

        return $data;
    }

    public static function Value($sql_string){

        $data = self::dbQuery($sql_string)->fetchColumn();

        return $data;
    }
}

This is the progress of the class so far and it seems to be working great. Special Thanks to Phil for all the support.

Any other recommendations are welcome and will be greatly appreciated.

Thanks again.

---

Original question

Basically I am trying to build a db access class that I can use with the following syntax

$test = db::dbDataArray("SELECT * FROM fw_settings");

The class will create the connection if one is not present so I can just call the methods from the class anytime without needing to create a new instance.

Here is what I have so far for the structure with a single function. Once I can get the status class to work on a single query item I will add all the other functions.

class db
{
    public $connection;
    const __DB__HOST__      = __DB__HOST__;
    const __DB_USERNAME__   = __DB_USERNAME__;
    const __DB_PASSWORD__   = __DB_PASSWORD__;
    const __DB_NAME__       = __DB_NAME__;

    function __construct() {

        if (self::$connection == null){

             self::$connection = mysql_connect(__DB_HOST__,__DB_USERNAME__,__DB_PASSWORD__)
                or die('Unable to Connect to SQL Host'.__DB_HOST__);
             @mysql_select_db(__DB_NAME__)
                or die('Unable to Select DB: '.__DB_NAME__);
        }
    }

    //Regular SQL query such as delete, update, etc.
    public static function dbQuery($sql_string) {

        //Handle the rest of the SQL query here
        if (!$sqlResult = mysql_query($sql_string, self::$connection) ){
            //Let's output the MySQL error if we have one
            die('
                <div style="border:1px solid red;color:red;background:yellow;">
                    <p>'.$sql_string.'</p>
                    SQL Error:'. mysql_error(self::$connection).'
                </div>'
            );

        }
        else {
            //Return the sql result
            if (strpos($sql_string, "DELETE")){
                if (mysql_affected_rows($sqlResult) > 0){
                    return $sqlResult;
                }
                else{
                    return false;
                }
            }
            else{
                return $sqlResult;
            }
        }
    }

    //Return all results from sqlquery in array
    public static function dbDataArray($sql_string){

        $data = array();
        $dataQuery = self::dbQuery($sql_string);
        $i = 1;

        while(($row = mysql_fetch_assoc($dataQuery)) !== false){
            foreach($row AS $key=>$val){
                $data[$i][$key] = $val;
            }
            $i++;
        }
        return $data;
    }
}

The constants are defined in another file that is loaded before the class, so they will be present.

The error that I am getting right now is as follows.

Fatal error: Access to undeclared static property: db::$connection

It looks like it is making the connection fine. I just can't access the connection using the dbQuery function. The dbQuery function is used in all the other functions.

This is just the start of the class, and it is based on a group of functions I was already using.

The ultimate goal once it is working is to be able to pass a database name to the class and access that database in that specific instance, so I can use multiple databases in the projects I am planning to use this in.

Answer 1

In order for your class to operate statically, you need to do a few things.

First, make the connection static, eg

private static $connection;

Secondly, why all the underscores?

define('DB_HOST', 'localhost');
define('DB_NAME', 'your_db_name');
define('DB_USER', 'username');
define('DB_PASS', 'password');

Also, why use class constants at all? Just use the constants you've already defined.

Third, lose the constructor. You cannot expect to create an instance of this class and use it statically. I would go for a lazy-load approach for the connection

private static function getConnection() {
    if (self::$connection === null) {
        $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8',
            DB_HOST, DB_NAME);

        self::$connection = new PDO($dsn, DB_USER, DB_PASS, array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ));
    }
    return self::$connection;
}

Then, your public methods would call this method internally. I'll also flesh out your dbDataArray method to show you how to return an associative array

public static function dbDataArray($query, $params = array()) {
    $stmt = self::getConnection()->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}