php class for mysql database manipulation

Here, this is a php class for manipulating mysql database and executing queries. For beginners this very useful. Include this file in your project and call this class by creating object. Here is class :


<?php
  class Db
  {
  
  protected	$_con=null;
  
  protected	$_recordset=null;
  
  public function Db(array $params)
  {
  
  	$this->_con=mysql_connect($params['host'],$params['username'],$params['password']) or die('Connection error');
  	
    if($this->_con)
  	{
  		$db=mysql_select_db($params['db'],$this->_con) or die('Error '.mysql_error($this->_con));
  
  	}
  }
  
  
  public function execute($query)
  {
  if ($this->_recordset = mysql_query($query)) 
  { 
  	return $this->_recordset;
  }
      else 
      {
       die("Error in executing query... :: ".$query." <br/>".mysql_error($this->_con));
      }
  }
  
  
  public function getConnection()
  {
  	return $this->_con;
  }

  public function lastInsertID()
  {	if($this->_con)
  return mysql_insert_id($this->_con);
  return false;
  }

  public function insert($table,$data)
  {
  $query="INSERT INTO `".$table."`  (%s)  VALUES  (%s)";
  $fields=array();
  $values=array();
  foreach ($data as $key=>$val)
  {
  $fields[]="`".$key."`";
  $values[]="'".mysql_real_escape_string($val,$this->_con)."'";
  }
  $query=sprintf($query, implode( ",", $fields) ,  implode( ",", $values ) );
  return 	$this->execute($query) ;
  }
  
  public function update($table,$data,$where)
  {
  $query="UPDATE  `".$table."`  SET   %s  ";
  $fields=array();
  foreach ($data as $key=>$val)
  {
  $fields[]="`".$key."`="."'".mysql_real_escape_string($val,$this->_con)."'";
  }
  $where="  WHERE  ".$where;
  $query=sprintf( $query, implode( ",", $fields)).$where;
  return 	$this->execute($query) ;
  }

  public function  delete($table,$where)
  {
  $query="DELETE  FROM  `".$table."`   ";
  $where="  WHERE  ".$where;
  $query=$query.$where;
  return 	$this->execute($query) ;
  }
 
 
  public function __destruct()
  {
  	mysql_close($this->_con);
  }
}

For initializing the class we have to pass array with 4 values (user name,password,host and database name) as given in example:

Database Connection

$params=array('host'=>'localhost','username'=>'database user name','password'=>'your password','db'=>'database name');
$db=new Db($params);

Now object $db is created and connection is established, if there is problem in connection then class will give error message and stops working.

Let use this class, first of all we create table "posts" in database

Create Table

$query="CREATE TABLE posts (id int AUTO_INCREMENT PRIMARY KEY,title varchar(255),post text);";
$db->execute($query);
After this insert record, for inserting record I took an array, in array key names are fields name of table.

Insert Data

//insert query
$table="posts";
$data=array();
$data['title']="First Test Post";
$data['post']="This is content of first post. Testing testing";
$db->insert($table,$data);

Select record

$query="SELECT * FROM ".$table;
$result=$db->execute($query);
while($current=mysql_fetch_assoc($result)){
echo $current['title'].'<br/>';
echo $current['post'].'<br/>';
}

Update Record

$table="posts";
$data=array();
$data['title']="Testing post";
$data['post']="This is content of first post. Testing testing22222";
$where="id=1";
$db->update($table,$data,$where);

Delete Record

At last delete record
$where="id=1";
$db->delete($table,$where);