OOPS way of querying database !!!

Tired of writing those connect and execute query statements in php all the time ?
Now you have got an OOPS way of executing your queries...

Using this, there are many advantages like avoiding the reuse of code and improvement in performance.

Supose we have a user table with the fields id, name, branch and city:

CREATE TABLE IF NOT EXISTS `user` (
`id` int(2) NOT NULL auto_increment,
`name` varchar(25) NOT NULL,
`branch` varchar(25) NOT NULL,
`city` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
)

Dump few values into it:

INSERT INTO `user` (`id`, `name`, `branch`, `city`) VALUES
(1, 'ravi', 'CSE', 'Hyderabad'),
(2, 'kiran', 'Mech', 'Vijayawada'),
(3, 'prasad', 'CSIT', 'Vizag'),
(4, 'sashi', 'ECE', 'Tirupati');



Now to perform the task, the following are the steps:

1) First create a config file with the details of the constants like:
host, username, password, database name and all the table names of your database.
Name this file as: config.inc.php

<?php
define('DB_SERVER', "localhost");
define('DB_USER', "root");
define('DB_PASS', "");
define('DB_DATABASE', "smarty"); // a sample database

define('TABLE_USERS', "user"); // better to define even tables
?>

2) now write the class file containing the methods for executing your queries and returning the result sets. Name this file as: Database.class.php

<?php
class Database {

var $server = ""; //database server
var $user = ""; //database login name
var $pass = ""; //database login password
var $database = ""; //database name
var $pre = ""; //table prefix

function Database($server, $user, $pass, $database, $pre=''){
$this->server=$server;
$this->user=$user;
$this->pass=$pass;
$this->database=$database;
$this->pre=$pre;
}

function connect($new_link=false) {
$this->link_id=@mysql_connect($this->server,$this->user,$this->pass,$new_link);

if (!$this->link_id) {//open failed
$this->oops("Could not connect to server: <b>$this->server</b>.");
}

if(!@mysql_select_db($this->database, $this->link_id)) { //no database
$this->oops("Could not open database: <b>$this->database</b>.");
}

// unset the data so it can't be dumped
$this->server='';
$this->user='';
$this->pass='';
$this->database='';
}

function close() {
if(!@mysql_close($this->link_id)){
$this->oops("Connection close failed.");
}
}

function query($sql) {
// do query
$this->query_id = @mysql_query($sql, $this->link_id);

if (!$this->query_id) {
$this->oops("<b>MySQL Query fail:</b> $sql");
return 0;
}

$this->affected_rows = @mysql_affected_rows($this->link_id);

return $this->query_id;
}

function fetch_assoc($query_id=-1) {
// retrieve row
// fetches and returns results one line at a time

if ($query_id!=-1) {
$this->query_id=$query_id;
}

if (isset($this->query_id)) {
$record = @mysql_fetch_assoc($this->query_id);
}else{
$this->oops("Invalid query_id: <b>$this->query_id</b>. Records could not be fetched.");
}

return $record;
}


function oops($msg='') {
if($this->link_id>0){
$this->error=mysql_error($this->link_id);
$this->errno=mysql_errno($this->link_id);
}
else{
$this->error=mysql_error();
$this->errno=mysql_errno();
}
?>
<table align="center" border="1" cellspacing="0" style="background:white;color:black;width:80%;">
<tr><th colspan=2>Database Error</th></tr>
<tr><td align="right" valign="top">Message:</td><td><?php echo $msg; ?></td></tr>
<?php if(strlen($this->error)>0) echo '<tr><td align="right" valign="top" nowrap>MySQL Error:</td><td>'.$this->error.'</td></tr>'; ?>
<tr><td align="right">Date:</td><td><?php echo date("l, F j, Y \a\\t g:i:s A"); ?></td></tr>
<tr><td align="right">Script:</td><td><a href="<?php echo @$_SERVER['REQUEST_URI']; ?>"><?php echo @$_SERVER['REQUEST_URI']; ?></a></td></tr>
<?php if(strlen(@$_SERVER['HTTP_REFERER'])>0) echo '<tr><td align="right">Referer:</td><td><a href="'.@$_SERVER['HTTP_REFERER'].'">'.@$_SERVER['HTTP_REFERER'].'</a></td></tr>'; ?>
</table>
<?php
}

}
?>


3) Finally write your query and execute it: test.php

<?php

require("config.inc.php"); // I'm using a separate config file

require("Database.class.php"); // pull the database class file

$db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

$db->connect(); // connect to the server

// your main code would go here

$sql = "select name,branch,city from ".TABLE_USERS." where id in (1,2,4)";

$res = $db->query($sql);

echo '<table style="border:2px solid #F5CC99" width="400">
<tr style="background-color:#33CCd5"><td>Name</td><td>Branch</td><td>City</td></tr>';

while ($record = $db->fetch_assoc($res)) {
echo "<tr>
<td>$record[name]</td>
<td>$record[branch]</td>
<td>$record[city]</td>
</tr>";
}

echo '</table>';

// and when finished, remember to close connection
$db->close();

?>

Now this will display a table with the 3 rows of users.
Enjoy !