Intro to SQL Part D

For the last post in this short series on SQL I wanted to explain some more in-depth usage. A technique that I wish I would have started using in my early websites is a centralized script to handle all of my database calls. PHP does have some great functions built in to connect, read, and write using SQL statements (check out mysqli), but it's easy to start copying the same eight to ten lines of code every time you want a piece of data from your tables. A better approach is to handle the queries with a centralized class.

Before anything can be read or written to a table with a SQL statement, you need to create a connection. Your website will be signing as a user with specific privileges. A good technique is to make two users, one that can read and one that can read and write. By using the former user as much as possible and taking extra care with the latter one, you will minimize the possibility of hackers uploading malicious information or dropping whole tables.

Once a connection is made you can start having fun with the database class, customizing the methods as much as necessary to integrate it easily with your site. I prefer returning my information as arrays of objects or single objects, so I have two separate methods in my class.

class Database
{
  
  private $read;
  private $write;
  
  function _construct()
  {
    $this->read = new mysqli('localhost','USER','PASS');
    $this->write = new mysqli('localhost','USER','PASS');
  }
  
  function escape($string)
  {
    return $this->read->real_escape_string($string);
  }
  
  function select($query)
  {
    if($result = $this->read->query($query))
    {
      while($row = $result->fetch_assoc())
        $array[] = (object) $row;
      $result->close();
      return $array;
    }
  }
  
  function selectRow($query)
  {
    if($result = $this->read->select($query))
      if(count($result)==1)
        return array_pop($result);
  }
  
  function execute($query)
  {
    if($this->write->query($query))
      return true;
  }

}

This is a very basic example of a database class. The first method, _construct, is called whenever a new database object is created and makes two separate connections, one for reading and one for writing. The select, selectRow, and execute methods use the appropriate connection to perform a query and return the results. I also threw in an escape method to clean up strings before I finish a SQL query. Here's a few usage examples...

$db = new Database();
$query = "SELECT * FROM `blogs` WHERE `id` = '{$db->escape($id)}' LIMIT 1";
$db->selectRow($query);

$db = new Database();
$query = "INSERT INTO `blogs` (`title`,`body`) VALUES ('{$db->escape($title)}','{$db->escape($body)}')";
$db->execute($query);

You might have noticed that the _construct method does little more than create two mysqli objects. This database class is nothing more than a wrapper class for this built-in class, providing a centralized location to control the connection and return format for your queries. You can easily add more functionality and advanced clean-up methods with this single class.

This is the last post in this brief introduction to SQL. I hope you find it helpful for your future web projects!