PDA

View Full Version : Database Class



NancyJ
10-22-2006, 12:21 AM
So, I thought it was about time I upgraded my database include from


<?
$dbc=mysql_connect ("host", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydatabase") or die(mysql_error());
?>


I designed it to be easy to use and easy to read - it may not be as complex and fancy as some but I find when you try to get too fancy - and in particular include every possible functionality, classes can become difficult to use and remembering parameter lists and orders becomes a nightmare - that happened with a form building class I wrote and I never use it anymore - it was worse than typing html.
So its designed to have a nice balance between writing SQL and queries being built.
There are basic functions for select, insert (with replace option), update, delete and a query function that will run any sql you pass it.
It relies on 2 config files - a global config file (for your entire site) and a module specific config file (just for the dbc class) - part of a templating system I'm building - so for simplicity you can simply take those parts out or modify the constructor to your liking. It also has a destructor for those using PHP 5 (not critical, remove it for php 4)
The config files specify the values of the class variables.
It has a debug mode - that will output errors to the screen and a logging mode that logs errors to a file.

Selects return the resource identifier, other queries return msql_affected_rows and the query() function can return either.

Enjoy - feedback welcome.



<?
//database class

class dbc
{
var $host;
var $user;
var $password;
var $database;
var $conn;
var $debug;
var $log;

function dbc($configOverride="")
{
//get variables from config file
$expected = array('host', 'user', 'password', 'database', 'debug', 'log');
//get global config values first
$config = file($_SERVER['DOCUMENT_ROOT'].'/../lib/global.cnf');
$this->loadConfig($config, $expected);
$config = file($_SERVER['DOCUMENT_ROOT'].'/../modules/config/dbc.cnf');
$this->loadConfig($config, $expected);
if($configOverride)
{
$this->loadConfig($configOverride, $expected);
}
$this->connect();
$this->selectDatabase($this->database);
}

function loadConfig($config, $expected)
{
foreach($config as $line)
{
$configOptions = explode(":", $line, 2);
if(in_array(strtolower($configOptions[0]), $expected))
{
$this->$configOptions[0]=str_replace(array("\n", "\r\n","\r", "\n\r"), "",$configOptions[1]);
}
}
}
function connect()
{
if($this->host && $this->user && $this->database)
{
$this->conn = mysql_connect($this->host,$this->user,$this->password) or $this->error("Could not connect to server","Connect to $this->host $this->user:$this->password");
}
}

function selectDatabase($database)
{
$this->database = $database;
mysql_select_db($database, $this->conn) or $this->error(mysql_error(), "USE $database");
}

function error($error, $sql)
{
if($this->debug)
{
echo "SQL: $sql<br />Error: $error";
}
if($this->log)
{
$handle = fopen($_SERVER['DOCUMENT_ROOT'].'/../modules/errorlogs/dbc.log', 'a');
$errorLog = 'DATE:'."\t".date("Y-m-d H:i:s")."\n";
$errorLog .= 'PAGE:'."\t".$_SERVER['PHP_SELF']."?".$_SERVER['argv'][0]."\n";
$errorLog .= 'SQL:'."\t".$sql."\n";
$errorLog .= 'ERROR:'."\t".$error."\n\n";
fwrite($handle, $errorLog);
fclose($handle);
}
}

function select($fields, $tables, $where = array(), $order = array(), $group=array(),$limit = "")
{
$fields = implode(",",$fields);
$tables = implode(",", $tables);
$where = implode(" AND ", $where);
$order = implode(",",$order);
$group = implode(",",$group);

$sql = "SELECT $fields FROM $tables ";
if(!empty($where))
{
$sql .= ' WHERE '.$where;
}
if(!empty($order))
{
$sql .= ' ORDER BY '.$order;
}
if(!empty($group))
{
$sql .= ' GROUP BY '.$group;
}
if(!empty($limit))
{
$sql .= ' LIMIT '.$limit;
}

$result = mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);

return $result;
}

function insert($tables, $values, $fields=array(), $replace = false)
{
foreach($values as $key => $val)
{
$values[$key] = $this->protect($val);
}
$tables = implode(",", $tables);
$values = implode(",", $values);
$fields = implode(",", $fields);

if($replace)
{
$sql = "REPLACE INTO $tables ";
}
else
{
$sql = "INSERT INTO $tables ";
}


if(!empty($fields))
{
$sql .= '('.$fields.') ';
}

$sql .= 'VALUES ('.$values.')';

mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);

}

function update($tables, $values, $fields, $where= array(), $limit= "")
{
foreach($values as $key=>$val)
{
$pairs[] = $this->$fields[$key].' = '.$this->protect($val);
}
$pairs = implode(",",$pairs);
$tables = implode(",", $tables);
$where = implode(" AND ", $where);
$sql = "UPDATE $tables SET $pairs ";
if(!empty($where))
{
" WHERE $where";
}
if(!empty($limit))
{
$sql .= " LIMIT $limit";
}
mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);
}

