Database wrapper for Codeigniter 3

Dani 2 308 Views

DaniWeb is built on top of the Codeigniter 3.1.x PHP framework. Although I probably should have built it as a CI model, here is the database library that we are using. You can see it mainly serves as a wrapper for CodeIgniter's built-in database class.

You can see we use a master-slave database setup. Some of the functionality includes logging if a slow query takes more than 1s. We also group all queries that are the result of a single POST request (e.g. submitting a form, etc.) in a transaction. If a query fails, we retry it, and if it fails multiple times, we roll back the transaction.

I hope this helps you with your own project, and even if it doesn't, perhaps you'll find it interesting.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

// This class serves as our own wrapper for CodeIgniter's built-in database class
class Dw_database {

	private $CI;
	private $requests;
	private $master_conn;
	private $slave_conn;
	public $transaction_status;

	// Read rows from the database
	public function read($query = '')
	{
	    $time = microtime(true);

	    $this->requests++;

		$response = $this->CI->db->query($query);

		$time = microtime(true) - $time;
		$class_name = strtolower($this->CI->router->class);

		if ($class_name != 'cron' AND $time > 1)
		{
		    file_put_contents(STATIC_PATH . '/httpdocs/application/logs/database.php', date('Y-m-d H:i:s') . " => $time => " . uri_string() . " $query\n", FILE_APPEND);
		}

		return $response;
	}
	
	// Read rows from the slave
	public function read_slave($query = '')
	{	    
	    $time = microtime(true);	    
	    
	    if ($this->slave_conn === null)
	    {
	        // Load CodeIgniter's built-in database class
	        $this->slave_conn = $this->CI->load->database('slave', true);	        
	    }	    
	    
	    $this->requests++;
	    
	    $response = $this->slave_conn->query($query);
	    
	    $time = microtime(true) - $time;
	    
	    if ($time > 30)
	    {
	        file_put_contents(STATIC_PATH . '/httpdocs/application/logs/database_slave.php', date('Y-m-d H:i:s') . " => $time => " . uri_string() . " $query\n", FILE_APPEND);
	    }
	    
	    return $response;
	}
	
	public function read_slave_unbuffered($query = '')
	{	    
	    if ($this->slave_conn === null)
	    {
	        // Load CodeIgniter's built-in database class
	        $this->slave_conn = $this->CI->load->database('slave', true);	        
	    }
	    
	    return $this->slave_conn->conn_id->query($query, MYSQLI_USE_RESULT);
	}
	
	public function read_unbuffered($query = '')
	{
	    return $this->CI->db->conn_id->query($query, MYSQLI_USE_RESULT);
	}

	// Write to the database
	public function write($query = '', $get_id = false)
	{
	    $time = microtime(true);

	    $this->requests++;
	    
	    $i = 0;
		
		// If the query failed ...
		while ($this->CI->db->query($query) === false)
		{
		    // file_put_contents('/home/daniweb/httpdocs/application/logs/db_lock.php', date('Y-m-d H:i:s') . " => " . uri_string() . " => " . json_encode($_POST) . " $query\n", FILE_APPEND);		    
		    
		    // Try again
		    		    
		    if (is_cli())
		    {
		        sleep(5);
		    }
		    else
		    {
		        usleep(10000);
		    }
		    
		    $i++;
		    		    
		    // Query failed many times
		    if ($i > 5)
		    {
		        // file_put_contents('/home/daniweb/httpdocs/application/logs/db_lock_fail.php', date('Y-m-d H:i:s') . " => " . uri_string() . " => " . json_encode($_POST) . " $query\n", FILE_APPEND);
		        
		        $this->transaction_status = false;
		        
		        return 0;
		    }
		}

		if ($get_id)
		{
			$response = $this->CI->db->insert_id();
		}
		else
		{
			$response = $this->CI->db->affected_rows();
		}
		
		$time = microtime(true) - $time;
		$class_name = strtolower($this->CI->router->class);

		/*
		if ($class_name != 'cron' AND $time > 1)
		{
	       file_put_contents('/home/daniweb/httpdocs/application/logs/database.php', date('Y-m-d H:i:s') . " => $time => " . uri_string() . " $query\n", FILE_APPEND);
		}
		*/

		return $response;
	}

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

