PHP, PDO and Database Connections


PHP


VIEW SOURCE CODE BACK TO CATEGORIES







Up down arrows


PHP


Here you will find procedural and object orientated PHP source code snippets and scripts. Feel free to explore and use any of the source code with anything you may wish.

If you have any questions or suggestions send me a message.

Remember, to force a browser cache refresh and reload any page press Ctrl + F5. Or hold down Shift + Reload.

divider
1. Connecting To a Database With PHP

As you may already know there are many ways to connect to a database with PHP. There is/was the old simple way of using mysql_query() but this was deprecated as of PHP 5.5 and opened many doors to security vulnerabilities like SQL injection.

Alternatively, a better way is to use the MySQLi extension which can be implemented procedurally or object orientated. The MySQLi extension improves upon security through the use of prepared statements to name just one example.

Arguably, the best way to connect to a database is to make use of the PDO (PHP Data Objects) extension. The extension acts like a data access layer which uses a unified API. PDO is great for portability, extensibility and maintenance, making it very easy to switch between different databases if the need arises, for example from MySQL to PostgreSQL.

For the sake of simplicity, I've used MySQLi for the below snippet through an object oriented approach. For future reference PDO shall be used.

    
class Db {

    // The database connection
    protected static $connection;

    /**
     * Connect to the database
     *
     * @return bool false on failure / mysqli MySQLi object instance on success
     */
    public function connect() {

        // Try and connect to the database
        if(!isset(self::$connection)) {
            // Load configuration as an array. Use the actual location of your configuration file
            $config = parse_ini_file('./config.ini');
            self::$connection = new mysqli('localhost', $config['username'], $config['password'], $config['dbname']);
        }

        // If connection was not successful, handle the error
        if(self::$connection === false) {

            // Handle error - notify administrator, log to a file, show an error screen, etc.
            return false;
        }
        return self::$connection;
    }

    /**
     * Query the database
     *
     * @param $query The query string
     * @return mixed The result of the mysqli::query() function
     */
    public function query($query) {

        // Connect to the database
        $connection = $this -> connect();

        // Query the database
        $result = $connection -> query($query);

        return $result;
    }

    /**
     * Fetch rows from the database (SELECT query)
     *
     * @param $query The query string
     * @return bool False on failure / array Database rows on success
     */
    public function select($query) {
        $rows = array();
        $result = $this -> query($query);
        if($result === false) {
            return false;
        }
        while ($row = $result -> fetch_assoc()) {
            $rows[] = $row;
        }
        return $rows;
    }

    /**
     * Fetch the last error from the database
     *
     * @return string Database error message
     */
    public function error() {
        $connection = $this -> connect();
        return $connection -> error;
    }

    /**
     * Quote and escape value for use in a database query
     *
     * @param string $value The value to be quoted and escaped
     * @return string The quoted and escaped string
     */
    public function quote($value) {
        $connection = $this -> connect();
        return "'" . $connection -> real_escape_string($value) . "'";
    }
}
    
Using The Script
    
// Our database object
$db = new Db();

// Quote and escape form submitted values
$name = $db -> quote($_POST['username']);
$email = $db -> quote($_POST['email']);

// Insert the values into the database
$result = $db -> query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");
    
A SELECT query.
    
$db = new Db();
$rows = $db -> select("SELECT `name`,`email` FROM `users` WHERE id=5");
    
Up Arrow