Monday, August 2, 2010

Database Class using PHP

Hi PHP Developers,
I wrote a Database utility class, which is used mostly in any projects. If you've this class, then you don't need to write queries each & every time when a new project comes. You can just copy this file & paste it in your class directory & can call it anytime. The class written below is very simple & it can be understood to the novice also. There will be a way to minimize the functions & statements inside the class too, if so, don't regret to post it in comments.

<?php
/*-----------------------------------------------------------------------
Created By: Sathish Kumar.R
Date: 30 July 2010
E-mail: smart2raise[at]gmail[dot]com
Purpose: Database Manipulation


Functions Available:
db: Constructor (for mysql connect)
query: Executing query(mysql_query)
num_rows: Finding total rows(mysql_num_rows)
fetch_object: Fetch results in Object (mysql_fetch_object)
fetch_array: Fetch results in Array (mysql_fetch_array)
fetch_assoc: Fetch results in Array (mysql_fetch_assoc)
insert: Insert query (Insert into...)
insert_id: Gets last inserted ID (mysql_insert_id)
update Update query (Update ...)
delete Delete query (Delete ...)
countof: Count function in mysql
maxof: Max function in mysql
sumof: Sum function in mysql
avgof: AVG function in mysql
last_query: Displays the query which you executed last
throw_error: If any mysql error occurs & you set $debug = true then it will show the query & the mysql error
-------------------------------------------------------------------------*/
class db{
var $debug = false;
var $query = array();
var $prefix = "";
function db($server,$username,$pwd,$db){
mysql_connect($server,$username,$pwd) or die('Please check your database connection');
mysql_select_db($db);
}
function query($qry){
$this->query[] = $qry;
$res = mysql_query($qry);
if(mysql_error()){
$this->throw_error();
}
return $res;
}
function num_rows($res){
return mysql_num_rows($res);
}
function fetch_object($res){
$fet = mysql_fetch_object($res);
return $fet;
}
function fetch_array($res){
$fet = mysql_fetch_array($res);
return $fet;
}
function fetch_assoc($res){
$fet = mysql_fetch_assoc($res);
return $fet;
}
function insert($val,$table){
$query = 'INSERT INTO '.$table.' (';
foreach ($val AS $key => $value)
$query .= '`'.$key.'`,';
$query = rtrim($query, ',').') VALUES (';
foreach ($val AS $key => $value){
if(get_magic_quotes_gpc())
$query .= '\''.$value.'\',';
else
$query .= '\''.mysql_real_escape_string($value).'\',';
}
$query = rtrim($query, ',').')';
return $this->query($query);
}
function insert_id(){
return mysql_insert_id();
}
function update($val,$table,$con){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else
{
$where = "";
}
$query = 'update '.$table.' set ';
foreach ($val AS $key => $value){
if(get_magic_quotes_gpc())
$query .= $key."=".'\''.$value.'\',';
else
$query .= '\''.mysql_real_escape_string($value).'\',';
}
$query = rtrim($query, ',')." ".$where;
return $this->query($query);
}
function delete($table,$con){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else {
$where = "";
}
return $this->query("delete from {$table} {$where}");
}
function countof($col,$table,$con="",$group=""){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else {
$where = "";
}
if($group!="")
$groupby = "group by ".$group;
else
$groupby = "";
$query = $this->query("select count({$col}) from {$table} {$where} {$groupby}");
$fet = $this->fetch_array($query);
return $fet[0];
}
function maxof($col,$table,$con="",$group=""){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else {
$where = "";
}
if($group!="")
$groupby = "group by ".$group;
else
$groupby = "";
$query = $this->query("select max({$col}) from {$table} {$where} {$groupby}");
$fet = $this->fetch_array($query);
return $fet[0];
}
function sumof($col,$table,$con="",$group=""){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else {
$where = "";
}
if($group!="")
$groupby = "group by ".$group;
else
$groupby = "";
$query = $this->query("select sum({$col}) from {$table} {$where} {$groupby}");
$fet = $this->fetch_array($query);
return $fet[0];
}
function avgof($col,$table,$con="",$group=""){
if($con!=""){
$where = "where ";
$lastitem = end($con);
foreach ($con AS $key => $value){
if($value!=$lastitem){
if(get_magic_quotes_gpc())
$where .= $key."='".$value."' && ";
else
$where .= $key."='".mysql_real_escape_string($value)."' && ";
}
else{
if(get_magic_quotes_gpc())
$where .= $key."='".$value."'";
else
$where .= $key."='".mysql_real_escape_string($value)."'";
}
}
}
else {
$where = "";
}
if($group!="")
$groupby = "group by ".$group;
else
$groupby = "";
$query = $this->query("select avg({$col}) from {$table} {$where} {$groupby}");
$fet = $this->fetch_array($query);
return $fet[0];
}
function last_query(){
return end($this->query);
}
function throw_error(){
if($this->debug==true){
$qry = "".end($this->query)."<br>";
}
else{
$qry = "";
}
die("<div style='width:500px; margin:auto; text-align:left; color:red; font-size:12px;border:2px solid #FFD700;vertical-align:middle; line-height:19px;background:#FFFFDD;font-family:verdana;padding:3px;'>".$qry."Mysql Error: ".mysql_error());
}
}
?>