	// Escape a string and encase it in single quotes (for use in DB queries)
	public function escape($string = '')
	{
	    return $this->CI->db->escape($string);
	    // return $this->CI->db->escape(filter_var($string, FILTER_UNSAFE_RAW, array('flags' => FILTER_FLAG_STRIP_HIGH)));
	}

	// Constructor function
	public function __construct()
	{
	    $this->requests = 0;

		$this->CI =& get_instance();

		// Load CodeIgniter's built-in database class
		// $this->master_conn = $this->CI->load->database('master', true);

		// Load Cache
		$this->CI->load->driver('cache', array('adapter' => 'memcached'));

		// $this->CI->output->enable_profiler(TRUE);	
		
		if ($this->CI->input->server('REQUEST_METHOD') == 'POST')
		{
	       $this->CI->db->trans_begin();
	       
	       // Maintain our own transaction status
	       $this->transaction_status = true;
		}
	}

	public function __destruct()
	{	    
	    if ($this->CI->input->server('REQUEST_METHOD') == 'POST')
	    {
	        if ($this->transaction_status === false)
	        {
	            $this->CI->db->trans_rollback();
	        }
	        else
	        {
	            $this->CI->db->trans_commit();
	        }
	    }
	}
}
Member Avatar for Dani
Dani 4,329 The Queen of DaniWeb

Wow, this is old. I just stumbled upon it and it’s so crazy looking back at old code you wrote once upon a time. Especially since we are still working off the same framework so it’s just interesting to see how much it’s morphed and evolved over the years.

Member Avatar for pyeri
pyeri 51 Newbie Poster

Glad to know that Daniweb uses CodeIgniter! It's a very minimal but robust framework and has all the bells and whistles which other so called "modern" frameworks keep talking about.

I myself use CI3 for most of my freelance projects. For database interaction, I usually don't use the CI models, I prefer dealing with the $this->db object directly with SQL queries. I use a couple of helper functions which make the task of query building easier:

  function build_insert_query($table, $values)
  {
      $fields = [];
      $padding = [];
      $value_array = [];
      foreach($values as $key=>$value) {
          //if (!is_numeric($value)) $value = "'".$value."'";
          $fields[] = $key;
          $padding[] = "?";
          $value_array[] = $value;
      }
      return [ "insert into $table(" . implode(',', $fields) . ") values(" . implode(',', $padding) . ")",
      $value_array ];
  }

  function build_update_query($table, $values, $where)
  {
      $ss = "update ".$table." set ";
      $parts = [];
      $value_array = [];
      foreach($values as $key=>$value) {
          $parts[] = $key."=?";
          $value_array[] = $value;
      }
      $parts[] = "modified_at=?";
      $value_array[] = (new DateTime())->format("Y-m-d H:i:s");
      return [ $ss . implode(',', $parts) . " where " . $where,
      $value_array ];
  }
Member Avatar for Dani
Dani 4,329 The Queen of DaniWeb

I started with CI3 probably like a decade ago, and that’s what I’m continuing to use, albeit with a bunch of security updates and so forth. It’s not worth it to me to upgrade to CI4 right now since that would pretty much be a complete rewrite of the entire app for not much upside. Do you have any experience with CI4?

Member Avatar for pyeri
pyeri 51 Newbie Poster

I have never bothered to check CI4 as CI3 fulfilled all my web development needs. It required some modifications when PHP 8 came (PHP 8 didn't allow dynamic properties which are used in CI3). But otherwise, it works fine for most small to medium sized projects. One of the best things about CI3 is that it works out of the box with minimum initial configuration or coding, and the memory footprint is so small!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview