Database Class

The first thing you'll notice about this class is that it is not a class (but it does interface with one). A class in this case is just too much trouble for every time you want to interact with your database. It is much easier to have these standard functions that you can use anywhere and everywhere, and not have to worry about a $mysqli or class object. These functions will:

Open a MySQLi object
Juggle that object so you don't have to mess with it
Trigger an error if something ever goes wrong
Make data safe to insert into your database
Take the trudgery and tediousness out of generating queries
Easily insert and update records

A good place to store these functions (along with your sensitive database information) is in your website's root directory. When you need to connect to your database, just include the file:

include_once (BASE . 'database.php');

string escape_data ( string $data )

This function will escape any problematic characters, and make a string safe to insert into your database. This function is used by default when validating data in our Form Class.

$data	The data to be escaped.
Returns	Your escaped data.
Example	$username = escape_data ("Hacker'); DROP TABLE users;--");

object db_query ( string $query )

This function executes your $query, let's you know if there's a problem, and returns the $result.

$query	Your database query.
Returns	The $result of your query.
Example	$result = db_query ("SELECT data FROM table WHERE column='{$value}'"); while (list($data) = $result->fetch_row()) { $html .= $data . '<br />'; }

integer db_insert ( string $table, array $array )

This function makes it a lot easier to put together an insert query, and to see at a glance what is going on.

$table	The database table you're inserting records into.
$array	Key (column) and value pairs of data.
Returns	The auto generated id of your inserted data.
Example	$insert = array(); $insert['name'] = 'username'; $insert['password'] = 'password'; $insert['email'] = 'email@address.com'; $insert['registered'] = 'NOW()'; $userid = db_insert('users', $insert);

integer db_update ( string $table, array $array, string $column, mixed $id [, string $add ] )

This function makes it a lot easier to put together an update query, and to see at a glance what is going on.

$table	The database table whose record(s) you're updating.
$array	Key (column) and value pairs of data.
$column	The column you're using to define what exactly should be updated (usually the table's auto generated id column).
$id	The id (or value) of the column you'd like to update.
$add	If you have any other conditional statements that you would like to make then you can say so here.
Returns	The number of rows your update affected.
Example	$update = array(); $update['confirmed'] = 'Y'; db_update ('users', $update, 'user_id', 1);

Subscribe to our feed

database.php

<?php 
 
/* 
 *    author:           Kyle Gadd 
 *    documentation:    http://www.php-ease.com/classes/database.html 
 * 
 *    This program is free software: you can redistribute it and/or modify 
 *    it under the terms of the GNU General Public License as published by 
 *    the Free Software Foundation, either version 3 of the License, or 
 *    (at your option) any later version. 
 * 
 *    This program is distributed in the hope that it will be useful, 
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of 
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
 *    GNU General Public License for more details. 
 * 
 *    You should have received a copy of the GNU General Public License 
 *    along with this program.  If not, see <http://www.gnu.org/licenses/>. 
 */ 
 
define ('DB_SERVER', 'localhost'); 
define ('DB_USERNAME', 'username'); 
define ('DB_PASSWORD', 'password'); 
define ('DB_DATABASE', 'database'); 
 
$mysqli = new mysqli (DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE); 
 
if (mysqli_connect_errno()) { 
  trigger_error ('DB Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); 
  exit ('Sorry!  We were unable to connect to the database.  Please try again.'); 
} 
 
function escape_data ($data) { 
  global $mysqli; 
  if (get_magic_quotes_gpc()) $data = stripslashes($data); 
  return $mysqli->real_escape_string(trim($data)); 
} 
 
function db_query ($query) { 
  global $mysqli; 
  if (!$result = $mysqli->query ($query)) trigger_error("Query: {$query}<br />Error: {$mysqli->error}"); 
  return $result; 
} 
 
function db_insert ($table, $array) { 
  global $mysqli; 
  $unquote = array('NULL', 'NOW()'); 
  foreach ($array as $key => $value) { 
    $columns[] = $key; 
    if (is_numeric($value) || in_array($value, $unquote)) { 
      $data[] = $value; 
    } else { 
      $data[] = "'{$value}'"; 
    } 
  } 
  db_query ('INSERT INTO `' . $table . '` (`' . implode('`, `', $columns) . '`) VALUES (' . implode(', ', $data) . ')'); 
  return $mysqli->insert_id; 
} 
 
function db_update ($table, $array, $column, $id, $add='') { 
  global $mysqli; 
  $unquote = array('NULL', 'NOW()'); 
  foreach ($array as $key => $value) { 
    if (is_numeric($value) || in_array($value, $unquote)) { 
      $data[] = '`' . $key . '`=' . $value; 
    } else { 
      $data[] = '`' . $key . "`='{$value}'"; 
    } 
  } 
  $id = (is_numeric($id)) ? $id : "'{$id}'"; 
  db_query ("UPDATE `{$table}` SET " . implode(', ', $data) . " WHERE `{$column}`={$id} {$add}"); 
  return $mysqli->affected_rows; 
} 
 
?>