diff options
| -rw-r--r-- | Db.class.php | 267 | ||||
| -rw-r--r-- | LICENSE | 7 | ||||
| -rw-r--r-- | Log.class.php | 71 | ||||
| -rw-r--r-- | README.md | 260 |
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; + } +} +?> @@ -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 + |