Location: PHPKode > scripts > MyEzSQL > myezsql-2009-04-05/Class/MyEzSQL.class.php
<?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);
		}
}
?>
Return current item: MyEzSQL