Merge branch 'master' of github.com:jayc89/php-mysql-pdo-database-class

4 files changed, 605 insertions, 0 deletions

diff --git a/Db.class.php b/Db.class.php
new file mode 100644
index 0000000..cf0e4d8
--- /dev/null
+++ b/Db.class.php
@@ -0,0 +1,267 @@
+<?php
+/**
+ *  DB - A simple database class 
+ *
+ * @author		Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal)
+ * @git 		https://github.com/indieteq/PHP-MySQL-PDO-Database-Class
+ * @version      0.2ab
+ *
+ */
+require("Log.class.php");
+class DB
+{
+	# @object, The PDO object
+	private $pdo;
+
+	# @object, PDO statement object
+	private $sQuery;
+
+	# @array,  The database settings
+	private $settings;
+
+	# @bool ,  Connected to the database
+	private $bConnected = false;
+
+	# @object, Object for logging exceptions	
+	private $log;
+
+	# @array, The parameters of the SQL query
+	private $parameters;
+		
+       /**
+	*   Default Constructor 
+	*
+	*	1. Instantiate Log class.
+	*	2. Connect to database.
+	*	3. Creates the parameter array.
+	*/
+		public function __construct()
+		{ 			
+			$this->log = new Log();	
+			$this->Connect();
+			$this->parameters = array();
+		}
+	
+       /**
+	*	This method makes connection to the database.
+	*	
+	*	1. Reads the database settings from a ini file. 
+	*	2. Puts  the ini content into the settings array.
+	*	3. Tries to connect to the database.
+	*	4. If connection failed, exception is displayed and a log file gets created.
+	*/
+		private function Connect()
+		{
+			global $settings;
+			$dsn = 'mysql:dbname='.$settings["dbname"].';host='.$settings["dbhost"].'';
+			try 
+			{
+				# Read settings from INI file, set UTF8
+				$this->pdo = new PDO($dsn, $settings["dbuser"], $settings["dbpass"], array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
+				
+				# We can now log any exceptions on Fatal error. 
+				$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+				
+				# Disable emulation of prepared statements, use REAL prepared statements instead.
+				$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
+				
+				# Connection succeeded, set the boolean to true.
+				$this->bConnected = true;
+			}
+			catch (PDOException $e) 
+			{
+				# Write into log
+				echo $this->ExceptionLog($e->getMessage());
+				die();
+			}
+		}
+	/*
+	 *   You can use this little method if you want to close the PDO connection
+	 *
+	 */
+	 	public function CloseConnection()
+	 	{
+	 		# Set the PDO object to null to close the connection
+	 		# http://www.php.net/manual/en/pdo.connections.php
+	 		$this->pdo = null;
+	 	}
+		
+       /**
+	*	Every method which needs to execute a SQL query uses this method.
+	*	
+	*	1. If not connected, connect to the database.
+	*	2. Prepare Query.
+	*	3. Parameterize Query.
+	*	4. Execute Query.	
+	*	5. On exception : Write Exception into the log + SQL query.
+	*	6. Reset the Parameters.
+	*/	
+		private function Init($query,$parameters = "")
+		{
+		# Connect to database
+		if(!$this->bConnected) { $this->Connect(); }
+		try {
+				# Prepare query
+				$this->sQuery = $this->pdo->prepare($query);
+				
+				# Add parameters to the parameter array	
+				$this->bindMore($parameters);
+
+				# Bind parameters
+				if(!empty($this->parameters)) {
+					foreach($this->parameters as $param)
+					{
+						$parameters = explode("\x7F",$param);
+						$this->sQuery->bindParam($parameters[0],$parameters[1]);
+					}		
+				}
+
+				# Execute SQL 
+				$this->succes 	= $this->sQuery->execute();		
+			}
+			catch(PDOException $e)
+			{
+					# Write into log and display Exception
+					echo $this->ExceptionLog($e->getMessage(), $query );
+					die();
+			}
+
+			# Reset the parameters
+			$this->parameters = array();
+		}
+		
+       /**
+	*	@void 
+	*
+	*	Add the parameter to the parameter array
+	*	@param string $para  
+	*	@param string $value 
+	*/	
+		public function bind($para, $value)
+		{	
+			$this->parameters[sizeof($this->parameters)] = ":" . $para . "\x7F" . $value;
+		}
+       /**
+	*	@void
+	*	
+	*	Add more parameters to the parameter array
+	*	@param array $parray
+	*/	
+		public function bindMore($parray)
+		{
+			if(empty($this->parameters) && is_array($parray)) {
+				$columns = array_keys($parray);
+				foreach($columns as $i => &$column)	{
+					$this->bind($column, $parray[$column]);
+				}
+			}
+		}
+       /**
+	*   	If the SQL query  contains a SELECT or SHOW statement it returns an array containing all of the result set row
+	*	If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
+	*
+	*   	@param  string $query
+	*	@param  array  $params
+	*	@param  int    $fetchmode
+	*	@return mixed
+	*/			
+		public function query($query,$params = null, $fetchmode = PDO::FETCH_ASSOC)
+		{
+			$query = trim($query);
+
+			$this->Init($query,$params);
+
+			$rawStatement = explode(" ", $query);
+			
+			# Which SQL statement is used 
+			$statement = strtolower($rawStatement[0]);
+			
+			if ($statement === 'select' || $statement === 'show') {
+				return $this->sQuery->fetchAll($fetchmode);
+			}
+			elseif ( $statement === 'insert' ||  $statement === 'update' || $statement === 'delete' ) {
+				return $this->sQuery->rowCount();	
+			}	
+			else {
+				return NULL;
+			}
+		}
+		
+      /**
+       *  Returns the last inserted id.
+       *  @return string
+       */	
+		public function lastInsertId() {
+			return $this->pdo->lastInsertId();
+		}	
+		
+       /**
+	*	Returns an array which represents a column from the result set 
+	*
+	*	@param  string $query
+	*	@param  array  $params
+	*	@return array
+	*/	
+		public function column($query,$params = null)
+		{
+			$this->Init($query,$params);
+			$Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);		
+			
+			$column = null;
+
+			foreach($Columns as $cells) {
+				$column[] = $cells[0];
+			}
+
+			return $column;
+			
+		}	
+       /**
+	*	Returns an array which represents a row from the result set 
+	*
+	*	@param  string $query
+	*	@param  array  $params
+	*   	@param  int    $fetchmode
+	*	@return array
+	*/	
+		public function row($query,$params = null,$fetchmode = PDO::FETCH_ASSOC)
+		{				
+			$this->Init($query,$params);
+			return $this->sQuery->fetch($fetchmode);			
+		}
+       /**
+	*	Returns the value of one single field/column
+	*
+	*	@param  string $query
+	*	@param  array  $params
+	*	@return string
+	*/	
+		public function single($query,$params = null)
+		{
+			$this->Init($query,$params);
+			return $this->sQuery->fetchColumn();
+		}
+       /**	
+	* Writes the log and returns the exception
+	*
+	* @param  string $message
+	* @param  string $sql
+	* @return string
+	*/
+	private function ExceptionLog($message , $sql = "")
+	{
+		$exception  = 'Unhandled Exception. <br />';
+		$exception .= $message;
+		$exception .= "<br /> You can find the error back in the log.";
+
+		if(!empty($sql)) {
+			# Add the Raw SQL to the Log
+			$message .= "\r\nRaw SQL : "  . $sql;
+		}
+			# Write into log
+			$this->log->write($message);
+
+		return $exception;
+	}			
+}
+?>
diff --git a/LICENSE b/LICENSE
new file mode 100644
index 0000000..1a4140c
--- /dev/null
+++ b/LICENSE
@@ -0,0 +1,7 @@
+/*
+ * ----------------------------------------------------------------------------
+ * "THE BEER-WARE LICENSE" 
+ * If we meet some day, and you think
+ * this stuff is worth it, you can buy me a beer in return.
+ * ----------------------------------------------------------------------------
+ */
\ No newline at end of file
diff --git a/Log.class.php b/Log.class.php
new file mode 100644
index 0000000..4c066c3
--- /dev/null
+++ b/Log.class.php
@@ -0,0 +1,71 @@
+<?php 
+       /* *
+	* Log 			A logger class which creates logs when an exception is thrown.
+	* @author		Author: Vivek Wicky Aswal. (https://twitter.com/#!/VivekWickyAswal)
+	* @git 			https://github.com/indieteq/PHP-MySQL-PDO-Database-Class
+	* @version      0.1a
+	*/
+	class Log {
+			
+		    # @string, Log directory name
+		    	private $path = '/logs/';
+			
+		    # @void, Default Constructor, Sets the timezone and path of the log files.
+			public function __construct() {
+				global $root;
+				date_default_timezone_set('Europe/Amsterdam');	
+				$this->path  = $root.$this->path;	
+			}
+			
+		   /**
+		    *   @void 
+		    *	Creates the log
+		    *
+		    *   @param string $message the message which is written into the log.
+		    *	@description:
+		    *	 1. Checks if directory exists, if not, create one and call this method again.
+	            *	 2. Checks if log already exists.
+		    *	 3. If not, new log gets created. Log is written into the logs folder.
+		    *	 4. Logname is current date(Year - Month - Day).
+		    *	 5. If log exists, edit method called.
+		    *	 6. Edit method modifies the current log.
+		    */	
+			public function write($message) {
+				$date = new DateTime();
+				$log = $this->path . $date->format('Y-m-d').".txt";
+
+				if(is_dir($this->path)) {
+					if(!file_exists($log)) {
+						$fh  = fopen($log, 'a+') or die("Fatal Error !");
+						$logcontent = "Time : " . $date->format('H:i:s')."\r\n" . $message ."\r\n";
+						fwrite($fh, $logcontent);
+						fclose($fh);
+					}
+					else {
+						$this->edit($log,$date, $message);
+					}
+				}
+				else {
+					  if(mkdir($this->path,0777) === true) 
+					  {
+ 						 $this->write($message);  
+					  }	
+				}
+			 }
+			
+			/** 
+			 *  @void
+			 *  Gets called if log exists. 
+			 *  Modifies current log and adds the message to the log.
+			 *
+			 * @param string $log
+			 * @param DateTimeObject $date
+			 * @param string $message
+			 */
+			    private function edit($log,$date,$message) {
+				$logcontent = "Time : " . $date->format('H:i:s')."\r\n" . $message ."\r\n\r\n";
+				$logcontent = $logcontent . file_get_contents($log);
+				file_put_contents($log, $logcontent);
+			    }
+		}
+?>
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..7b43f11
--- /dev/null
+++ b/README.md
@@ -0,0 +1,260 @@
+PDO Database Class
+============================
+
+A database class for PHP-MySQL which uses the PDO extension.
+
+If you have any questions go to : http://indieteq.com/index/readmore/how-to-prevent-sql-injection-in-php
+
+## To use the class
+#### 1. Edit the database settings in the settings.ini.php
+### Note if PDO is loading slow change localhost to -> 127.0.0.1 !
+```
+[SQL]
+host = 127.0.0.1
+user = root
+password = 
+dbname = yourdatabase
+```
+#### 2. Require the class in your project
+```php
+<?php
+require("Db.class.php");
+```
+#### 3. Create the instance 
+```php
+<?php
+// The instance
+$db = new Db();
+```
+#### 4.  Logs - Modify the read/write rights of the root folder
+
+Everytime an exception is thrown by the database class a log file gets created or modified.
+These logs are stored in the logs directory. Which means the database class needs write access for the logs folder.
+If the files are on a webserver you'll have to modify the rights of the root folder otherwise you'll get a "Permission denied" error.
+
+The log file is a simple plain text file with the current date('year-month-day') as filename.
+
+## Examples
+Below some examples of the basic functions of the database class. I've included a SQL dump so you can easily test the database
+class functions. 
+#### The persons table 
+| id | firstname | lastname | sex | age
+|:-----------:|:------------:|:------------:|:------------:|:------------:|
+| 1       |        John |     Doe    | M | 19
+| 2       |        Bob  |     Black    | M | 41
+| 3       |        Zoe  |     Chan    | F | 20
+| 4       |        Kona |     Khan    | M | 14
+| 5       |        Kader|     Khan    | M | 56
+
+#### Fetching everything from the table
+```php
+<?php
+// Fetch whole table
+$persons = $db->query("SELECT * FROM persons");
+```
+#### Fetching with Bindings (ANTI-SQL-INJECTION):
+Binding parameters is the best way to prevent SQL injection. The class prepares your SQL query and binds the parameters
+afterwards.
+
+There are three different ways to bind parameters.
+```php
+<?php
+// 1. Read friendly method  
+$db->bind("id","1");
+$db->bind("firstname","John");
+$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");
+
+// 2. Bind more parameters
+$db->bindMore(array("firstname"=>"John","id"=>"1"));
+$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));
+
+// 3. Or just give the parameters to the method
+$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname",array("firstname"=>"John","id"=>"1"));
+```
+
+More about SQL injection prevention : http://indieteq.com/index/readmore/how-to-prevent-sql-injection-in-php
+
+#### Fetching Row:
+This method always returns only 1 row.
+```php
+<?php
+// Fetch a row
+$ages     =  $db->row("SELECT * FROM Persons WHERE  id = :id", array("id"=>"1"));
+```
+##### Result
+| id | firstname | lastname | sex | age
+|:-----------:|:------------:|:------------:|:------------:|:------------:|
+| 1       |        John |     Doe    | M | 19
+#### Fetching Single Value:
+This method returns only one single value of a record.
+```php
+<?php
+// Fetch one single value
+$db->bind("id","3");
+$firstname = $db->single("SELECT firstname FROM Persons WHERE id = :id");
+```
+##### Result
+|firstname
+|:------------:
+| Zoe
+#### Fetching Column:
+```php
+<?php
+// Fetch a column
+$names    =  $db->column("SELECT Firstname FROM Persons");
+```
+##### Result
+|firstname | 
+|:-----------:
+|        John 
+|        Bob  
+|        Zoe  
+|        Kona 
+|        Kader
+### Delete / Update / Insert
+When executing the delete, update, or insert statement by using the query method the affected rows will be returned.
+```php
+<?php
+
+// Delete
+$delete   =  $db->query("DELETE FROM Persons WHERE Id = :id", array("id"=>"1"));
+
+// Update
+$update   =  $db->query("UPDATE Persons SET firstname = :f WHERE Id = :id", array("f"=>"Jan","id"=>"32"));
+
+// Insert
+$insert   =  $db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)", array("f"=>"Vivek","age"=>"20"));
+
+// Do something with the data 
+if($insert > 0 ) {
+  return 'Succesfully created a new person !';
+}
+
+```
+## Method parameters
+Every method which executes a query has the optional parameter called bindings.
+
+The <i>row</i> and the <i>query</i> method have a third optional parameter  which is the fetch style.
+The default fetch style is <i>PDO::FETCH_ASSOC</i> which returns an associative array.
+
+Here an example :
+
+```php
+<?php
+  // Fetch style as third parameter
+  $person_num =     $db->row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"), PDO::FETCH_NUM);
+
+  print_r($person_num);
+  // Array ( [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 )
+    
+```
+More info about the PDO fetchstyle : http://php.net/manual/en/pdostatement.fetch.php
+
+
+EasyCRUD
+============================
+The easyCRUD is a class which you can use to easily execute basic SQL operations like(insert, update, select, delete) on your database. 
+It uses the database class I've created to execute the SQL queries.
+
+Actually it's just a little ORM class.
+
+## How to use easyCRUD
+#### 1. First, create a new class. Then require the easyCRUD class.
+#### 2. Extend your class to the base class Crud and add the following fields to the class.
+#### Example class :
+```php
+<?php
+require_once("easyCRUD.class.php");
+ 
+class YourClass  Extends Crud {
+ 
+  # The table you want to perform the database actions on
+  protected $table = 'persons';
+
+  # Primary Key of the table
+  protected $pk  = 'id';
+  
+}
+```
+
+## EasyCRUD in action.
+
+#### Creating a new person
+```php
+<?php
+// First we"ll have create the instance of the class
+$person = new person();
+ 
+// Create new person
+$person->Firstname  = "Kona";
+$person->Age        = "20";
+$person->Sex        = "F";
+$created            = $person->Create();
+ 
+//  Or give the bindings to the constructor
+$person  = new person(array("Firstname"=>"Kona","age"=>"20","sex"=>"F"));
+$created = person->Create();
+ 
+// SQL Equivalent
+"INSERT INTO persons (Firstname,Age,Sex) VALUES ('Kona','20','F')"
+```
+#### Deleting a person
+```php
+<?php
+// Delete person
+$person->Id  = "17";
+$deleted     = $person->Delete();
+ 
+// Shorthand method, give id as parameter
+$deleted     = $person->Delete(17);
+ 
+// SQL Equivalent
+"DELETE FROM persons WHERE Id = 17 LIMIT 1"
+```
+#### Saving person's data
+```php
+<?php
+// Update personal data
+$person->Firstname = "John";
+$person->Age  = "20";
+$person->Sex = "F";
+$person->Id  = "4"; 
+// Returns affected rows
+$saved = $person->Save();
+ 
+//  Or give the bindings to the constructor
+$person = new person(array("Firstname"=>"John","age"=>"20","sex"=>"F","Id"=>"4"));
+$saved = $person->Save();
+ 
+// SQL Equivalent
+"UPDATE persons SET Firstname = 'John',Age = 20, Sex = 'F' WHERE Id= 4"
+```
+#### Finding a person
+```php
+<?php
+// Find person
+$person->Id = "1";
+$person->Find();
+
+echo $person->firstname;
+// Johny
+ 
+// Shorthand method, give id as parameter
+$person->Find(1); 
+ 
+// SQL Equivalent
+"SELECT * FROM persons WHERE Id = 1"
+```
+#### Getting all the persons
+```php
+<?php
+// Finding all person
+$persons = $person->all(); 
+ 
+// SQL Equivalent
+"SELECT * FROM persons 
+```
+
+## Copyright and license
+#### Code released under Beerware
+