function delete($table, $where="", $limit="")
{
$where = implode(",", $where);
if(empty($where) && empty($limit))
{
$sql = "TRUNCATE $table";
}
else
{
$sql = "DELETE FROM $table ";
if(!empty($where))
{
$sql .= ' WHERE '.$where;
}
if(!empty($limit))
{
$sql .= ' LIMIT '.$limit;
}
}
mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);
}

function query($sql, $return="result")
{
$result = mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
switch($return)
{
case "result":
return $result;
break;
case "affected_rows":
return mysql_affected_rows($this->conn);
break;
}
}

function protect($val)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}

if (!is_numeric($value) || $value[0]=='0')
{
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}

function close()
{
mysql_close($this->conn);
}

function __destruct()
{
$this->close();
}
}



examples of use



$dbc = new dbc();

$result = $dbc->query("My very complicated sql query that selects data");

$affected = $dbc->query("My very complicated sql query that affects some rows", "affected_rows");

$result = $dbc->select(array('field1', 'field2'), 'mytable', array('id=1',"foo='bar'"));

$dbc->delete('mytable');

//...etc

sudarshan
02-19-2008, 07:51 AM
So, I thought it was about time I upgraded my database include from


<?
$dbc=mysql_connect ("host", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("mydatabase") or die(mysql_error());
?>


I designed it to be easy to use and easy to read - it may not be as complex and fancy as some but I find when you try to get too fancy - and in particular include every possible functionality, classes can become difficult to use and remembering parameter lists and orders becomes a nightmare - that happened with a form building class I wrote and I never use it anymore - it was worse than typing html.
So its designed to have a nice balance between writing SQL and queries being built.
There are basic functions for select, insert (with replace option), update, delete and a query function that will run any sql you pass it.
It relies on 2 config files - a global config file (for your entire site) and a module specific config file (just for the dbc class) - part of a templating system I'm building - so for simplicity you can simply take those parts out or modify the constructor to your liking. It also has a destructor for those using PHP 5 (not critical, remove it for php 4)
The config files specify the values of the class variables.
It has a debug mode - that will output errors to the screen and a logging mode that logs errors to a file.

Selects return the resource identifier, other queries return msql_affected_rows and the query() function can return either.

Enjoy - feedback welcome.



<?
//database class

class dbc
{
var $host;
var $user;
var $password;
var $database;
var $conn;
var $debug;
var $log;

function dbc($configOverride="")
{
//get variables from config file
$expected = array('host', 'user', 'password', 'database', 'debug', 'log');
//get global config values first
$config = file($_SERVER['DOCUMENT_ROOT'].'/../lib/global.cnf');
$this->loadConfig($config, $expected);
$config = file($_SERVER['DOCUMENT_ROOT'].'/../modules/config/dbc.cnf');
$this->loadConfig($config, $expected);
if($configOverride)
{
$this->loadConfig($configOverride, $expected);
}
$this->connect();
$this->selectDatabase($this->database);
}

function loadConfig($config, $expected)
{
foreach($config as $line)
{
$configOptions = explode(":", $line, 2);
if(in_array(strtolower($configOptions[0]), $expected))
{
$this->$configOptions[0]=str_replace(array("\n", "\r\n","\r", "\n\r"), "",$configOptions[1]);
}
}
}
function connect()
{
if($this->host && $this->user && $this->database)
{
$this->conn = mysql_connect($this->host,$this->user,$this->password) or $this->error("Could not connect to server","Connect to $this->host $this->user:$this->password");
}
}

function selectDatabase($database)
{
$this->database = $database;
mysql_select_db($database, $this->conn) or $this->error(mysql_error(), "USE $database");
}

function error($error, $sql)
{
if($this->debug)
{
echo "SQL: $sql<br />Error: $error";
}
if($this->log)
{
$handle = fopen($_SERVER['DOCUMENT_ROOT'].'/../modules/errorlogs/dbc.log', 'a');
$errorLog = 'DATE:'."\t".date("Y-m-d H:i:s")."\n";
$errorLog .= 'PAGE:'."\t".$_SERVER['PHP_SELF']."?".$_SERVER['argv'][0]."\n";
$errorLog .= 'SQL:'."\t".$sql."\n";
$errorLog .= 'ERROR:'."\t".$error."\n\n";
fwrite($handle, $errorLog);
fclose($handle);
}
}

function select($fields, $tables, $where = array(), $order = array(), $group=array(),$limit = "")
{
$fields = implode(",",$fields);
$tables = implode(",", $tables);
$where = implode(" AND ", $where);
$order = implode(",",$order);
$group = implode(",",$group);

$sql = "SELECT $fields FROM $tables ";
if(!empty($where))
{
$sql .= ' WHERE '.$where;
}
if(!empty($order))
{
$sql .= ' ORDER BY '.$order;
}
if(!empty($group))
{
$sql .= ' GROUP BY '.$group;
}
if(!empty($limit))
{
$sql .= ' LIMIT '.$limit;
}

$result = mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);

return $result;
}

function insert($tables, $values, $fields=array(), $replace = false)
{
foreach($values as $key => $val)
{
$values[$key] = $this->protect($val);
}
$tables = implode(",", $tables);
$values = implode(",", $values);
$fields = implode(",", $fields);

if($replace)
{
$sql = "REPLACE INTO $tables ";
}
else
{
$sql = "INSERT INTO $tables ";
}


if(!empty($fields))
{
$sql .= '('.$fields.') ';
}

$sql .= 'VALUES ('.$values.')';

mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);

}

