UltraMega Blog
1Sep/110

Creating A Database Connection On Demand

It may be useful to only create a database connection when you actually use it for the sake of efficiency. Here is a wrapper for MySQLi that does just that in the most simple way I could devise. A (possibly desired) side-effect is that this limits you to a single connection.

How you pass in the connection details is up to you. You can hard-code them into the mysqli initialization, put them in class constants, or pass them into a constructor that sets static properties.

<?php
/**
 * Wrapper for MySQLi
 *
 * Creates a database connection on demand
 */
class DB {
    private static $db;
    private function connect() {
        if(!isset(self::$db)) {
            self::$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        }
    }
    public function __call($name, $arguments) {
        $this->connect();
        return call_user_func_array(array(self::$db, $name), $arguments);
    }
    public function __get($name) {
        $this->connect();
        return self::$db->$name;
    }
    public function __set($name, $value) {
        $this->connect();
        self::$db->$name = $value;
    }
    public function __isset($name) {
        $this->connect();
        return isset(self::$db->$name);
    }
    public function __unset($name) {
        $this->connect();
        unset(self::$db->$name);
    }
}

Usage:

<?php
$db = new DB(); // functionally identical to a mysqli object
$db->query('...');
Tagged as: , , , No Comments
11Aug/091

Snippet: Maintain a Single Database Object in PHP 5 Using the Singleton Pattern

When creating a PHP application, it is usually necessary to connect to a database to perform certain tasks. In some cases you only want to open a connection when necessary, but limit it to a single connection. This way you don't waste resources on unnecessary database connections. For these situations I use the Singleton Pattern, which is perfect for this.

In this example, we are creating a MySQLi object and forcing it to a single instance. We just need to call DB::get() to create and/or access the object.

28Jul/0929

Using MySQL Prepared Statements in PHP

Prepared statements in MySQL are an alternative to writing raw SQL code to execute. Instead, you write a statement with placeholders (?) where you want variable to go, then attach variables to those placeholders.

A prepared statement is basically a template that can be reused with different variables. There are some benefits and drawbacks to prepared statements that should be considered:

Pros:

  • Prevents SQL injection without needing to escape data
  • Allows you to repeat the same statement without the overhead of parsing the SQL
  • Allows you to send raw binary data in packets
  • Creates code that is easier to read by separating SQL logic from data

Cons:

  • Slower for one time queries since it requires two requests from the MySQL server
  • Limited to SELECT, INSERT, REPLACE, UPDATE, DELETE, and CREATE TABLE queries
  • Placeholders can only be used for values and not table/column names

Conclusion: I'd say prepared statements win due to security benefits alone

PHP supports MySQL prepared statements using the Mysqli (MySQL Improved) extension in PHP 5 via the MySQLi_STMT class. They are fairly easy to use once you get used to the differences from writing raw SQL statements. This tutorial will explain how to use prepared statements.

27Mar/0922

Creating a TinyURL Clone

This tutorial will explain how to create a basic clone of the TinyURL service. If you've never heard of it, TinyURL allows you to turn long URLs into shorter links so they can be easily sent via email or other means. It might be fun to create your own personalized version that works just as well, and it's very easy to do. We'll go over the database structure, creating the required PHP script, and using mod_rewrite to make nicer URLs.