<?PHP
//File Name: MyEzSQL.class.php
class MyEzSQL{
/*
MyEzSQL is a class written in PHP <www.php.net>. It allows users to
interact with a database server with extreme ease. Documentation
on how to use can be found at <www.drwsoftware.org>.
Copyright (C) 2009 Lewis Ferguson <lewisferguson_011[at]msn[dot]com>
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/>.
*/
private $lastSQLStatement = "";
private $lastSQLExec = "";
private $lastError = "";
private $DB = "";
private $flag = "";
private $DBHost = "";
private $DBPort = "";
private $DBUser = "";
private $DBPass = "";
private $DBName = "";
//Set the login details
function setLogin($host, $port, $user, $pass, $name){
if(empty($host) || empty($user) || empty($name)){
$this->getError("Cannot continue, login details not filled out.");
}
$this->DBHost = $host;
$this->DBPort = $port; //Fill in if required
$this->DBUser = $user;
$this->DBPass = $pass;
$this->DBName = $name;
}
//Debugging:
function setFlag($flag, $setting){
//Flags available:
$Flags = array("recordSQL", "outputSQL");
$settings = array("recordSQL" => array(TRUE, FALSE),
"outputSQL" => array(TRUE, FALSE));
//Check that the flag sent exists:
for($i = 0; $i < count($Flags); $i++){
if($flag == $Flags[$i]){
$flagOK = 1;
$i = count($Flags);
}
}
//Check that the setting that has been sent is ok:
for($i = 0; $i < count($settings[$flag]); $i++){
if($setting == $settings[$flag][$i] ){
$setOK = 1;
$i = count($settings[$flag]);
}
}
if($flagOK != 1 || $setOK != 1){
die("");
}
$this->flag[$flag] = $setting;
if(count($this->flag) < count($Flags)){
for($i = 0; $i < count($Flags); $i++){
if(!isset($this->flag[$Flags[$i]])){
$this->flag[$Flags[$i]] = FALSE;
}
}
}
}
function recordSQL(){
$fh = fopen("logs/sql-log-".date("d-m-y").".txt", "a");
fwrite($fh, $this->lastSQLStatement."\n");
fclose($fh);
}
function showLastSQL(){
echo $this->lastSQLStatement;
}
function getError($error, $sql = ""){
if(strlen($error) > 0){
$output = ("Error: ".$error."\n");
if(strlen($sql) > 0){
$output .= ("<br />The SQL executed was: ".$this->lastSQLStatement.".\n");
}
print ($output);
$output = str_replace("<br />", " - ", $output);
if($this->flag["recordSQL"]){
$this->recordSQL();
}
die();
}
}
//Connects to the database:
function Connect(){
if(!empty($port)){
$this->DB = @mysqli_connect($this->DBHost, $this->DBUser, $this->DBPass, $this->DBName, $this->DBPort);
}else{
$this->DB = mysqli_connect($this->DBHost, $this->DBUser, $this->DBPass, $this->DBName);
}
if(mysqli_connect_error()){
$this->getError("Cannot Connect to the database server. <br />MySQL Error Number: ". mysqli_connect_errno().";<br />MySQL Error: ".mysqli_connect_error());
return false;
}else{
return true;
}
}
//Allows you to select data from the database:
function Select($tbl, $col = "", $data = "", $extra = ""){
$this->lastSQLStatement = "";
$extras = "";
$tbl = $this->SQLSecure($tbl);
//Ensure all the data has been populated:
if(empty($tbl)){
//Throw error:
$this->getError("No tables and/or columns set!");
return false;
}
//Ensure that the table name is secure.
$tbl = ( get_magic_quotes_gpc() ) ? $tbl : addslashes($tbl);
//Check if the user is wanting all the data from the table:
if(empty($col) || empty($data)){
$SQL = "SELECT * FROM `$tbl`;";
}else{
if(is_array($col) === TRUE && is_array($data) === TRUE){
if(count($col) == count($data)){
//Build up the SQL here:
$SQL = "SELECT * FROM `".$tbl."` WHERE ";
for($i = 0; $i < count($col); $i++){
$col[$i] = $this->SQLSecure($col[$i]);
$data[$i] = $this->SQLSecure($data[$i]);
$SQL .= "`".$col[$i]."` = '".$data[$i]."'";
$p = $i + 1;
if($p != count($col)){
$SQL .= " AND ";
}
}
}else{
$this->getError("Error: Invalid data sent");
return false;
}
}else{
$col = $this->SQLSecure($col);
$data = $this->SQLSecure($data);
$SQL = "SELECT * FROM `".$tbl."` WHERE `".$col."` = '".$data."' ";
}
if(is_array($extra) && count($extra) < 3){
//These cannot be changed although you may add your own here:
$accepted_extra = array("asc", "desc", "limit");
for($i = 0; $i < count($accepted_extra); $i++){
if($extra[$accepted_extra[$i]] != ""){
$key = $this->SQLSecure($extra[$accepted_extra[$i]]);
if($accepted_extra[$i] == "asc" || $accepted_extra[$i] == "desc"){
$extras .= "ORDER BY `$key`";
if($extra['asc'] != ""){
$extra['desc'] = "";
$extras .= " ASC ";
}else{
$extras .= " DESC ";
}
}else{
$extras .= " LIMIT $key ";
}
}
}
}
$SQL = $SQL.$extras;
}
$this->lastSQLStatement = $SQL;
if($this->flag['recordSQL']){
$this->recordSQL();
}
if($this->flag['outputSQL']){
$this->showLastSQL();
}
if($Q = @mysqli_query($this->DB, $SQL)){
$this->lastSQLExec = $Q;
return true;
}else{
$this->getError("Error: ".mysqli_error(), $SQL);
return false;
}
}
//Insert data to the database:
function Insert($tbl, $data){
//Sets all the variables:
$cols = array();
$cols_use = array();
$values_use = array();
$tbl = $this->SQLSecure($tbl);
//We need to get ALL the columns from the table in $tbl;
$pulledCols = mysqli_query($this->DB, "SHOW COLUMNS FROM ".$tbl) or die("MySQL Error: ".mysqli_error);
while($columns = mysqli_fetch_assoc($pulledCols)){
$cols[] = $columns['Field'];
}
foreach($data as $key => $value){
if(in_array($key, $cols) && trim($value)){
if($value == "DATESTAMP"){
$value_use[] = "NOW()";
}else{
if(is_numeric($value) === TRUE){
$values_use[] = $this->SQLSecure($value);
}else{
$values_use[] = "'".$this->SQLSecure($value)."'";
}
$cols_use[] = "`".$this->SQLSecure($key)."`";
}
}
}
if( (sizeof($cols_use) == 0 ) || (sizeof($values_use) == 0)){
$this->getError("Error: There was no data passed!");
return false;
}else{
$SQL = "INSERT INTO `".$tbl."` (".implode(",",$cols_use). ") VALUES (".implode(",",$values_use). ")";
$this->lastSQLStatement = $SQL;
if($this->flag['recordSQL']){
$this->recordSQL();
}
if($this->flag['outputSQL']){
$this->showLastSQL();
}
if(@mysqli_query($this->DB, $SQL)){
return true;
}else{
$this->getError("Error: ".mysqli_error(), $SQL);
return false;
}
}
}
//Allows users to update data in the database:
function Update($tbl, $upd_col, $upd_data, $def_col, $def_data){
$this->lastSQLStatement = "";
//Ensure all the data has been populated:
if(empty($tbl) || empty($upd_col) || empty($upd_data) || empty($def_col) || empty($def_data)){
//Throw error:
$this->getError("Error: one of the vars wasn't populated!");
return false;
}
//Secure the data:
$tbl = $this->SQLSecure($tbl);
$upd_col = $this->SQLSecure($upd_col);
$upd_data = $this->SQLSecure($upd_data);
$def_col = $this->SQLSecure($def_col);
$def_data = $this->SQLSecure($def_data);
$SQL = "UPDATE `$tbl` SET ";
if(is_array($upd_col) === TRUE && is_array($upd_data) === TRUE){
if(count($upd_col) == count($upd_data)){
for($i = 0; $i < count($upd_col); $i++){
if($upd_data == "DATESTAMP"){
$upd_data = "NOW()";
}
$SQL .= "`".$upd_col[$i]."` = '".$upd_data."' ";
if($i + 1 != count($upd_col)){
$SQL .= ", ";
}
}
}else{
$this->getError("Error: Invalid data sent.");
return false;
}
}elseif(!is_array($upd_col) && !is_array($upd_data)){
$SQL .= "`".$upd_col."` = '".$upd_data."'";
}else{
$this->getError("Error: Invalid data sent.");
return false;
}
if(is_array($def_col) === TRUE){
if(is_array($def_data) === TRUE && count($col) == count($data)){
//Build up the SQL here:
$SQL .= " WHERE ";
for($i = 0; $i < count($col); $i++){
$SQL .= "`".$def_col[$i]."` = '".$def_data[$i]."'";
if($i + 1 != count($col)){
$SQL .= " AND ";
}
}
}else{
$this->getError("Error: Invalid data sent");
return false;
}
}else{
$SQL .= " WHERE `".$def_col."` = '".$def_data."';";
}
if($this->flag['recordSQL']){
$this->recordSQL();
}
if($this->flag['outputSQL']){
$this->showLastSQL();
}
if(@mysqli_query($this->DB, $SQL)){
$this->lastSQLStatement = $SQL;
return true;
}else{
$this->getError("Error: ".mysqli_error(), $SQL);
return false;
}
}
//Deletes specified information from the database;
function Delete($tbl, $col, $data){
$this->lastSQLStatement = "";
//Ensure all the data has been populated:
if(empty($tbl) || empty($col) || empty($data)){
//Throw error:
$this->getError("Error: No table and/or column set!");
return false;
}
//Secure the data:
$tbl = $this->SQLSecure($tbl);
$col = $this->SQLSecure($col);
$data = $this->SQLSecure($data);
if(is_array($col) === TRUE){
if(is_array($data) === TRUE && count($col) == count($data)){
//Build up the SQL here:
$SQL = "DELETE FROM `".$tbl."` WHERE ";
for($i = 0; $i < count($col); $i++){
$SQL .= "`".$col[$i]."` = '".$data[$i]."'";
if($i + 1 != count($col)){
$SQL .= " AND ";
}
}
}else{
$this->getError("Error: Invalid data sent");
return false;
}
}else{
$SQL = "DELETE FROM `".$tbl."` WHERE `".$col."` = '".$data."';";
}
$this->lastSQLStatement = $SQL;
if($this->flag['recordSQL']){
$this->recordSQL();
}
if($this->flag['outputSQL']){
$this->showLastSQL();
}
if(@mysqli_query($this->DB, $SQL)){
return true;
}else{
$this->getError("Error: ".mysqli_error(), $SQL);
return false;
}
}
//Return an assoc array:
function getArray(){
//This gets the array data from the last query executed.
//Check for and ensure that the first word in the SQL is SELECT:
if(strpos($this->lastSQLStatement, "SELECT") == 0){
return mysqli_fetch_assoc($this->lastSQLExec);
}else{
$this->getError("The last SQL run WAS NOT a select command, it was:" . $this->lastSQLStatement);
$this->lastSQlStatement = "";
return false;
}
}
//Returns the number of rows from a select command:
function numRows(){
//This gets the number of rows from the last query executed.
//Check for and ensure that the first word in the SQL is SELECT:
if(strpos($this->lastSQLStatement, "SELECT") == 0){
return mysqli_num_rows($this->lastSQLExec);
}else{
$this->getError("The last SQL run was NOT a select command, it was:" . $this->lastSQLStatement);
$this->lastSQlStatement = "";
return false;
}
}
//Secures the data before being entered into the database:
function SQLSecure($data){
if(!$this->DB){
$data = addslashes($data);
}else{
$data = mysqli_real_escape_string($this->DB, $data);
}
return $data;
}
//Closes the database connection:
function Close(){
@mysqli_close($this->DB);
}
}
?>