Created: 11/04/2012
By: Conrad Warhol
Email: conradwarhol@gmail.com
Thank you for purchasing this class. If you have any questions that are beyond the scope of this help file, please feel free to email via my user page contact form here. Thanks so much!
smplPDO is a simple and light-weight class written to extend PHP Data Objects (PDO) with extremely useful features. Shorthand methods packed with it can reduce the amount of duplicate code and increase readability of your scripts as well as improve security and performance with automatically preparing/executing prepared statements.
Here is a good old method to fetch rows as asscoiative arrays with native mysql_* functions:
$name = mysql_real_escape_string( $_GET['name'] );
$query = mysql_query( "SELECT * FROM users WHERE name=$name" );
$results = array();
while( $row = mysql_fetch_assoc( $query ) ) {
$results[] = $row;
}
Consider a visitor loading the URL that executes this script with a parameter including something like ";DELETE FROM users", that would clean up all the records in users table. That's why you have to escape any string passed from the visitor to the script. Also with more complicated queries; saving, rebuilding the string, and also reparsing on the server will result in performance losses. To avoid these penalties, you need to use prepared statements.
/* Same query above with PDO */ $stmt = $dbh->prepare( "SELECT * FROM users WHERE name=:name" ); $stmt->execute( array( ':id'=>$_GET['name'] ) ); $results = $stmt->fetchAll( PDO::FETCH_ASSOC );
This is a good practise for both security and performance reasons. You don't need to escape the variable, you just need to bind it to the PDO statement object and rest will be handled automatically. Awesome!
But wait... What if i told you that you can do the exactly same thing in just one line of code? Yes, this is what smplPDO will do for you!
$results = $db->get_all( 'users', array( 'name'=>$_GET['name'] ) );
This will prepare the statement and execute it with bind parameters automatically without any additional steps. It's simple, readable, safe and beautiful!
As a child class of PDO, smplPDO inherits all the features of parent class PDO and can be used as a replacement to it. This file only includes usage examples of smplPDO spesific features. To learn more about what you can do with PDO please refer to official PHP documentation: http://php.net/manual/en/book.pdo.php
Please check out Example Code to see more of what smplPDO can do for you.
As advised by many experts; smplPDO accepts the same arguments with PDO.
To establish connection with a database:
// Include class file in your script.
require('/path/to/smplPDO.php');
$db_host = 'localhost';
$db_name = 'database';
$db_user = 'db_username';
$db_pass = 'db_password';
// init the class same as you do with PDO
$db = new smplPDO( "mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass );
*** Alternatively you may hardcode your database credentials into the class. Please check out smplPDO.php file source for more info.
You may execute any SQL query you like with run() method. Please note that, these queries are open to SQL Injections and should not be used with input from the public access.
Refer to Prepared Statements to see better practises.
// Regular INSERT, UPDATE and DELETE queries:
$db->run("INSERT INTO `users` (`name`,`age`) VALUES ('John Doe',28)");
echo $db->num_rows; // output the number of rows inserted.
// or echo $db->insert_id; to output the ID of last inserted record
$db->run("UPDATE `users` SET `age`=29 WHERE `name`='John Doe'");
echo $db->num_rows; // output the number of rows updated.
$db->run("DELETE FROM `users` WHERE `name`='John Doe'");
echo $db->num_rows; // output the number of rows deleted.
For SELECT queries run() method returns a PDO statement and it can be chained with PDO fetch methods. Results are returned as an associative array as column=>value pairs.
// Select all mathcing records (See also: GET_ALL shorthand method) $users = $db->run( "SELECT * FROM `users`" )->fetchAll(); // Select a single row (See also: GET_ROW shorthand method) $user = $db->run( "SELECT * FROM `users` WHERE id=1" )->fetch(); // Select a column (See also: GET_COL shorthand method) $emails = $db->run( "SELECT `email` FROM `users`" )->fetchAll( PDO::FETCH_COLUMN, 0 ); // Select a single variable (See also: GET_VAR shorthand method) $email = $db->run( "SELECT `email` FROM `users` WHERE `id`=1" )->fetchColumn();
If you don't want to worry about SQL injections you must always use prepared statements in your scripts. It may seem a bit confusing with native PDO at first. But it is extremely easy to execute prepared statements with smplPDO. Write a query to prepare with placeholders and just add a second argument to run() method including the bind parameters as placeholder=>value pairs. That's all.
Note that all the Shorthand Methods of smplPDO automatically executes your SQL query as a prepared statements.
// Readable: $db->sql = "INSERT INTO `users` (`name`,`email`,`age`) VALUES (:name,:email,:age)"; $db->bind = array( ':name'=>'John Doe', ':email'=>'john@doe.com', ':age'=>28 ); $db->run(); echo $db->insert_id; // output the ID of last inserted record // ...or as a one-liner: $db->run( "INSERT INTO `users` (`name`,`email`,`age`) VALUES (:name,:email,:age)", array( ':name'=>'John Doe', ':email'=>'john@doe.com', ':age'=>28 ) ); echo $db->insert_id; // output the ID of last inserted record // DELETE example if( $db->run( "DELETE FROM `users` WHERE `id`=:id", array( ':id'=>1 ) ) ) echo $db->num_rows . ' users deleted!'; // SELECT using prepared statements $db->run( "SELECT `email` FROM `users` WHERE `id`=:id", array( 'id'=>1 ) )->fetch();
smplPDO is packed with extremely useful shorthand methods. All these methods creates placeholders and builds prepared statements for your queries automatically.
Where conditions accepted by these methods are merged together with AND conditional. If you require using other conditionals or additional statements, you must use run() method instead.
Please refer to class file for argument explanations if you have any doubts.
NOTE: With any shorthand methods below, table and column names are automatically enclosed with back-quotes.
Insert a single record to the database table specified.
// insert( table_name [string], insert_data [assoc. array] ); $db->insert( 'users', array( 'name'=>'John Doe', 'email'=>'john@doe.com', 'age'=>28 ) );
Update matching records in the database table specified.
// update( table_name [string], update_data [assoc. array], where_cond. [assoc array] );
$db->update( 'users', array( 'age'=>29 ), array('name'=>'John Doe') );
// you can use duplicate fields between data and where arrays.
$db->update( 'users', array( 'age'=>29 ), array( 'age'=>28 ) );
Delete matching records in the database table specified.
// delete( table_name [string] where_cond.[assoc array] ); $db->delete( 'users', array( 'age'=>29 ) );
get() method helps you select data from your database using prepared statements in simple one-liners.
You will probably be using get_* methods instead in production.
// get( table_name [string], where_condition [array], fetch_type [string])
// fourth and last argument defines a fetch type. Can be 'all', 'row', 'col' or 'var'
// fetch all matching records ar an array of arrays:
$db->get( 'users', array( 'age'=>29 ), array('name','email'), 'all' );
// fetch one single row as associative array:
$db->get( 'users', array( 'age'=>29 ), array('name','email'), 'row' );
// fetch one column as numerical indexed array:
$db->get( 'users', array( 'age'=>29 ), array('name','email'), 'col' );
// fetch a single variable:
$db->get( 'users', array( 'age'=>29 ), array('name','email'), 'var' );
*** Do not enclose field names with back-quotes when using within an array type argument. They will be enclosed automatically.
Select all mathcing records.
Returns a numerical indexed array, each element is an array of column=>value pairs.
$db->get_all( 'users', array( 'age'=>29 ), array('name','email') );
// same with:
$db->get( 'users', array( 'age'=>29 ), array('name','email'), 'all' );
Select one single row.
Returns an array of column=>value pairs.
$db->get_row( 'users', array( 'age'=>29 ) ); // same with: $db->get( 'users', array( 'age'=>29 ), '*', 'row' );
Select a column.
Returns a numerical indexed array of values.
$db->get_col( 'users', array( 'age'=>29 ), array('email') );
// same with:
$db->get( 'users', array( 'age'=>29 ), array('email'), 'col' );
Select a single variable.
Returns a string.
// can be used like:
$db->get_var( 'users', array( /* no where conditions */ ), 'MIN(age)' );
// ...or
$db->get_var( 'users', array( 'age'=>29 ), array('email') );
// same with:
$db->get( 'users', array( 'age'=>29 ), array('email'), 'var' );
For each call to a get_* method, you may also provide order by, group by and limit parts to append to the query. Note that these properties will be nulled after each query execution and they will only be available to the first query following them.
$db->group_by = "`age`"; // group rows by age field. $db->order_by = "`id` DESC"; // order by id field descending. $db->limit = "0,10"; // get 10 rows starting from 0. $db->get_all( 'users' );
Another useful shorthand function to retrieve the count of mathcing records in a table.
// Count of matching records can be retrieved like:
$db->get_all( 'users' );
$user_count = $db->num_rows;
// ...but it is expensive because get_all fetches all the mathcing rows.
// get_count method just fetches COUNT(*)
$user_count = $db->get_count( 'users', array( 'age'=>29 ) );
// same with:
$user_count = $db->run("SELECT COUNT(*) FROM users WHERE age=29")->fetchColumn();
This is a shorthand method for quickly checking for a record in database with given conditions. Returns true if any matching records found in database and returns false if not.
$db->exists( 'users', array( 'age'=>29 ) );
Executed query is stored in $sql property.
$db->get_all( 'users' ); echo $db->sql; // outputs: SELECT * FROM `user` WHERE 1;
Result of the latest query is stored in $result property.
$db->get_all( 'users' ); print_r( $this->last_result ); // outputs the whole users table
You can access the number of rows affected by the last query with $num_rows property. Works with select queries too.
$db->get_all( 'users' ); echo $db->num_rows;
If last query executed was an INSERT, you can access the ID of last inserted record with $insert_id property.
$db->insert( 'users', array('email'=>'john@doe.com') );
echo $db->insert_id;
smplPDO catches & stores the error message thrown by PDO in $error property.
// try to fetch from a non existant table $db->get_all( 'useers' ); echo $db->error; // outputs: Base table or view not found error.
To see a clean dump of all properties above, you may use debug() method.
$db->get_var('usser',null,'email');
$db->debug();
// smplPDO - PHP Mysql Database Class
// Error Catched:
// SQLSTATE[42S02]: Base table or view not found:
// 1146 Table 'database.usser' doesn't exist
// Last Query:
// SELECT `email` FROM `usser` WHERE 1;
// © 2012 - Conrad Warhol - conradwarhol@gmail.com
Combining shorthand smplPDO methods; you can eliminate a lot of duplicate code and write your scripts faster & within less steps. Please check the examples below and compare the number of lines required to do the same job with either using regular mysql_* functions or PDO prepared statements.
Please note that these are not the best practises. They're just quick & dirty examples to to demonstrate capabilities of smplPDO class.
Store visitor hits to your site:
// scenario: get the ip address of visitor and check if she visited before.
// if so increase her hit count, if this is her first visit add her ip to the table.
//
// table: visitors
// fields: ip, hits
$ip = array( 'ip'=>$_SERVER['REMOTE_ADDR'] );
if( $db->exists( 'visitors', $ip ) ) {
$h = $db->get_var( 'visitors', $ip, 'hits' );
$db->update( 'visitors', array( 'hits'=>(int)$h+1 ), $ip );
} else $db->insert( 'visitors', $ip + array( 'hits'=>1 ) );
Send email to all users:
// scenario: loop through all records in users table, check if they're mailed
// if not, mail them nicely with their name and mark as 'mailed'.
//
// table: users
// fields: id, name, email, mailed
foreach( $db->get_all( 'users' ) as $user ) {
if( ! $user['mailed'] && mail( $user['email'], "Hello $user[name]", "Hey!" ) )
$db->update( 'users', array( 'mailed'=>1 ), array( 'id'=>$user['id'] ) );
}
A very simple user login:
// scenario: Check if the user exists by submitted email. If so check if the submitted
// password is correct. If so let the user in.
//
// table: users
// fields: id, name, email, mailed
$email = array( 'email'=>$_POST['email'] );
if( ! $db->exists( 'users', $email ) )
exit('User not found!');
else if( $db->get_var( 'users', $email, 'pwd' ) != md5( $_POST['pwd'] ) )
exit('Wrong Password');
else
echo 'User Logged In!';
Some math:
// scenario: calculate the average age of users
//
// table: users
// fields: id, name, email, age
$av_age = floor( array_sum( $db->get_col( 'users', null, 'age' ) ) / $db->num_rows );
// or
$av_age = $db->run("SELECT ROUND( AVG( `age` ) ) FROM `users`")->fetchColumn();
Please send and email to me at conradwarhol@gmail.com for any difficulties you may experience using this class. Bug reports and feature requests are very welcome too. I will do my best to answer all your emails quickly.
Once again, thank you so much for purchasing this class. If you have a more general question relating to the PHP scripts on codecanyon.net, you might consider visiting the forums and asking your question in the "Item Discussion" section.
Conrad Warhol