function update($tables, $values, $fields, $where= array(), $limit= "")
{
foreach($values as $key=>$val)
{
$pairs[] = $this->$fields[$key].' = '.$this->protect($val);
}
$pairs = implode(",",$pairs);
$tables = implode(",", $tables);
$where = implode(" AND ", $where);
$sql = "UPDATE $tables SET $pairs ";
if(!empty($where))
{
" WHERE $where";
}
if(!empty($limit))
{
$sql .= " LIMIT $limit";
}
mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);
}

function delete($table, $where="", $limit="")
{
$where = implode(",", $where);
if(empty($where) && empty($limit))
{
$sql = "TRUNCATE $table";
}
else
{
$sql = "DELETE FROM $table ";
if(!empty($where))
{
$sql .= ' WHERE '.$where;
}
if(!empty($limit))
{
$sql .= ' LIMIT '.$limit;
}
}
mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);
}

function query($sql, $return="result")
{
$result = mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
switch($return)
{
case "result":
return $result;
break;
case "affected_rows":
return mysql_affected_rows($this->conn);
break;
}
}

function protect($val)
{
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}

if (!is_numeric($value) || $value[0]=='0')
{
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}

function close()
{
mysql_close($this->conn);
}

function __destruct()
{
$this->close();
}
}



examples of use



$dbc = new dbc();

$result = $dbc->query("My very complicated sql query that selects data");

$affected = $dbc->query("My very complicated sql query that affects some rows", "affected_rows");

$result = $dbc->select(array('field1', 'field2'), 'mytable', array('id=1',"foo='bar'"));

$dbc->delete('mytable');

//...etc



Hello ,

Thers a bug into code see this


function update($tables, $values, $fields, $where= array(), $limit= "")
{
foreach($values as $key=>$val)
{
$pairs[] = $this->$fields[$key].' = '.$this->protect($val);
}
$pairs = implode(",",$pairs);
$tables = implode(",", $tables);
$where = implode(" AND ", $where);
$sql = "UPDATE $tables SET $pairs ";
if(!empty($where))
{
" WHERE $where";
}
if(!empty($limit))
{
$sql .= " LIMIT $limit";
}
mysql_query($sql, $this->conn) or $this->error(mysql_error(), $sql);
return mysql_affected_rows($this->conn);
}

it must have


foreach($values as $key=>$val)
{
$pairs[] = $fields[$key].' = '.$this->protect($val);
}

and


if(!empty($where))
{
$sql .= " WHERE $where";
}
if(!empty($limit))
{
$sql .= " LIMIT $limit";
}

kbluhm
02-19-2008, 06:31 PM
I notice you are using PHP4 class syntax, but have included a PHP5-style destructor. When used with PHP4, the destructor will never be called.

Add this line to the end of your constructor if you'd actually like to have the destructor called:

register_shutdown_function( array( & $this, '__destruct' ) );

Also, the protect() method is stripping slashes from everything it receives if magic_quotes is enabled, but slashes are only added to $_GET, $_POST, and $_COOKIE values. What if a value is passed that does not come from the those three arrays? You potentially have incorrectly modified data.

Grant Palin
08-30-2008, 06:54 AM
Thanks for sharing the code. I've been working on a somewhat similar class recently, except it does database abstraction as well.

I have an abstract class with some basic implementation, and a subclass for MySQL to implement functionality specific to MySQL. So if I move my database to, say, PostgreSQL, I can just write a PqSQL implementation of my abstract class. I am still debating certain aspects of my setup, so your code may help me to decide what to do!

As a suggested enhancement for your protect function, you could add some code to handle arrays as well. If the function receives an array, you can recursively call protect on the array, which will 'clean' each value within. I'm using something like this:


function clean($input) {
if (is_array($input)) {
return array_map("clean", $input);
}

// process individual values here ...
}

This could save you having to sanitize each item yourself.

hmvrulz
10-16-2008, 11:48 AM
Thanks for sharing the code. I've been working on a somewhat similar class recently, except it does database abstraction as well.

I have an abstract class with some basic implementation, and a subclass for MySQL to implement functionality specific to MySQL. So if I move my database to, say, PostgreSQL, I can just write a PqSQL implementation of my abstract class. I am still debating certain aspects of my setup, so your code may help me to decide what to do!

As a suggested enhancement for your protect function, you could add some code to handle arrays as well. If the function receives an array, you can recursively call protect on the array, which will 'clean' each value within. I'm using something like this:


function clean($input) {
if (is_array($input)) {
return array_map("clean", $input);
}

// process individual values here ...
}

This could save you having to sanitize each item yourself.

can u please provide me with ur class