To add to this, I hope you wont mind me posting my own personal database class that I use almost all the time.
<?php
/*
MySQL Database Class
--------------------
Author: Matthew Wilson
Site: http://www.icdsoftware.com
Contact: admin at icdsoftware.com
License: Free for non-commercial use.
About
-----
Contains MySQL query operations within an object
as well as simplifying some query types.
*/
define('SQL_EXPRESSION', "--{0009def7886864195af0955f4b92bbb4cc1aaf099b3515e6ff0}--");
define('SQL_ROW', 'mysql_fetch_row');
define('SQL_ASSOC', 'mysql_fetch_assoc');
function expr($val = ''){
return array(
SQL_EXPRESSION,
$val
);
}
class mysql {
var $dbc;
var $cache;
var $querynum;
var $exec;
var $pconnect;
var $backquote;
var $serialize;
function __construct($settings = array()) {
list($user, $pass, $host, $db, $pconnect, $bq, $serialize) = $settings;
$this->pconnect = $pconnect;
$this->sqlconnect($user, $pass, $db, $host);
$this->exec = 0;
$this->cache = false;
$this->dbc = false;
$this->querynum = 0;
$this->backquote = $bq;
$this->serialize = $serialize;
}
function mysql( $settings = array() ){
if( $this->dbc == NULL ){
$this->__construct($settings);
}
}
function sqlconnect($user, $pass, $db, $host = 'localhost'){
$connection_function = 'mysql_connect';
if($this->pconnect == 1) {
$connection_function = 'mysql_pconnect';
}
if($this->dbc = @$connection_function($host, $user, $pass)){
if(@mysql_select_db($db, $this->dbc)){
return true;
} else {
return false;
}
} else {
return false;
}
}
function query($sql = ''){
$timer = explode(' ', microtime());
if($this->cache = @mysql_query($sql)){
$end_timer = explode(' ', microtime());
$difference = ($end_timer[0]-$timer[0])+($end_timer[1]-$timer[1]);
$this->exec += $difference;
++$this->querynum;
return $this->cache;
} else {
return false;
}
}
function fetch($result = NULL, $method = 'mysql_fetch_assoc'){
if($result === NULL){
$result = $this->cache;
}
if($result !== false){
if($result = $method($result)){
return $result;
} else {
return false;
}
} else {
return false;
}
}
function numrows($result = NULL){
if($result == NULL){
$result = $this->cache;
}
if($rows = @mysql_num_rows($result)){
return $rows;
} else {
return false;
}
}
function insertid(){
return @mysql_insert_id();
}
function affected(){
return @mysql_affected_rows();
}
function escape($str = ''){
return @mysql_escape_string($str);
}
function free($result = NULL){
if($result === NULL){
$result = $this->result;
}
return @mysql_free_result($result);
}
function close(){
return @mysql_close($this->dbc);
}
function getExec($round = 5){
return array(
'query_count' => $this->querynum,
'execution_time' => $this->exec
);
}
function insert($table, $key_value_array){
return $this->makeQuery($key_value_array, $table, 'insert');
}
function update($table, $key_value_array, $clause = ''){
return $this->makeQuery($key_value_array, $table, 'update', $clause);
}
function makeQuery($key_to_value_data = array(), $table = '', $type = 'insert', $clause = ''){
$query = '';
$bq = ($this->backquote == 1) ? '`' : '';
$table = "$bq$table$bq";
$_keys = array();
$_values = array();
foreach($key_to_value_data as $key => $value){
switch(gettype($value)){
case 'integer': case 'double': case 'boolean':
$_values[] = $value;
break;
case 'string': case 'resource': default:
$_values[] = "'".$this->escape($value)."'";
break;
case 'NULL':
$_values[] = 'NULL';
break;
case 'array':
if(isset($value[0]) && $value[0] == SQL_EXPRESSION){
$_values[] = $value[1];
break;
}
case 'object':
if($this->serialize == 1){
$_values[] = serialize($value);
} else {
settype($value, 'string');
$_values[] = $value;
}
break;
}
$_keys[] = $key;
}
if($type == 'insert'){
// Yes, this is the coolest variable name ever
$implosion = "$bq,$bq";
$fields = implode($implosion, $_keys);
$fields = "($bq$fields$bq)";
$values = implode(',', $_values);
if($values != ''){
$query = "INSERT INTO $table $fields VALUES ( $values )";
} else {
return false;
}
} else {
$update = '';
$count_keys = count($_keys);
for($i=0;$i<$count_keys;$i++){
$update .= "$bq{$_keys[$i]}$bq = {$_values[$i]}, ";
}
$update = substr_replace($update, "", -2);
if($update != ''){
$query = "UPDATE $table SET $update";
if($clause != ''){
$query .= " WHERE $clause";
}
} else {
return false;
}
}
if($result = $this->query($query)){
return $result;
} else {
return false;
}
}
}
?>
Some Examples:
<?php
$settings = array(
'username', // Database Username
'password', // Database Password
'host', // Database Host
'database', // Database Name
'pconnect', // Use a permanent connection ? (1 or 0)
'backquotes', // Whether or not to use backquotes in generated queries ( 1 or 0 )
'serialiaze' // When building queries using the easy methods (insert() & update()), should arrays/objects be automatically serialized?
);
// My Usual settings:
$mysql = array( 'root', 'rootpass', 'localhost', 'database', 0, 1, 1 );
$db = new mysql( $settings );
// You can then call all of the MySQL methods, such as:
$insert = array( 'field' => 'value' );
$db->insert( 'table_name', $insert );
// It will automatically escape values passed, so you dont have to run an escape on every value in the insert/update array.
// You might want to update:
$db->update( 'table_name', $insert, 'id=5' );
// If you need to use something like "fieldname = fieldname+x", use the expr() function:
$update = array( 'field', expr( 'field+7' ) );
$db->update( 'table_name', $update );
// Example query and result set:
$result = $db->query( "SELECT * FROM table" );
while( $row = $db->fetch( $result ) ){
// Code
}
Also, you can specify a function to use when fetching like so:
$db->fetch( $result, SQL_ROW ); // As set at the top of the page, or:
$db->fetch( $result, 'mysql_fetch_object' ); // Passing a string
?>