Small PHP class for rapid MySQL development with prepared statements

In this small code example I show you how I made a class in PHP that handles communication with a MySQL database using PDO and prepared statements.

/**
 * DBHandler class
 * Communicates with a mysql database.
 */
class DBHandler
{
    /** @var object $pdo PHP Data Object (Database connection object). */
    private static $pdo = null;

    /** @var array $ps Prepared statements array. */
    private static $ps = array();

    /**
     * getPDO method
     *
     * @return object Returns a static database connection object.
     */
    // PHP closes MySql connection automatically, use self::$pdo = null; to do it explicitly
    protected static function getPDO()
    {
        if(self::$pdo == null)
        {
            self::$pdo = new PDO('mysql:host=localhost; dbname=dbname; charset=utf8', 'username', 'password');
        }
        return self::$pdo;
    }

    /**
     * getPS method
     *
     * @param string $name Name of the PDOStatement.
     * @return object Returns a PDOStatement object from the predifined array.
     */
    protected static function getPS($name)
    {
        if(self::$ps == null)
        {
            self::$ps = array(

                // prepared select statements (PDOStatement)
                "stmt1" => self::getPDO()->prepare("select 'hello';"),        // select the word hello

                "stmt2" => self::getPDO()->prepare("select 'world!';"),        // select the word world!

            );

        }
        if(!isset($name) || !is_string($name))
        {
            throw new Exception("PDOStatement name invalid.");
        }
        return self::$ps[$name];
    }

    /**
     * executePreparedStatement method
     *
     * @return array Returns an associative array, the result of the database query.
     */
    public static function executePreparedStatement($stname, $params = array())
    {
        self::getPS($stname)->execute($params);

        return self::getPS($stname)->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * executeQuery method
     *
     * @param string $query A database query.
     * @return array Returns an associative array, the result of the database query.
     */
    // escape $query properly!!!
    // ca!!
    public static function executeQuery($query)
    {
        return self::getPDO()->query($query)->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * checkResult method
     *
     * @param string $result A database query result to be examined.
     * @throws Exception if the result is empty
     */
    public static function checkResult($result)
    {
        if(!isset($result) || ($result === false))
        {
            throw new Exception("Mysql return value false.");
        }
    }

}

Leave a Reply

Your email address will not be published. Required fields are marked *