How to work with php classes and objects to communicate with mysql database - beginner in oop

Question

I started learning resources from the web on object oriented programming not quite long. I have an application am working on that needs that bit of programming ability to protect against sql injections, session hijacking and brute force attacks, thereby, in the process capturing every possible information on who uses or attempted access to the application and where such activity was held. I have almost completed the application now with old fashioned php programming which I learnt on my own too. But the added functionality whereby the administrator of the application would have a tool to check the above information where necessary requires a good touch of OOP.

Here is my class file codes;

class Database 
{
    private $host = DB_HOST;
    private $user = DB_USER;
    private $pass = DB_PASS;
    private $dbname = DB_NAME;
    private $dbh;
    private $error;
    private $stmt;


    public function __construct()
    {
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        $options = array(
            PDO::ATTR_PERSISTENT => true, 
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8"
        );

        try {
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
        }
    }

    public function query($query)
    {
        $this->stmt = $this->dbh->prepare($query);
    }

    public function bind($param, $value, $type = null)
    {
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($param, $value, $type);
    }

    public function execute()
    {
        return $this->stmt->execute();
    }

    public function resultset()
    {
        $this->execute();
        return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function single()
    {
        $this->execute();
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function rowCount()
    {
        return $this->stmt->rowCount();
    }

    public function lastInsertId()
    {
        return $this->dbh->lastInsertId();
    }

    public function beginTransaction()
    {
        return $this->dbh->beginTransaction();
    }

    public function endTransaction()
    {
        return $this->dbh->commit();
    }

    public function cancelTransaction()
    {
        return $this->dbh->rollBack();
    }

    public function debugDumpParams()
    {
        return $this->stmt->debugDumpParams();
    }
}

Then I have a test page for the database connection, created the database and attached a username and password to it. Now the details are well entered in the php page that includes the class below like this;

require_once 'database.class.php';

define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB_NAME", "dbname");

$database = new Database();

$database->query("CREATE TABLE mytable (
ID int(11) NOT NULL AUTO_INCREMENT,
FName varchar(50) NOT NULL,
LName varchar(50) NOT NULL,
Age int(11) NOT NULL,
Gender enum('male','female') NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ");

$database->query('INSERT INTO mytable (FName, LName, Age, Gender)      VALUES (:fname, :lname, :age, :gender)');

$database->bind(':fname', 'John');
$database->bind(':lname', 'Smith');
$database->bind(':age', '24');
$database->bind(':gender', 'male');

$database->execute();

echo $database->lastInsertId();

But it throws the following error;

Fatal error: Call to a member function prepare() on null in C:\xampp\htdocs\folder\folder1\folder2\database.class.php on line 38.

Please, I need to know how I can use classes to perform operations in my database, so far, I conveniently achieve database connection and communication using MySQL_connect($dbserver, $dbroot, $dbpwd), MySQL_select_db($dbname,$query), MySQL_query("Statement"). Please I need a quick solution, as am currently carrying out more study and research on object oriented programming, but my project has to be finished on time. Thanks

Answer 1

Ok man, this should be the base class to make your database connection. Keep this as a separate file. From here you can write your custom classes for specific interactions you may want to do.

This uses PDO Prepared statements so it's close to bulletproof for sql injections.

<?php

/**
 * Class DatabaseFactory
 *
 * Use it like this:
 * $database = DatabaseFactory::getFactory()->getConnection();
 *
 * That's my personal favourite when creating a database connection.
 * It's a slightly modified version of Jon Raphaelson's excellent answer on StackOverflow:
 * http://stackoverflow.com/questions/130878/global-or-singleton-for-database-connection
 *
 * Full quote from the answer:
 *
 * "Then, in 6 months when your app is super famous and getting dugg and slashdotted and you decide you need more than
 * a single connection, all you have to do is implement some pooling in the getConnection() method. Or if you decide
 * that you want a wrapper that implements SQL logging, you can pass a PDO subclass. Or if you decide you want a new
 * connection on every invocation, you can do do that. It's flexible, instead of rigid."
 */

/**
 * Configuration for: Database
 * DB_TYPE The used database type. Note that other types than "mysql" might break the db construction currently.
 * DB_HOST The mysql hostname, usually localhost or 127.0.0.1
 * DB_NAME The database name
 * DB_USER The username
 * DB_PASS The password
 * DB_PORT The mysql port, 3306 by default (?), find out via phpinfo() and look for mysqli.default_port.
 * DB_CHARSET The charset, necessary for security reasons. Check Database.php class for more info.
 */
$DB_TYPE = 'mysql';
$DB_HOST = 'localhost';
$DB_NAME = 'db_name';
$DB_USER = 'user_name';
$DB_PASS = 'password';
$DB_PORT = '3306';
$DB_CHARSET = 'utf8';

class DatabaseFactory
{
    private static $factory;
    private $database;

    public static function getFactory()
    {
        if (!self::$factory) {
            self::$factory = new DatabaseFactory();
        }
        return self::$factory;
    }

    public function getConnection() {
        if (!$this->database) {
            $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);
            $this->database = new PDO(
                $DB_TYPE . ':host=' . $DB_HOST . ';dbname=' .
                $DB_NAME . ';port=' . $DB_PORT . ';charset=' . $DB_CHARSET,
                $DB_USER, $DB_PASS, $options
            );
        }
        return $this->database;
    }
}

?>

Then you can run your queries like so from your other classes/methods, such as Users::doesUsernameAlreadyExist($user_name);

public static function doesUsernameAlreadyExist($user_name)
{

    // Establish Connection
    $database = DatabaseFactory::getFactory()->getConnection();

    // Prepare Query
    $query = $database->prepare("SELECT user_id FROM users WHERE user_name = :user_name LIMIT 1");

    // Execute
    $query->execute(array(':user_name' => $user_name));

    // If exists, return true
    if ($query->rowCount() == 1) { return true; }

    // Default, return false
    return false;

}