[PROJECT] MySQL Database Class
Posted: Thu Jul 13, 2006 10:51 pm
I might be reinventing the wheel here but is anyone interested in creating a nice database class we can all use? I started on one and what I have below is posted below. If you guys are interested maybe we can perfect it and move it over to snippets or something.
I haven't tested it 100% but I believe most if not all of it should work flawlessly.
It also supports joins.
EDITS:
1. Modifed __construct to connect when the class is instantiated.
2. Added resetMe function to prevent multiple queries from interfering with each other.
Here are some usage examples...
I haven't tested it 100% but I believe most if not all of it should work flawlessly.
It also supports joins.
EDITS:
1. Modifed __construct to connect when the class is instantiated.
2. Added resetMe function to prevent multiple queries from interfering with each other.
Code: Select all
class databaseConnection {
function __construct($host, $username, $password) {
$this->database = '';
$this->query = '';
$this->fields = '';
$this->table = '';
$this->parameters = '';
$this->limit = '';
$this->orderby = '';
$this->joins = '';
$this->linkid = '';
$this->connected = false;
$this->dbselected = false;
if (!$this->linkid = @mysql_connect($host, $username, $password)) {
$this->displayError(mysql_errno(), mysql_error());
}
$this->connected = true;
}
function selectDatabase($database) {
if (!@mysql_select_db($database, $this->linkid)) {
$this->displayError(mysql_errno(), mysql_error());
}
$this->dbselected = true;
}
function dispatch($action, $data) {
if (!$this->connected) {
$this->displayError('NA', 'Not connected to database.');
}
if (!$this->dbselected) {
$this->displayError('NA', 'No database selected.');
}
if ($this->table == '') {
$this->displayError('NA', 'No table selected.');
}
switch ($action) {
case 'select':
return $this->selectData($data);
break;
case 'insert':
return $this->insertData($data);
break;
case 'update':
return $this->updateData($data);
break;
case 'delete':
return $this->deleteData();
break;
default:
$this->displayError('NA', 'No valid action selected. Options are select, insert, update and delete');
}
}
function selectFields($fields) {
$fieldlist = null;
$fields = explode(" ", $fields);
$fieldCount = count($fields) - 1;
for ($i = 0; $i <= $fieldCount; $i++) {
if (substr_count($fields[$i], '.') == 0) {
$fieldlist .= "`" . mysql_real_escape_string($fields[$i]) . "`, ";
} else {
list($table, $fieldname) = explode('.', $fields[$i]);
$fieldlist .= "`" . mysql_real_escape_string($table) . "`.`" . mysql_real_escape_string($fieldname) . "`, ";
}
}
$fieldlist = substr($fieldlist, 0, -2) . " FROM ";
return $fieldlist;
}
function addJoin($newtable, $newtablejoinfield, $primaryjoinfield, $jointype = null) {
$this->joins .= $jointype . " JOIN `" . mysql_real_escape_string($newtable) . "` ON (`";
$this->joins .= $this->table . "`.`" . mysql_real_escape_string($primaryjoinfield) . "` = `";
$this->joins .= $newtable . "`.`" . mysql_real_escape_string($newtablejoinfield) . "`) ";
}
function selectData($fields) {
$this->query = "SELECT " . $this->selectFields($fields) . "`" . $this->table . "` ";
if ($this->joins != '') {
$this->query .= $this->joins;
}
if ($this->parameters != '') {
$this->query .= "WHERE ";
while (list($Field, $Value) = each($this->parameters)) {
$Value = explode(" ", $Value);
$this->query .= " `" . mysql_real_escape_string($Field) . "` " . mysql_real_escape_string($Value[0]) . " '" . mysql_real_escape_string($Value[1]) . "' AND ";
}
$this->query = substr($this->query, 0, -4);
}
if ($this->orderby != '') {
$this->orderby = mysql_real_escape_string($this->orderby);
$this->query .= "ORDER BY " . $this->orderby . ' ';
}
if ($this->limit != '') {
$this->limit = mysql_real_escape_string($this->limit);
$this->query .= "LIMIT " . $this->limit . " ";
}
return $this->sendQuery();
}
function resetMe() {
$this->fields = '';
$this->table = '';
$this->parameters = '';
$this->limit = '';
$this->orderby = '';
$this->joins = '';
}
function sendQuery() {
if (!$this->linkid = @mysql_query($this->query)) {
$this->displayError(mysql_errno(), mysql_error());
}
$this->resetMe();
return $this->linkid;
}
function insertData($data) {
$this->query = "INSERT INTO `" . $this->table . "` (";
while (list($fields, ) = each($data)) {
$this->query .= "`" . mysql_real_escape_string($fields) . "`, ";
}
$this->query = substr($this->query, 0, -2) . ") values (";
reset($data);
while (list(, $values) = each($data)) {
$this->query .= "'" . mysql_real_escape_string($values) . "', ";
}
$this->query = substr($this->query, 0, -2) . ")";
return $this->sendQuery();
}
function updateData($data) {
$this->query = "UPDATE `" . $this->table . "` SET ";
while (list($fields, $values) = each($data)) {
$this->query .= "`" . mysql_real_escape_string($fields) . "`" . "='" . mysql_real_escape_string($values) . "', ";
}
if ($this->parameters == '') {
$this->displayError('NA', 'Cowardly refusing to perform table update without WHERE clause.');
}
$this->query = substr($this->query, 0, -2) . " WHERE ";
while (list($field, $value) = each($this->parameters)) {
$value = explode(" ", $value);
$this->query .= "`" . mysql_real_escape_string($field) . "` " . mysql_real_escape_string($value[0]) . " '" . mysql_real_escape_string($value[1]) . "' AND ";
}
$this->query = substr($this->query, 0, -4);
return $this->sendQuery();
}
function deleteData() {
$this->query = "DELETE FROM `" . $this->table . "` WHERE ";
if ($this->parameters == '') {
$this->displayError('NA', 'Cowardly refusing to perform delete query without WHERE clause.');
}
while (list($field, $value) = each($this->parameters)) {
$Value = explode(" ", $value);
$this->query .= "`" . mysql_real_escape_string($field) . "` " . mysql_real_escape_string($value[0]) . " '" . mysql_real_escape_string($value[1]) . "' AND ";
}
$this->query = substr($this->query, 0, -4);
return $this->sendQuery();
}
function fetchRow() {
if ($this->row = mysql_fetch_assoc($this->linkid)) {
return $this->row;
} else {
return false;
}
}
function getInsertID() {
// need to test for a valid linkid here or place an & in front of mysql_insert_id
return mysql_insert_id($this->linkid);
}
function getNumRows() {
// need to test for a valid linkid here or place an & in front of mysql_num_rows
$this->numrows = mysql_num_rows($this->linkid);
return $this->numrows;
}
function getAffectedRows() {
// need to test for a valid linkid here or place an & in front of mysql_affected_rows
return mysql_affected_rows($this->linkid);
}
function cleanup() {
// need to test for a valid linkid here or place an & in front of mysql_free_result
mysql_free_result($this->linkid);
}
function displayError($ErrorNumber, $ErrorText) {
echo '<h2>Database Error</h2>' . "\n"
.'<p>There was an error processing a database command.</p>' . "\n"
.'<p>' . $this->query . "</p>\n"
.'<p>Error #' . $ErrorNumber . "</p>\n"
.'<p>Error Description: ' . $ErrorText . "</p>\n";
}
}Here are some usage examples...
Code: Select all
// connect and select database
$Database = new databaseConnection($host, $username, $password);
$Database->selectDatabase($database);
// select table
$Database->table = 'members';
// retrieve records
// set where clause, supports =, !=, <> and all that
$Database->parameters = array('username' => '= ' . $_POST['username']);
$Database->limit = '1';
$Database->dispatch('select', 'user_id user_status user_password user_login_tries');
// insert a record
$Database->table = 'members';
$newUser = array('user_status' => '0',
'username' => $this->username,
'user_password' => md5($this->password),
'user_lastvisit' => time(),
'user_regdate' => time(),
'user_level' => 0,
'user_email' => $this->email,
'user_actkey' => '0000');
$Database->dispatch('insert', $newUser);