mega

PHP-MySQL Database Connection Class

 

 
Overview
 

 
 
criteria 1
 
 
 
 
 


 


Bottom Line

time and I hope it will to you too. The class is dual-licensed under the GNU General Public License and the MIT License and is copyright (C) 2009 Studio77D. You can download it from HERE. Bellow I will show you examples of it’s usage. 1 <?php 2 require_once ‘db.class.php’; 3 4 // setup your connection details 5 […]

0
Posted April 3, 2013 by Bandish Patel

 
Full Article
 
 

time and I hope it will to you too.

The class is dual-licensed under the GNU General Public License and the MIT License and is copyright (C) 2009 Studio77D.

You can download it from HERE.

Bellow I will show you examples of it’s usage.

1 <?php
2 require_once 'db.class.php';
3
4 // setup your connection details
5 $db=new DBConnection('localhost','username','password','database');
6 ?>

or other way you can just insert your details under the db.class.php (line 77)

1 function DBConnection($db_host='', $db_user='', $db_pass='', $db_name='')

and than call the class like this

1 <?php
2 $db=new DBConnection();
3 ?>

This is example table which I will use during the examples below

01 CREATE TABLE `customers` (
02  `customer_id` int(11) NOT NULL auto_increment,
03  `name` varchar(255) default NULL,
04  PRIMARY KEY  (`customer_id`)
05 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
06
07 INSERT INTO `customers` VALUES (1,'User 1');
08 INSERT INTO `customers` VALUES (2,'User 2');
09 INSERT INTO `customers` VALUES (3,'User 3');
10 INSERT INTO `customers` VALUES (4,'User 4');
11 INSERT INTO `customers` VALUES (5,'User 5');

Example Select which will retun results as an associative array

1 <?php
2 $query='SELECT * FROM customers';
3 $res=$db->rq($query);
4 $row=$db->fetch($res);
5
6 echo $row['name']; // Output is User 1
7 ?>

Example Select which will retun results as an object

1 <?php
2 $query='SELECT * FROM customers';
3 $res=$db->rq($query);
4 $row=$db->ofetch($res);
5
6 echo $row->name;  // Output is User 1
7 ?>

Example Select which will retun results as an associative array, a numeric array, or both.

1 <?php
2 $query='SELECT * FROM customers';
3 $res=$db->rq($query);
4 $row=$db->afetch($res);
5 echo $row[1];  // Output is User 1
6 echo '<br>';
7 echo $row['name'];  // Output is User 1
8 ?>

If you want to make the while

01 <?php
02 $query='SELECT * FROM customers';
03 $res=$db->rq($query);
04 while(($row=$db->fetch($res)) != FALSE) {
05  echo $row['name'].'<br />';
06 }
07
08 /* Output is:
09 User 1
10 User 2
11 User 3
12 User 4
13 User 5
14 */
15 ?>

You can easyly free result memory  with:

1 <?php
2 $db->free_result();
3 ?>

To obtain total num rows after select

1 <?php
2 $query='SELECT * FROM customers';
3 $res=$db->rq($query);
4 $num_rows=$db->num_rows($res);
5 echo $num_rows// Output is 5
6 ?>

To obtain affected rows after select, update or insert

1 <?php
2 $query='INSERT INTO customers SET name="Customer 6"';
3 $res=$db->rq($query);
4 $affected_rows=$db->affected_rows($res);
5 echo $affected_rows// Output is 1
6 ?>

To obtain the number of all exectued queries

1 <?php
2 for($i=0;$i<10;$i++) {
3  $query='SELECT * FROM customers';
4  $res=$db->rq($query);
5 }
6 $num_queries=$db->num_queries($res);
7 echo $num_queries// Output is 10
8 ?>

If you need to lock a table(s), you can do it very easy. Some good info about when to lock your tables you can find here

01 <?php
02 // if you want to lock one table for READ
03 $tablesToLock=array('customers'=>'READ');
04 // if you want to lock one table for WRITE
05 $tablesToLock=array('customers'=>'WRITE');
06 // if you want to lock more than one table
07 $tablesToLock=array('customers'=>'WRITE','customers2'=>'READ');
08
09 $db->lock_tables($tablesToLock);
10 ?>

To unlock your tables, you just need to call

1 <?php
2 $db->unlock_tables();
3 ?>

To obtain last id from insert

1 <?php
2 $query='INSERT INTO customers SET name="Customer 6"';
3 $db->rq($query);
4 echo $db->last_id();
5 ?>

If you need to escape your sting before passing it

1 <?php
2 $content='""""""test\'te%st`test_';
3 $query='INSERT INTO customers SET name="'.$db->string_escape($content).'"';
4 $db->rq($query);
5 ?>

If you want also to escape the % and _ characters you can call the “string_escape” with second variable true

1 <?php
2 $query='INSERT INTO customers SET name="'.$db->string_escape($content,true).'"';
3 $db->rq($query);
4 ?>

And here is a short way to obratin a result (very helpful if you know your results will return 1 row)

1 <?php
2 // first variable is the table(s) from where you want to get the info. easy to make joins too
3 // second variable is the conditions which you want to use
4 // and third variable is the columns which you want to get
5 $getRowInfo=$db->getRow('customers','name="User 1"','name');
6 echo $getRowInfo['name']; // Output is User 1
7 ?>

Of course, in the end don’t forget to close your connection ;)

1 <?php
2 $db->close();
3 ?>

Enjoy!


Bandish Patel

 


0 Comments



Be the first to comment!


Leave a Response


(required)

Powered By Indic IME