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
<?
$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