As an example of what you can/should do with a mysql abstraction class, below is the class that I use for all my db stuff (I borrowed most of the code from another class I found online, this my or may not be the most recent version that I use, I've added a whack of debug information to it)...
Code: Select all
<?php
class mySQL {
var $_DEBUG;
var $_DEBUGFULL = FALSE;
var $connection;
var $selectedDb;
var $result;
var $isConnected;
var $queryType;
var $arrayType;
/* constructor */
function mySQL($dbuser, $dbpass, $dbdb, $dbhost="", $dbg=FALSE, $dbgFull=FALSE) {
if (!$dbhost) $dbhost="localhost";
$this->db = $dbdb;
$this->user = $dbuser;
$this->pass = $dbpass;
$this->server = $dbhost;
$this->_DEBUG = $dbg;
$this->_DEBUGFULL = $dbgFull;
$this->arrayType= "assoc";
$this->connect();
}
function debug(){$this->_DEBUG = TRUE;}
function nodebug(){$this->_DEBUG = FALSE;}
function connect() {
if($this->getConnected()) {
$this->closeConnection();
}
if ($this->_DEBUG) {
$this->printDebug("Connection", "Connecting to host: [$this->server] with user: [$this->user] and pass: [$this->pass].");
}
if($this->connection = (@mysql_pconnect($this->server, $this->user, $this->pass))) {
$this->setConnected(true);
if ($this->_DEBUG) $this->printDebug("Connection", "Connection Success -- Mysql Returned: [$this->connection]");
if($this->db) {
$this->setDb($this->db);
}
return true;
}
else {
$this->setConnected(false);
$this->printError("Connection", $this->getMysqlError());
return false;
}
}
/* destructor */
function disconnect() {
if($this->result) {
if ($this->_DEBUG) $this->printDebug("Disconnect","Freeing Result");
$this->freeResult();
}
if($this->getConnected()) {
if ($this->_DEBUG) $this->printDebug("Disconnect","Closing Connection");
$this->closeConnection();
}
}
function setDb($dbName) {
if(!$this->getConnected()) {
$this->printError("Db Set", "Not connected in function setDb()");
return false;
}
if($this->selectedDb = mysql_select_db($dbName, $this->connection)) {
if ($this->_DEBUG) $this->printDebug("Db Set","Setting of DB [$dbName] Successful!");
return true;
}
if ($this->_DEBUG) $this->printDebug("Db Set","Setting of DB [$dbName] Failed! -- ".mysql_error());
return false;
}
function getConnected() {
return $this->isConnected;
}
function setConnected($status) {
$this->isConnected = $status;
}
function closeConnection() {
if($this->getConnected()) {
mysql_close($this->connection);
$this->setConnected(false);
}
}
function freeResult() {
if($this->result) {
if ($this->_DEBUG) $this->printDebug("Free Result","Freeing Resultset");
@mysql_free_result($this->result);
}
}
function query($sql, $return_type='array') {
if(strlen(trim($sql)) == 0) {
if($this->_DEBUG==true) {
$this->printError("Query", "No SQL was passed to query()");
}
return false;
}
if(!$this->getConnected()) {
if($this->_DEBUG==true) {
$this->printError("Query", $this->getMysqlError());
}
return false;
}
$queryType = substr(trim($sql), 0, strpos($sql, " "));
$this->setQueryType($queryType);
$this->prepare_vars($sql);
if ($this->_DEBUG) $this->printDebug("Query","Querying Database -- [$sql]");
$this->result = mysql_query($sql, $this->connection);
if($this->result) {
if ($this->_DEBUG) $this->printDebug("Query","Query Successful -- Mysql Returned: [$this->result]");
if ($this->_DEBUG) $this->printDebug("Query","Results returned as [$return_type]");
switch($return_type) {
case 'row':
return $this->fetchRow();
break;
case 'obj':
return $this->fetchObject();
break;
case 'index':
return $this->fetchIndex0();
break;
case 'none':
return true;
break;
default:
$res_array = $this->result_to_array();
return($res_array);
break;
}
return true;
}
else {
$this->printError("Query", $this->getMysqlError());
return false;
}
}
function setQueryType($type) {
$this->queryType = strtoupper($type);
}
function getQueryType() {
return $this->queryType;
}
function getNumRows() {
if($this->result) {
return mysql_affected_rows($this->connection);
}
return false;
}
function getQueryResult() {
return $this->result;
}
function setArrayType($type) {
$this->arrayType=$type;
}
function getArrayType() {
return $this->arrayType;
}
function fetchArray() {
if($this->result) {
switch($this->arrayType) {
case "assoc":
$type = MYSQL_ASSOC;
break;
case "num":
$type = MYSQL_NUM;
break;
default:
$type = MYSQL_BOTH;
break;
}
return $this->prepare_vars(mysql_fetch_array($this->result, $type));
}
return false;
}
function fetchObject() {
if($this->result) {
$temp = mysql_fetch_object($this->result);
if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($temp, TRUE)."</pre>";
return $temp;
}
return false;
}
function fetchRow() {
if($this->result) {
$temp = $this->prepare_vars(mysql_fetch_row($this->result));
if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($temp, TRUE)."</pre>";
return $temp;
}
return false;
}
function fetchIndex0() {
if($this->result) {
return $this->prepare_vars(mysql_result($this->result, 0));
}
return false;
}
function fetchInsertID() {
if($this->result) {
return mysql_insert_id();
}
else {
$this->printDebug("Fetch Insert ID", "No Insert ID From : [$this->result]");
}
return false;
}
function result_to_array($array=null) {
if(!empty($array)) {
$this->result = $array;
}
$res_array = array();
for ($count=0; $row = @$this->fetchArray(); $count++) {
$res_array[$count] = $this->prepare_vars($row);
}
if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($res_array, TRUE)."</pre>";
return $res_array;
}
function formatdate($date) {
//Reformat a mySQL date into a more readable one
list($this->yyyy, $this->mm, $this->dd) = explode('-',$date);
$this->date = date('j F Y', mktime(0,0,0,$this->mm,$this->dd,$this->yyyy));
return $this->date;
}
function printError($loc, $text, $killApp=false) {
if($text) {
print("Error: [<span style=\"color:#F00;\">$loc</span>] : <span style=\"text-weight:bold;\">".$text."</span><br/>");
}
if($killApp) {
exit();
}
}
function printDebug($loc, $text) {
if ($text) {
echo "Debug: [<span style=\"color:#F00;\">$loc</span>] : <span style=\"text-weight:bold;\">$text</span><br/>";
}
}
function getMysqlError() {
if(mysql_error()) {
return "<span style=\"font-weight:bold;\">Mysql Error Number [".mysql_errno()."]</span> -- [<span style=\"text-decoration:underline;\">".mysql_error()."</span>]";
}
return false;
}
function prepare_vars($input) {
switch($this->getQueryType()) {
case 'SELECT':
if (is_array($input)) {
foreach ($input as $this->key => $this->value) {
$output[$this->key] = trim(stripslashes($this->value));
}
}
else {
$output = trim(stripslashes($input));
}
return $output;
break;
case 'UPDATE':
case 'INSERT':
if (is_array($input)) {
foreach ($input as $key => $value) {
$output[$key] = trim(addslashes($value));
}
}
else {
$output = $input;
}
return $output;
break;
}
}
}
?>