PHP SQL Class in PHP Template Engine

In the previous post on PHP Template Engine, you have seen how I implement the custom PHP Template Engine. Here I am going to create a PHP SQL Class in a custom template Engine.

SQL Class will handle all the SQL Operations like connection, selecting a database, fetch query data, get executable query data like if insert or delete rows.

All the transactional query statements like start transaction, commit & rollback are handle by this class.

PHP SQL Class

In this SqlCon class, the constructor has four default parameters SQLSERVER, SQLUSER, SQLPASS, and SQLDB.

These parameter constant can be defined using define functions.

define(‘SQLSERVER’,’localhost’).

There is one member variable $con, used to store the database link.

Transactional Statement

There are three member functions, which handle the transactional queries

Begin is a member function that will start the transactional statement and can be committed using the Commit member function.

Rollback is a member function that rolls back the current transaction for the specified database connection.

Fetch Rows

Rows is a member function that will return the result set of a query.

CheckSql is member function that will return the number of rows affected with update, insert or delete query.

Close connection

Close is a member function that will close the connection and set the member variable to null.

class SqlCon
{
 var $con;
 function __construct($host=SQLSERVER, $user=SQLUSER, $pass=SQLPASS,$db=SQLDB)
 {
  $this->con=mysqli_connect($host,$user,$pass);
  if(!$this->con)
  {
   die("Error in Connection" . mysqli_error($this->con));
  }
  if(!mysqli_select_db($this->con,$db))
  {
   die("Error to select database".mysqli_error($this->con));
  }
 }

 function Close()
 {
  mysqli_close($this->con);
  $this->con = null;
 }

 function Begin()
 {
  mysqli_query($this->con,"start transaction");
  mysqli_query($this->con,"begin");
 }

 function Commit()
 {
  mysqli_query($this->con,"commit");
 }

 function Rollback()
 {
  mysqli_query($this->con,"rollback");
 }
	
 function Rows(&$result)
 {
  if(!$result)
  {
   echo "Technical Error";
   return;
  }
  else if(mysqli_num_rows($result)<=0)
  {
   return false;
  }
  else
  {
   return true;
  }	
 }

 function CheckSql()
 {
  return mysqli_affected_rows($this->con);
 }
}

How to use SQL database class

Now, let see how to use this class in your application

first, creating connection and selecting database by creating object of class.

$DB = new SqlCon(); 

second, fetch rows

$sql = "select * from employee";
$result = mysqli_query($DB->con, $sql);
if($DB->Rows($result)
{
 // do something
}

third, checking queries like insert, delete

$sql = "delete from employee";
mysqli($DB->con,$sql);
if($DB->CheckSql()>=1) // if there is affected rows
{
 // dosomething
}

forth, handling transactional statement

$DB->Begin();
$sql = "delete from transactions where master_id=3 and payment='done'";
mysqli($DB->con,$sql);
if($DB->CheckSql()>=1)
{
  $sql = "delete from master where master_id = 3";
  mysql($DB->con,$sql);
  if($DB->CheckSql()>=1){
    $DB->Commit();
  }else { 
    $DB->Rollback();
  }
}
else{
 $DB->Rollback();
}