I just coded this, this morning (yes work is slow at the moment) but haven't had a chance to actually test it out (PC at work doesn't have anything set up to run my php). Basically is this class doing what I think it's doing? It's aim was to take any sql syntax away from other parts of my code so error catching would be easier, and just generally make it more readable.
At the top of the code are examples of how it can be used.
Also are there any security risks im missing? I'm really quite new to php and mysql so just wanted to make sure I have this sorted before I move on and get other class' relying on this one coded. Thanks for your time.
<?php
/*
EXAMPLE - Basic Usage
$db = new database("debug");
# Get a row
$row = $db->getRow("accounts", "id name age email", "50 $name $age [email]
[email protected][/email]";
echo $row['id'] . $row['name'] . $row['age'] . $row['email'];
# Insert a row
$db->insertRow("accounts", "name email", "Jimmy $email");
# Update a row (table, set_what?, set_value, where_what?, where_value)
$db->updateRow("accounts", "name email", "Noob [email]
[email protected][/email]", "name", "Tommy");
*/
class DATABASE {
# Constructor
function __construct($mode) {
# Set error display mode
$this->_mode = $mode;
# Connect to MySQL & database
$this->connectMySQL();
$this->connectDB();
}
#Destructor
function __destruct() {
# Close connection
if ($this->_link)
mysql_close($this->_link);
}
# Variables
var $_host = "localhost";
var $_user = "root";
var $_pass = "abc123";
var $_db = "game";
var $_link = null;
var $_mode; # when in debug mode, more specific error messages are given
# Attempts to connect to MySQL using stored variables
function connectMySQL() {
# Attempt to create a connection to MySQL
$this->_link = mysql_connect($this->_host, $this->_user, $this->_pass);
# Check to see whether a connection was made
if (!$this->_link) {
if ($this->_mode == "debug")
die ("Could not connect to MySQL.
Error: " . mysql_error() . "
");
return false;
}
# Display message if we are in debug mode so we know a successful connection was made
$this->printError("Connected to MySQL.", "");
return true;
}
# Attempts to connect to a database using stored variable
function connectDB () {
$db_selected = mysql_select_db($this->_db, $this->_link);
# Check to see whether database was succesfully connected to
if (!$db_selected) {
if ($this->_mode == "debug")
die ("Could not connect to Database.
Error: " . mysql_error() . "
");
return false;
}
$this->printError("Connected to database.", "");
return true;
}
# Returns an array of rows from the database, or false if no rows exist
function getRows($table, $where_list, $where_value_list) {
if (!$where_query = $this->getFieldQuery($where_list, $where_value_list, "WHERE"))
return false;
# Assemble query, escaping dangerous variables
$query = sprintf("SELECT * FROM $table WHERE $where_query");
$result = mysql_query($query, $this->_link);
# Check if anything was returned at all
if (!$result) {
$this->printError("[b]Error Occured:[/b] No rows found in table: " . $table . " where: " . $where_key . " = " . $where_value,
"An error occured while getting information from the database.");
return false;
}
# Check if any rows were found
if (mysql_num_rows($result) < 1) {
$this->printError("[b]Error Occured:[/b] No rows found in table: " . $table . " where: " . $where_key . " = " . $where_value,
"An error occured while getting information from the database.");
return false;
}
# Rows were found, let's put them into an array
$row_count = 0; # For the row_array index
while ($row = mysql_fetch_array($result)) {
$row_array[$row_count] = $row;
$row_count++;
}
return $row_array;
}
# Returns a single row from a table. WARNING: Should only be used if you are expecting there to be only 1 of what you are looking for to exist
function getRow($table, $where_list, $where_value_list) {
# Get an array of rows
if ($row_array = $this->getRows($table, $where_list, $where_value_list)) {
# Since we are only expecting to find one row, that should be the size of the array
if (count($row_array) > 1) {
$this->printError("[b]Error Occured:[/b] More than 1 row found in table: " . $table . " where: " . $where_key . " = " . $where_value,
"An error occured while getting information from the database.");
return false;
} else {
# 1 Row found, return it.
return $row_array[0];
}
}
# No rows found
return false;
}
# Insert row into table where Example call - $object->insertRow("a_table", "name,email,pass", $value_array) - Value array must be made before hand
function insertRow($table, $field_list, $field_value_list) {
if (!$field_query = $this->getFieldQuery($field_list, $field_value_list, "INSERT"))
return false;
#Assemble full query
$query = sprintf("INSERT INTO $table $field_query");
$result = mysql_query($query, $this->_link);
if (!$result) {
$this->printError("[b]Error Occured:[/b] INSERTING fields" . $field_list . " with VALUES " . $field_value_list . " Field Query: " . $field_query,
"An error occured while inserting information into the database.");
return false;
}
return true;
}
# Updates a row within the table
function updateRow($table, $field_list, $field_value_list, $where_list, $where_value_list) {
# Assemble both parts of query SET and WHERE
if (!$set_query = $this->getFieldQuery($field_list, $field_value_list, "SET"))
return false;
if (!$where_query = $this->getFieldQuery($where_list, $where_value_list, "WHERE"))
return false;
# Assemble query
$query = sprintf("UPDATE $table SET $set_query WHERE $where_query");
$result = mysql_query($query, $this->_link);
if (!$result) {
$this->printError("[b]Error Occured:[/b] UPDATING fields" . $set_query . " with WHERE " . $where_query,
"An error occured while updating information within the database.");
return false;
}
return true;
}
# Prints an error message to the screen. If we are in debug mode that error is printed, if not the other more user friendly message is printed.
function printError($debug_error_msg, $clean_error_msg) {
if ($this->_mode == "debug")
echo "
" . $debug_error_msg . "
";
else
echo "
" . $clean_error_msg . "
";
}
# Returns an escaped array of items within a field list. Query type determines how this information is formatted so it can be added to a query
function getFieldQuery($field_list, $field_value_list, $query_type) {
$field_list_query = "";
$field_list_array = split(" ", $field_list); # Example $field_list = "where_key1 where_key2 where_key3"
$field_value_array = split(" ", $field_value_list); # Example $field_value_list = "where_value1 where_value2 where_value3"
# Ensure there are the same amount of field keys as there are values
if (!$this->compareFieldArrays($field_list_array, $field_value_array))
return false; # Error occured (compareFieldArrays will display the error)
# SELECT * FROM table WHERE field_key = 'field_value' AND field_key = 'field_value' #
if ($query_type == "WHERE") {
# Within the loop we will assemble the array into something that can be put into a query. We will also escape each element of
# both arrays to ensure they are safe.
for ($i = 0; $i < count($field_list_array); $i++) {
# Escape both array elements
$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);
# Assemble the query
# Example: "field_key = 'field_value'"
# and for more than one element: " AND field_key = 'field_value'"
if ($i > 0)
$field_list_query .= " AND ";
$field_list_query .= $field_list_array[$i] . "='" . $field_value_array[$i] ."'";
}
# Returned string should look something like this: "field_key = 'field_value' AND field_key = 'field_value'"
return $field_list_query;
}
# INSERT INTO table(field_key, field_key) VALUES('field_value', 'field_value') #
if ($query_type == "INSERT") {
# Assemble query
# Example query Keys: "(field_key,field_key,field_key)"
# Example query Values: " 'field_value','field_value','field_value'"
$field_list_query = "(";
$field_value_query = "VALUES (";
for ($i = 0; $i < count($field_list_array); $i++) {
# Escape both array elements
$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);
# Assemble both querys
if ($i > 0) {
$field_list_query .= ",";
$field_value_query .= ", ";
} else {
$field_list_query .= $field_list_array[$i];
$field_value_query .= "'" . $field_value_array[$i] . "'";
}
# Tidy up end if field querys and add them both together
$field_list_query .= ") ";
$field_value_query .= ")";
# Returned string should look something like this:
# "(field_key,field_key,field_key) VALUES ('field_value','field_value','field_value')"
return ($field_list_query .= $field_value_query);
}
}
# UPDATE table SET field_key = 'field_value' (plus add a where statement) #
if ($query_type == "SET") {
for ($i = 0; $i < count($field_list_array); $i++) {
# Escape both array elements
$field_list_array[$i] = mysql_real_escape_string($field_list_array[$i]);
$field_value_array[$i] = mysql_real_escape_string($field_value_array[$i]);
# Assemble the query
# Example: "field_key = 'field_value'"
# and for more than one element: " AND field_key = 'field_value'"
if ($i > 0)
$field_list_query .= ", ";
$field_list_query .= $field_list_array[$i] . "='" . $field_value_array[$i] ."'";
}
# Returned string should look something like this: "field_key = 'field_value', field_key = 'field_value'"
return $field_list_query;
}
$this->printError("[b]Error Occured:[/b] Query Type: " . $query_type . "not found.",
"An error occured while compiling a query for the database.");
return false;
}
# Compares 2 "field" arrays to ensure they are the same size.
function compareFieldArrays($field_list_array, $field_value_array) {
# Check if amount of fields and values being inserted match up
if (count($field_list_array) != count ($field_value_array)) {
$this->printError("[b]Error Occured:[/b] Field list: " . $field_list . "does not have the same amount of values as: " . $field_value_list,
"An error occured while compiling a query for the database.");
return false;
}
return true;
}
}
?>
Ps, if this does indeed work, anyone can use it. I don't care if you don't mention I made it :P it didn't take long ^_^
