<?php
/*******************************************************************************
* MySQL Database Class
*******************************************************************************
* Author: Micah Carrick
* Email: email@micahcarrick.com
* Website: http://www.micahcarrick.com
*
* File: db.class.php
* Version: 1.0.0
* Copyright: (c) 2005 - Micah Carrick
* You are free to use, distribute, and modify this software
* under the terms of the GNU General Public License. See the
* included license.txt file.
*
*******************************************************************************
* VERION HISTORY:
*
* v1.0.0 [04.17.2005] - Initial Version
*
*******************************************************************************
* DESCRIPTION:
*
* This class aids in MySQL database connectivity. It was written with
* my specific needs in mind. It simplifies the database tasks I most
* often need to do thus reducing redundant code. It is also written
* with the mindset to provide easy means of debugging erronous sql and
* data during the development phase of a web application.
*
* The future may call for adding a slew of other features, however, at
* this point in time it just has what I often need. I'm not trying to
* re-write phpMyAdmin or anything. :) Hope you find it useful.
*
*******************************************************************************
*/
// constants used by class
define('MYSQL_TYPES_NUMERIC', 'int real ');
define('MYSQL_TYPES_DATE', 'datetime timestamp year date time ');
define('MYSQL_TYPES_STRING', 'string blob ');
class db_class {
var $last_error; // holds the last error. Usually mysql_error()
var $last_query; // holds the last query executed.
var $host; // mySQL host to connect to
var $user; // mySQL user name
var $pw; // mySQL password
var $db; // mySQL database to select
var $db_link; // current/last database link identifier
var $auto_slashes; // the class will add/strip slashes when it can
function db_class() {
// class constructor. Initializations here.
// Setup your own default values for connecting to the database here. You
// can also set these values in the connect() function and using
// the select_database() function.
$this->host = 'localhost';
$this->user = 'my_username';
$this->pw = 'my_pasword';
$this->db = 'database_name';
$this->auto_slashes = true;
}
function connect($host='', $user='', $pw='', $db='', $persistant=true) {
// Opens a connection to MySQL and selects the database. If any of the
// function's parameter's are set, we want to update the class variables.
// If they are NOT set, then we're giong to use the currently existing
// class variables.
// Returns true if successful, false if there is failure.
if (!empty($host)) $this->host = $host;
if (!empty($user)) $this->user = $user;
if (!empty($pw)) $this->pw = $pw;
// Establish the connection.
if ($persistant)
$this->db_link = mysql_pconnect($this->host, $this->user, $this->pw);
else
$this->db_link = mysql_connect($this->host, $this->user, $this->pw);
// Check for an error establishing a connection
if (!$this->db_link) {
$this->last_error = mysql_error();
return false;
}
// Select the database
if (!$this->select_db($db)) return false;
return true; // success
}
function select_db($db='') {
// Selects the database for use. If the function's $db parameter is
// passed to the function then the class variable will be updated.
if (!empty($db)) $this->db = $db;
if (!mysql_select_db($this->db)) {
$this->last_error = mysql_error();
return false;
}
return true;
}
function select($sql) {
// Performs an SQL query and returns the result pointer or false
// if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
return $r;
}
function select_one($sql) {
// Performs an SQL query with the assumption that only ONE column and
// one result are to be returned.
// Returns the one result.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
if (mysql_num_rows($r) > 1) {
$this->last_error = "Your query in function select_one() returned more that one result.";
return false;
}
$ret = mysql_result($r, 0);
mysql_free_result($r);
if ($this->auto_slashes) stripslashes($ret);
else return $ret;
}
function get_row($result, $type='MYSQL_BOTH') {
// Returns a row of data from the query result. You would use this
// function in place of something like while($row=mysql_fetch_array($r)).
// Instead you would have while($row = $db->get_row($r)) The
// main reason you would want to use this instead is to utilize the
// auto_slashes feature.
if ($type == 'MYSQL_ASSOC') $row = mysql_fetch_array($result, MYSQL_ASSOC);
if ($type == 'MYSQL_NUM') $row = mysql_fetch_array($result, MYSQL_NUM);
if ($type == 'MYSQL_BOTH') $row = mysql_fetch_array($result, MYSQL_BOTH);
if (!$row) return false;
if ($this->auto_slashes) {
// strip all slashes out of row...
foreach ($row as $key => $value) {
$row[$key] = stripslashes($value);
}
}
return $row;
}
function dump_query($sql) {
// Useful during development for debugging purposes. Simple dumps a
// query to the screen in a table.
$r = $this->select($sql);
if (!$r) return false;
echo "<div style="border: 1px solid blue; font-family: sans-serif; margin: 8px;">n";
echo "<table cellpadding="3" cellspacing="1" border="0" width="100%">n";
$i = 0;
while ($row = mysql_fetch_assoc($r)) {
if ($i == 0) {
echo "<tr><td colspan="".sizeof($row).""><span style="font-face: monospace; font-size: 9pt;">$sql</span></td></tr>n";
echo "<tr>n";
foreach ($row as $col => $value) {
echo "<td bgcolor="#E6E5FF"><span style="font-face: sans-serif; font-size: 9pt; font-weight: bold;">$col</span></td>n";
}
echo "</tr>n";
}
$i++;
if ($i % 2 == 0) $bg = '#E3E3E3';
else $bg = '#F3F3F3';
echo "<tr>n";
foreach ($row as $value) {
echo "<td bgcolor="$bg"><span style="font-face: sans-serif; font-size: 9pt;">$value</span></td>n";
}
echo "</tr>n";
}
echo "</table></div>n";
}
function insert_sql($sql) {
// Inserts data in the database via SQL query.
// Returns the id of the insert or true if there is not auto_increment
// column in the table. Returns false if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$id = mysql_insert_id();
if ($id == 0) return true;
else return $id;
}
function update_sql($sql) {
// Updates data in the database via SQL query.
// Returns the number or row affected or true if no rows needed the update.
// Returns false if there is an error.
$this->last_query = $sql;
$r = mysql_query($sql);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$rows = mysql_affected_rows();
if ($rows == 0) return true; // no rows were updated
else return $rows;
}
function insert_array($table, $data) {
// Inserts a row into the database from key->value pairs in an array. The
// array passed in $data must have keys for the table's columns. You can
// not use any MySQL functions with string and date types with this
// function. You must use insert_sql for that purpose.
// Returns the id of the insert or true if there is not auto_increment
// column in the table. Returns false if there is an error.
if (empty($data)) {
$this->last_error = "You must pass an array to the insert_array() function.";
return false;
}
$cols = '(';
$values = '(';
foreach ($data as $key=>$value) { // iterate values to input
$cols .= "$key,";
$col_type = $this->get_column_type($table, $key); // get column type
if (!$col_type) return false; // error!
// determine if we need to encase the value in single quotes
if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $values .= "$value,";
elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
$value = $this->sql_date_format($value, $col_type); // format date
$values .= "'$value',";
}
elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
if ($this->auto_slashes) $value = addslashes($value);
$values .= "'$value',";
}
}
$cols = rtrim($cols, ',').')';
$values = rtrim($values, ',').')';
// insert values
$sql = "INSERT INTO $table $cols VALUES $values";
return $this->insert_sql($sql);
}
function update_array($table, $data, $condition) {
// Updates a row into the database from key->value pairs in an array. The
// array passed in $data must have keys for the table's columns. You can
// not use any MySQL functions with string and date types with this
// function. You must use insert_sql for that purpose.
// $condition is basically a WHERE claus (without the WHERE). For example,
// "column=value AND column2='another value'" would be a condition.
// Returns the number or row affected or true if no rows needed the update.
// Returns false if there is an error.
if (empty($data)) {
$this->last_error = "You must pass an array to the update_array() function.";
return false;
}
$sql = "UPDATE $table SET";
foreach ($data as $key=>$value) { // iterate values to input
$sql .= " $key=";
$col_type = $this->get_column_type($table, $key); // get column type
if (!$col_type) return false; // error!
// determine if we need to encase the value in single quotes
if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $sql .= "$value,";
elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
$value = $this->sql_date_format($value, $col_type); // format date
$sql .= "'$value',";
}
elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
if ($this->auto_slashes) $value = addslashes($value);
$values .= "'$value',";
}
}
$sql = rtrim($sql, ','); // strip off last "extra" comma
if (!empty($condition)) $sql .= " WHERE $condition";
// insert values
return $this->update_sql($sql);
}
function execute_file ($file) {
// executes the SQL commands from an external file.
if (!file_exists($file)) {
$this->last_error = "The file $file does not exist.";
return false;
}
$str = file_get_contents($file);
if (!$str) {
$this->last_error = "Unable to read the contents of $file.";
return false;
}
$this->last_query = $str;
// split all the query's into an array
$sql = explode(';', $str);
foreach ($sql as $query) {
if (!empty($query)) {
$r = mysql_query($query);
if (!$r) {
$this->last_error = mysql_error();
return false;
}
}
}
return true;
}
function get_column_type($table, $column) {
// Gets information about a particular column using the mysql_fetch_field
// function. Returns an array with the field info or false if there is
// an error.
$r = mysql_query("SELECT $column FROM $table LIMIT 1");
if (!$r) {
$this->last_error = mysql_error();
return false;
}
$ret = mysql_field_type($r, 0);
if (!$ret) {
$this->last_error = "Unable to get column information on $table.$column.";
mysql_free_result($r);
return false;
}
mysql_free_result($r);
return $ret;
}
function sql_date_format($value, $type) {
// Returns the date in a format for input into the database. You can pass
// this function a timestamp value such as time() or a string value
// such as '04/14/2003 5:13 AM'.
if (gettype($value) == 'string') $value = strtotime($value);
return date('Y-m-d H:i:s', $value);
}
function print_last_error($show_query=true) {
// Prints the last error to the screen in a nicely formatted error message.
// If $show_query is true, then the last query that was executed will
// be displayed aswell.
?>
<div style="border: 1px solid red; font-size: 9pt; font-family: monospace; color: red; padding: .5em; margin: 8px; background-color: #FFE2E2">
<span style="font-weight: bold">db.class.php Error:</span><br><?= $this->last_error ?>
</div>
<?
if ($show_query && (!empty($this->last_query))) {
$this->print_last_query();
}
}
function print_last_query() {
// Prints the last query that was executed to the screen in a nicely formatted
// box.
?>
<div style="border: 1px solid blue; font-size: 9pt; font-family: monospace; color: blue; padding: .5em; margin: 8px; background-color: #E6E5FF">
<span style="font-weight: bold">Last SQL Query:</span><br><?= str_replace("n", '<br>', $this->last_query) ?>
</div>
<?
}
}
?>
|
|
Store
Your email:
