Read This Offline With Code With Me APP

EASY CRUD Operation Using Core PHP

Posted On: Jun 22, 2015

If you are going to develop website using PHP, then you may need to use mysql database too. To perform CRUD(Create, Read, Update, Delete) operations in datatabase will be unmanagable or redudant. so here is a simple class which can perform those operations easily.

Create a file DB.php and paste following code


<?php
/*
Author: Sagar Devkota
EASY DB class for CRUD operations
Simply change database configuration below
*/
define('DB_SERVER','localhost');   
define('DB_USER','root');  //change with your mysql user id 
define('DB_PASS','');  //change with your mysql password 
define('DATABASE','testdb'); //your mysql database name

class DB{
		
	
		private $connection;
		public $debug=array();

		function __construct() {

       		$this->connection= mysql_connect(DB_SERVER,DB_USER,DB_PASS);
       		mysql_select_db(DATABASE);

  		}

  		

  		function get_results($sql) {
  			$res=mysql_query($sql,$this->connection);
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();
  			if(!$res) return false;
  			$data=array();
  			while($row=mysql_fetch_array($res)){
  				$data[]=$row;
  			}


  			return $data;
  		}


  		function get_result($sql) {
  			$res=mysql_query($sql,$this->connection);
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();

  			$data=array();

  			if(!$res) return false;
  			$data=mysql_fetch_row($res);
  			


  			return $data;
  		}



  		function query($sql){
  			$res=mysql_query($sql,$this->connection);
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();

  			if(!$res){
  				return false;
  			}
  			else
  			{
  				return true;
  			}
  		}

  		function insert($table,$data){
			if(!is_array($data)) return false;

  			$sql='';
  			$fields=array();
  			$values=array();
			foreach ($data as $field => $value) {
				# code...
				$fields[]=$field;
				$values[]=$value;

			}

			$fields=implode(",", $fields);
			$values=implode(",", $values);

			$sql="insert into $table($fields) values($values);";
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();

  			$res=mysql_query($sql,$this->connection);

  			if(!$res){
  				return false;
  			}
  			else
  			{
  				return true;
  			}
  		}


  		function update($table,$data,$where=''){
			if(!is_array($data)) return false;


  			$sql='';
  			$fields=array();
  			
			foreach ($data as $field => $value) {
				# code...
				
				$fields[]="$field='".$values."'";
			}

			if($where!="") {
				# code...
				
				$where.="WHERE ".$where;
			};

			

			$fields=implode(",", $fields);
			

			$sql="update into $table($fields) $where;";
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();

  			$res=mysql_query($sql,$this->connection);

  			if(!$res){
  				return false;
  			}
  			else
  			{
  				return true;
  			}
  		}



  		function delete($table,$where=''){
			
  			$fields=array();
  			
			
			if($where!="") {
				# code...
				
				$where.="WHERE ".$where;
			};

			

			
			

			$sql="delete from $table $where;";
  			$this->debug['QUERY']=$sql;
  			$this->debug['LAST_ERROR']=mysql_error();

  			$res=mysql_query($sql,$this->connection);

  			if(!$res){
  				return false;
  			}
  			else
  			{
  				return true;
  			}
  		}
}



$GLOBALS['DB']=new DB();


?>


Now implement it in your php file



Example Functions


get_results(sql): This function gets records from given table.
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	$students=$DB->get_results("select * from students");
	foreach($students as $student) {
			echo("Roll is".$student['roll']."<br/>");
			echo("Name is".$student['name']."<br/>");
			echo("Address is".$student['address']."<br/><hr>");
	}
	
?>


insert(tabale, data_array): This function adds new data into a table. returns true if success otherwise false
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	$student=array(
		'id'=>null, //for auto increment
		'name'=>'Ram',
		'roll'=>25,
		'address'=>'Pokhara'
		);
		
	$DB->insert('students',$student);
	
	
?>


update($table,$data,$where=''): This function data into a table. returns true if success
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	$student=array(
		
		'name'=>'Hari',
		'roll'=>29,
		'address'=>'Pokhara'
		);
		
	$DB->update('students',$student,"id=5"); //update student with id=5
	
	
?>


get_result($sql): This function gets the single row from table
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	
	$student=$DB->get_result("select * from students where id=5"); //get student with id=5
	echo "Roll is ". $student['roll'];
	
	
?>


query($sql): Run any custom query. like UPDATE, DELETE, ALTER etc
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	
	$DB->query("delete from students where id=5"); //delete student with id=5

	
?>


delete($table,$where=''): This function deletes data from table
<?php
	require("DB.php");
	global $DB;
	
	//suppose get all records from Table student with field id,roll,name,address
	
	$result=$DB->delete("students","id>10"); 
	if($result)
		echo "Record is deleted successfully";
	
	
?>


Tags:

  • crud
  • database
  • mysql
  • php

Share This On

Comments