<?php
//Usage of Class:


$hostname = "hostname";
$username = "username";
$password = "";
$database = "dbname";
$db = new db($hostname,$username,$password,$database);
$db->debug = true;
//If it is set to true, then mysql error will shown the query which throws the error.

$qry = $db->query("select columnname from tablename"); //Executing query
$db->num_rows($qry); //Finding total rows

$val = array("a"=>123,"b"=>"sathish","c"=>"kumar");

$a = $db->insert($val,"tablename");
/*Note: $val should be an array, else it will throw error Key in an array should be the column name in the table & value can be any value */

$db->insert_id(); //returns the last inserted ID in the database

$val1 = array("a"=>123,"b"=>"Sathish","c"=>"Kumar");

$db->update($val1,"tablename",array("id"=>2));
/*
Update query same as insert query, you can pass multiple conditions in the 3rd argument i.e array("id"=>2,"a"=>'123')
*/

$db->delete("tablename",array("id"=>4));
//You can pass multiple conditions in 2nd argument


$db->countof("id","tablename",array("a"=>123123));


$db->maxof("id","tablename",array("a"=>123123));


$db->avgof("id","tablename",array("a"=>123123));

/*
for countof(),maxof(),avgof() you can pass additional argument group by


$db->countof("id","tablename",array("a"=>123123),"c");

this will result as "select count(id) where a = '123123' group by c"
*/

$db->last_query() //This will return last executed query
?>

19 comments:

Social Network Design said...

It’s a great post, you really are a good writer! I’m so glad someone like you have the time, efforts and dedication writing, for this kind of article… Helpful, And Useful.. Very nice post!

Sathish Kumar said...

Thanks for your compliments. This will encourage me to write more.

wisdom said...

it is confusing to me.

SuSu said...

I copy and use ur code . Thanks Genius person

Sathish said...

@SuSu: Welcome :)

Drupal Development said...

Thanks for the piece of code.I was searching for this.You were helpful.

Anonymous said...

your code is helpful . Thank u so much.

capricorn said...

Thanks,It's great...

guru said...

Thanks

Mindmajix Trainings said...

simple insert query using data base class
// Simple Insert Query
$database->query('INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)');
Read more…

Check this site Mindmajix for more indepth PHP Tutorials

Go here if you’re looking for information on PHP concepts

Manjot kaur said...

Your design of the blog is really eye-catching. More over the content is also very productive. Information you have provided is really very beneficial.


6 Month php Industrial Training in Chandigarh
6 weeks php industrial training in chandigarh

SAP Success Factor Training in Chennai Chennai said...

Informative Content on php Thanks for sharing

Anonymous said...

Thanks for the update you have nicely covered this topic. keep it up. asp.net training in jalandhar

Bhavya Kumar said...

My friend Suggest me this blog and I can say this is the best blog to get the basic knowledge.Thank you so much for this Selenium Training in Chennai

Bhavesh Kavad said...

Thank you very much, Your code is fully helpful...!

ASP.NET Company India

Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
seo Company in India

Shanavas Rahiman said...

Nice blog for beginners keep it ip
Only4prormmers

dheeraj sharma said...

great post on page seo

Webwing Technologies said...

PHP is gaining popularity day by day as a programming language. It is widely used for website development. If you want professional results, to drive in additional traffic to your site hire php developer is the best option...
Hire PHP developer in India | Web design company Nashik