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);
Click to Download the PHP Database Class

 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; 
} 
 
?>

comments powered by Disqus
Copyright © 2011 PHP-Ease.com - PHP Made Easy