| Adrian Schneider |
12-06-2005 08:03 PM |
Here is my DB class for one of my sites.
PHP Code:
<?php
class Database
{
var $host;
var $username;
var $password;
var $databasename;
var $connection;
/**
* Constructor Method
* Initializes connection and selects database.
**/
function Database($host, $username, $password, $databasename)
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->databasename = $databasename;
$this->connect($this->host, $this->username, $this->password);
}
/**
* Establishes MySQL connection.
**/
function connect()
{
$this->connection = @mysql_connect($this->host, $this->username, $this->password);
if (!$this->connection)
{
$error = new Page;
$error->error('Could not establish database connection.</p><p>' . mysql_error());
}
return $this->select_db($this->databasename);
}
/**
* Selects MySQL database.
**/
function select_db()
{
$resource = @mysql_select_db($this->databasename, $this->connection);
if (!$resource)
{
$error = new Page;
$error->error('Could not select database "' . $this->databasename . '".');
}
return $resource;
}
/**
* Executes a MySQL query.
**/
function query($querystring)
{
if (strpos($querystring, "\t\t\t\t") !== false)
{
$querystring = str_replace("\t\t\t\t", "\t\t", $querystring);
$querystring = str_replace("\t\t\t", "\t", $querystring);
}
$resource = mysql_query($querystring, $this->connection);
if (!$resource)
{
$this->debug_query($querystring);
}
return $resource;
}
/**
* Fetches rows from the database.
**/
function fetch($result)
{
if (mysql_num_rows($result))
{
$resource = mysql_fetch_assoc($result);
return $resource;
}
return false;
}
/**
* Halts execution of the script and displays the problem.
**/
function debug_query($query)
{
$error = new Page;
$errormessage = '<h2 class="queryfail">Failed Query:</h2><div style="background-color: #EEEEEE; width:640px; padding:5px; margin: 0 auto;">';
$errormessage .= '<pre class="query">' . $this->highlight_sql($query) . '</pre></div>';
if (mysql_error())
{
$errormessage .= '<h2 class="queryfailHeader">Reason:</h2>';
$errormessage .= '<p class="queryfailReason">' . mysql_error() . '</p>';
}
$error->error($errormessage, 'Database Error');
}
/**
* Highlights MySQL Code
**/
function highlight_sql($query)
{
$query = preg_replace("/['\"]([^'\"]*)['\"]/i", "'<span style='color: #FF6600'>$1</span>'", $query, -1);
$find = array(
'*',
'SELECT ',
'UPDATE ',
'DELETE ',
'INSERT ',
'CREATE ',
'TABLE ',
'INTO',
'VALUES',
'FROM',
'LEFT',
'JOIN',
'WHERE',
'LIMIT',
'ORDER BY',
'AND',
'OR ',
'DESC',
'ASC',
'ON ',
"\t\t\t",
'INT ',
'varchar ',
'VARCHAR ',
'NOT NULL',
'AUTO_INCREMENT',
'UNSIGNED',
'PRIMARY KEY '
);
$replace = array(
'<span style="#FF6600; font-weight: bold;">*</span>',
'<span style="color: #00AA00; font-weight: bold;">SELECT </span>',
'<span style="color: #00AA00; font-weight: bold;">UPDATE </span>',
'<span style="color: #00AA00; font-weight: bold;">DELETE </span>',
'<span style="color: #00AA00; font-weight: bold;">INSERT </span>',
'<span style="color: #00AA00; font-weight: bold;">CREATE </span>',
'<span style="color: #00AA00; font-weight: bold;">TABLE </span>',
'<span style="color: #00AA00; font-weight: bold;">INTO</span>',
'<span style="color: #00AA00; font-weight: bold;">VALUES</span>',
'<span style="color: #00AA00; font-weight: bold;">FROM</span>',
'<span style="color: #00CC00; font-weight: bold;">LEFT</span>',
'<span style="color: #00CC00; font-weight: bold;">JOIN</span>',
'<span style="color: #00AA00; font-weight: bold;">WHERE</span>',
'<span style="color: #AA0000; font-weight: bold;">LIMIT</span>',
'<span style="color: #00AA00; font-weight: bold;">ORDER BY</span>',
'<span style="color: #0000AA; font-weight: bold;">AND</span>',
'<span style="color: #0000AA; font-weight: bold;">OR </span>',
'<span style="color: #0000AA; font-weight: bold;">DESC</span>',
'<span style="color: #0000AA; font-weight: bold;">ASC</span>',
'<span style="color: #00DD00; font-weight: bold;">ON </span>',
"\t",
'<span style="color: #0000AA; font-weight: bold;">INT </span>',
'<span style="color: #0000AA; font-weight: bold;">varchar </span>',
'<span style="color: #0000AA; font-weight: bold;">VARCHAR </span>',
'<span style="color: #0000AA; font-weight: bold;">NOT NULL</span>',
'<span style="color: #0000AA; font-weight: bold;">AUTO_INCREMENT</span>',
'<span style="color: #0000AA; font-weight: bold;">UNSIGNED</span>',
'<span style="color: #0000AA; font-weight: bold;">PRIMARY KEY </span>',
);
return str_replace($find, $replace, $query);
}
/**
* Escapes data when going into the database.
* This is not for SQL Injections.
**/
function escape($value)
{
if (!is_int($value))
{
if (is_numeric($value))
{
$value = floatval($value);
}
else
{
$value = (get_magic_quotes_gpc()) ? $value : addslashes($value);
}
$value = "'" . $value . "'";
}
return $value;
}
/**
* Checks how many rows were found from a query.
**/
function num_rows($resource)
{
return mysql_num_rows($resource);
}
/**
* Insert data into the database.
* Generates query based on $data array (field => val)
**/
function insert($table, $data)
{
// Field Names
$columnbit = '';
$columns = array_keys($data);
for ($i=0; $i<count($columns); $i++)
{
$columnbit .= (($i) ? ', ' : '') . '`' . $columns[$i] . '`';
}
// Values
$valuebit = '';
$values = array_values($data);
for ($i=0; $i<count($values); $i++)
{
$value = $this->escape($values[$i]);
$valuebit .= "\t\t\t\t" . $value . (($i != count($values)-1) ? ', ' . "\n" : '');
}
// Run Query
$this->query('
INSERT INTO ' . $table. '
(' . $columnbit . ')
VALUES (' . "\n" .
$valuebit . '
)'
);
}
/**
* Update data in the database.
* Generates query based on $data array (field => val)
**/
function update($table, $data, $whereclause)
{
$valuestofields = '';
$i = 0;
foreach ($data as $field => $value)
{
$i++;
$valuestofields .= $field . ' = ' . $this->escape($value) . (($i != count($data)) ? ', ' : '') . "\n\t\t\t\t";
}
// Run Query
$this->query('
UPDATE ' . $table. '
SET' . "\n\t\t\t\t" .
$valuestofields .
$whereclause
);
}
}
?>
Does anyone know of a good SQL syntax highlighter (that doesn't require installing additional libraries)?
[edit]Usage being:
PHP Code:
$db = new Database('server', 'user', 'pass', 'db');
just replace the error handling method ... mine uses another class for displaying things. |
More Information