PHP: Database Connection Boilerplate Code

Question

In nearly every script/function I use that involves operating on the database, I use this code:

$conn = connect();
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$sql = '...';
$stid = oci_parse($conn, $sql);
oci_execute($stid);

oci_fetch_all(...);

oci_close($conn);

The $sql and occassionally the method of fetching varies, but the rest remain 100% the same in all cases I know of. Is this the proper way to do DB operations via PHP? If not, how can I avoid this boilerplate code?

Answer 1

You could go down the OO route. eg...

Class DatabaseConnection {

  private $conn;

  public function __construct() { 
         $this->conn = connect();       
         if (!$this->conn) {
         $e = oci_error();
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);       
  }

  public function ExecuteQuery( $sql) {
      $stid = oci_parse($this->conn, $sql);
      oci_execute($stid);
      $aResult = oci_fetch_all(...);
      oci_close($this->conn);

      return $aResult;
  }

}

Then your code can just instantiate this class and pass in the sql. So in your script...

$sql = '...';
$oNewDatabaseConnection = new DatabaseConnection();
$aResult = $oNewDatabaseConnection->ExecuteQuery($sql);

If the method of the fetch varies then you can just add some new functions to your class to reflect this.

Answer 2

The best way would be to create a Database class (it should automatically connect to the database in the constructor for example) with the methods you use over and over again.

However, I highly encourage you to not to reinvent the wheel. Start using a frameworks like Codeigniter, Kohana, Laravel, whatever. This will save you a lot of time and teach you many best practices.