Recommend this page to a friend! |
Download .zip |
Info | Documentation | View files (2) | Download .zip | Reputation | Support forum (2) | Blog | Links |
Last Updated | Ratings | Unique User Downloads | Download Rankings | |||||
2014-12-13 (1 year ago) | Not enough user ratings | Total: 629 This week: 1 | All time: 4,812 This week: 1,047 |
Version | License | PHP version | Categories | |||
simple-pdo-mysql 1.2 | GNU General Publi... | 5.2 | PHP 5, Databases |
Description | Author | |||||||||||||
This class is a wrapper to access MySQL databases using PDO. |
|
PDO variant of the SimpleMySQLi class, designed to use prepared queries while providing support for existing implementations using SimpleMySQLi.
This class is designed to return result sets as OBJECTS rather than arrays (in keeping with the whole OOP structure), so it isn't technically fully backward compatible with existing SimpleMySQLi implementations, however, the swap is fairly straightfoward:
//SimpleMySQLi get_row
list( $username ) = $db->get_row( "SELECT username FROM users WHERE user_id = 10 LIMIT 1" );
echo $username;
//SimplePDO get_row
$user = $db->get_row( "SELECT username FROM users WHERE user_id = 10 LIMIT 1" );
echo $user->username;
Although this class is designed to support normal (non prepared) AND the more secure prepared statement queries, obviously using prepared statements is the purpose of this class (the PDO implementation is mainly because it 'could' be done). That being said, the above query for this class _should_ actually look like...
$user = $db->get_row( "SELECT username FROM users WHERE user_id = ? LIMIT 1", array( 10 ) );
echo $user->username;
_Limitations:_ As of 29-Nov-2014, the "insert_multi()" function isnot* implemented in this class from SimpleMySQLi. * This class has so far only been fully tested for MySQL servers; support for SQLlite and postgres forthcoming.
Same as simplemysqli, you can initiate this class with a new instance, or the singleton:
require_once( 'SimplePDO.php' );
$params = array(
'host' => 'localhost',
'user' => 'root',
'password' => 'root',
'database' => 'yourmagicdatabase'
);
//Initiate the class as a new instance
try {
$database = new SimplePDO( $params );
} catch( PDOException $e ) {
//Do whatever you'd like with the error here
}
//OR use the singleton...
try {
$database = SimplePDO::getInstance( $params );
} catch( PDOException $e ) {
//Do whatever you'd like with the error here
}
This class can:
$clear_password = $database->query( "UPDATE users SET user_password = ? WHERE user_id = ?", array( 'NULL', 5 ) );
$all_users = $database->get_results( "SELECT user_name, user_email FROM users WHERE user_active = ?", array( 1 ) );
foreach( $all_users as $user )
{
echo $user->user_name .' '. $user->user_email .'<br />';
}
Using LIKE statements in prepared-statement-land requires that the actual array value be encapsulated with the percentage signs as follows...
//CORRECT
$results = $database->get_results( "SELECT user_name, user_email FROM users WHERE user_name LIKE ? AND user_email = ? LIMIT 10", array( '%some%', 'you@magic.com' ) );
foreach( $results as $user )
{
echo $user->user_name .' '. $user->user_email .'<br />';
}
//THIS WILL NOT WORK- DO NOT DO THIS...
$results = $database->get_results( "SELECT user_name, user_email FROM users WHERE user_name LIKE '%?%' AND user_email = ? LIMIT 10", array( 'some', 'you@magic.com' ) );
Unfortunately, to handle IN statements, some extra work is indeed required to handle parameter bindings for security PHP.net, but it's not too bad, and in this case, requires only a single extra line of code.
//List of user IDs to retrieve
$list = array( 1, 48, 51 );
//Map of prepared "?" statements to correspond
$prep_bindings = $database->prepare_in( $list );
//Run the query as usual
$in_list = $database->get_results( "SELECT user_name FROM users WHERE user_id IN($prep_bindings)", $list );
$user = $database->get_row( "SELECT user_registered FROM users WHERE user_id = ?", array( 5 ) );
echo $user->user_registered;
echo 'Total users: '. $database->num_rows( "SELECT COUNT(user_id) FROM users" );
//Prepare the insertion array, keys must match column names
$userdata = array(
'user_name' => 'some username',
'user_password' => 'somepassword (should be hashed)',
'user_email' => 'someone@email.com',
'user_registered' => 'NOW()',
'user_active' => 1
);
//Run the insertion
$insert = $database->insert( 'your_db_table', $userdata );
//Get the last inserted ID
echo 'Last user ID '. $insert;
//Values to update
$update = array(
'user_name' => 'New username',
'user_password' => 'new password (should still be hashed!)',
'user_last_login' => 'NULL'
);
//WHERE clauses
$where = array(
'user_id' => 51
);
//Limit max updates
$limit = 1;
//Run the update, returns the number of affected rows
echo $database->update( 'your_db_table', $update, $where, $limit );
//The WHERE clauses
$delete_where = array(
'user_id' => 47,
'user_active' => 0
);
//Limit for deletions
$limit = 1;
//Run the query
$deleted = $database->delete( 'your_db_table', $delete_where, $limit );
Returns array
$table_fields = $database->list_fields( 'your_db_table' );
echo '<pre>';
echo 'Fields in table: '. PHP_EOL;
print_r( $table_fields );
echo '</pre>';
Returns int
$col_count = $database->num_fields( 'your_db_table' );
echo 'There are '. $col_count . ' fields in the table';
Returns int representing number of tables truncated
$tables = array(
'table1',
'table2'
);
echo $database->truncate( $tables );
Returns bool, useful for automated actions such as making sure tables exist, and if they don't, running auto installers
$table_exists = $database->table_exists( 'nonexistent' );
echo 'Total Queries: '. $database->total_queries();
1.2.1 * Bugfix for update with WHERE clauses existing in sql_constants array
1.2 * Removed internal error handling to allow user defined error handling with try/catch of any PDOException thrown
1.1 * Simplified initialization with removal of explicit options function * Set visibility on all methods and properties * Simplified exception triggers to allow more customized handling of errors and feedback * Chained commands where possible within internal functions
1.0 * Initial Release
Version Control | Unique User Downloads | Download Rankings | |||||||||||||||
100% |
|
|
Applications that use this package |
If you know an application of this package, send a message to the author to add a link here.