| corey |
02-24-2009 10:34 PM |
MySQL Database & Model Classes
This package can be used to manipulate MySQL database table records as objects.
There is a base class that abstracts basic MySQL database access operations like inserting, selecting, updating and deleting table records.
A sub-class of the base class can store and retrieve table records and perform operations to access the records information as if they are objects, like finding all records or only those that match a given criteria, create or update a given record with values from arrays, and delete a record.
Download as .zip (example files included)
Preview:
Database.php
PHP Code:
<?php
/**
* PHP 4 & 5 Database Class
* Performs basic MySQL database interactions
*/
class Database {
var $host = 'localhost';
var $user = 'username';
var $pass = 'password';
var $db = 'databasename';
var $conn; // Database connection
var $last_query; // Results of last query
var $last_sql; // String that contains last sql query
var $show_errors = true; // Whether or not to show error messages
/**
* Constructor function
* Connects and selects database
*
* @param string MySQL Host
* @param string MySQL Username
* @param string MySQL Password
* @param string MySQL Database name
* @return link Connection link
*/
function Database($host = '', $user = '', $pass = '', $db = '') {
$host = !empty($host) ? $host : $this->host;
$user = !empty($user) ? $user : $this->user;
$pass = !empty($pass) ? $pass : $this->pass;
$db = !empty($db) ? $db : $this->db;
$this->conn = mysql_connect($host, $user, $pass) or $this->error('Could not connect to database. Make sure settings are correct.');
if (is_resource($this->conn)) {
mysql_select_db($db, $this->conn) or $this->error("Database '$db' could not be found.");
return $this->conn;
}
return false;
}
/**
* Execute a query on the database
*
* @param string SQL query to execute
* @return query The query executed
*/
function query($sql) {
is_resource($this->conn) || $this->Database();
$this->last_sql = $sql;
return $this->last_query = mysql_query($sql, $this->conn) or $this->error();
}
/**
* Very simple select
*
* @param string Table name to select from
* @param string What to order by
* @param string Where statement
* @param string Columns to select
* @return result Result of query
*/
function select($table, $orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {
$orderby = !empty($orderby) ? "ORDER BY $orderby" : '';
$where = !empty($where) ? "WHERE $where" : '';
$limit = !empty($limit) ? "LIMIT $limit" : '';
return $this->query("SELECT $cols FROM $table $where $orderby $limit");
}
/**
* Performs an insert query
*
* @param string Table name to query
* @param array Associative array of Column => Value to insert
* @return result Result of query
*/
function insert($table, $data) {
if (!is_array($data))
return false;
foreach ($data as $col => $value)
$data[$col] = $this->escape($value);
$cols = array_keys($data);
$vals = array_values($data);
$this->query("INSERT INTO $table (".implode(',', $cols).") VALUES (".implode(',', $vals).")");
return mysql_insert_id();
}
/**
* Updates a row
*
* @param string Table name to query
* @param array Associtive array of columns to update
* @param string Where clause
* @return result Result of query
*/
function update($table, $data, $where) {
if (!is_array($data))
return false;
foreach ($data as $col => $value) {
$vals[] = $col.' = '.$this->escape($value);
}
return $this->query("UPDATE $table SET ".implode(',', $vals)." WHERE $where");
}
/**
* Delete a single row
*
* @param string Table name to query
* @param string The column to match against
* @param string Value to match against column
* @return result Result of query
*/
function delete($table, $where) {
return $this->query("DELETE FROM $table WHERE $where");
}
/**
* Get results of query
*
* @param string Return as object or array
* @return result Result of query
*/
function get($type = 'object') {
$type = $type == 'object' ? 'mysql_fetch_object' : 'mysql_fetch_array';
if (is_resource($this->last_query)) {
while($rows = $type($this->last_query))
$results[] = $rows;
}
else $this->error();
return (!empty($results)) ? $results : null;
}
/**
* Get first result of query
*
* @param string Return as object or array
* @return result Result of query
*/
function get_first($type = 'object') {
$type = $type == 'object' ? 'mysql_fetch_object' : 'mysql_fetch_array';
if (is_resource($this->last_query))
return $type($this->last_query);
else $this->error();
}
/**
* Escape strings
*
* @param mixed String to escape
* @return string Escaped string, ready for SQL insertion
*/
function escape($data) {
switch(gettype($data)) {
case 'string':
$data = "'".mysql_real_escape_string($data)."'";
break;
case 'boolean':
$data = (int) $data;
break;
case 'double':
$data = sprintf('%F', $data);
break;
default:
$data = ($data === null) ? 'null' : $data;
}
return (string) $data;
}
/**
* Show simple error messages to help aid development process
*
* @param string Custom error message to show
* @return death Error page
*/
function error($msg = '') {
if ($this->show_errors === true) {
$error = '<h1>Error!</h1>';
if (!empty($msg))
$error .= "$msg<br />";
if (mysql_error())
$error .= '<b>MySQL Error:</b> '.mysql_error().'<br />';
if (isset($this->last_sql))
$error .= '<b>SQL Statement:</b> '.$this->last_sql;
die($error);
}
}
}
Model.php
PHP Code:
<?php
require_once('Database.php');
/**
* PHP 4 & 5 Database Model Class
* Sets up the Model interface
*/
class Model extends Database {
var $table;
var $fields;
var $id;
/**
* Constructor function, gets table name, sets the ID of a current row
* and gets all field names from the table
*
* @param integer ID of row to update or delete
*/
function Model($id = '') {
if (empty($this->table))
$this->table = strtolower(substr(get_class($this), 0, -6));
if (empty($this->fields)) {
$this->query("SHOW COLUMNS FROM $this->table");
foreach ($this->get() as $col)
$this->fields[] = $col->Field;
}
if (!empty($id)) {
$this->id = $id;
}
}
/**
* Very simple select
*
* @param string What to order by
* @param string Where statement
* @param string Columns to select
* @return result Result of query
*/
function select($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {
if (!empty($this->id) && empty($where)) $where .= "id = $this->id";
return parent::select($this->table, $orderby, $where, $cols, $limit);
}
/**
* Insert a new record, or update existing
*
* @param array Data to insert (usually $_POST)
*/
function save($data) {
if (!is_array($data))
return false;
for ($i=0; $i<count($this->fields); $i++) {
$set[$this->fields[$i]] = !empty($data[$this->fields[$i]]) ? $data[$this->fields[$i]] : '';
}
if (empty($this->id))
return $this->insert($this->table, $set);
else {
foreach ($set as $key => $val) {
if (empty($set[$key]) || $set[$key] == '')
unset($set[$key]);
}
return $this->update($this->table, $set, "id = '$this->id'");
}
}
/**
* Select from table
*
* @param string Order by
* @param string Where clause
* @param string Columns to select
*/
function find_all($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {
$orderby = (!empty($orderby)) ? $orderby : 'id DESC';
$where = (!empty($where)) ? $where : '';
$cols = (!empty($cols)) ? $cols : '*';
$limit = (!empty($limit)) ? $limit : '';
$this->select($orderby, $where, $cols, $limit);
return $this->get();
}
/**
* Select single row
*
* @param string Order by
* @param string Where clause
* @param string Columns to select
*/
function find($orderby = 'id DESC', $where = '', $cols = '*', $limit = '') {
$orderby = (!empty($orderby)) ? $orderby : 'id DESC';
$where = (!empty($where)) ? $where : '';
if (!empty($this->id) && empty($where)) $where .= "id = $this->id";
$cols = (!empty($cols)) ? $cols : '*';
$limit = (!empty($limit)) ? $limit : '';
$this->select($orderby, $where, $cols, $limit);
return $this->get_first();
}
/**
* Delete row or rows
*
* @param string Where clause
*/
function delete($where) {
if (!empty($this->id) && empty($where)) $where .= "id = $this->id";
return parent::delete($this->table, $where);
}
}
|
