SimplePDO.php Its very useful to simple pdo to mysql drive It have CRUD method include

SimplePDO.php Its Developed From  Bennett Stone publish by www.phpdevtips.com. I am actually already have this link

PDO CRUD with use PHP Class file

but It was perfect class file having i will share this file.

1) simplePDO.php

<?php


class SimplePDO {

private $pdo = null;
private $link = null;
public $filter;
static $inst = null;
private $c_query;
private $counter = 0;
private $sql_constants = array(
'NOW()',
'TIMESTAMP()',
'UNIX_TIMESTAMP()',
'NULL'
);
static $settings = array(
'host' => '',
'user' => '',
'password' => '',
'database' => '',
'results' => 'object',
'charset' => 'utf8'
);


public function __construct()
{
$fetch_mode = ( self::$settings["results"] == 'object' ) ? PDO::FETCH_OBJ : PDO::FETCH_ASSOC;
$options = array(
PDO::ATTR_DEFAULT_FETCH_MODE => $fetch_mode,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES ".self::$settings["charset"],
PDO::ATTR_EMULATE_PREPARES => false
);
$dsn = 'mysql:dbname='.self::$settings["database"].';host='.self::$settings["host"].';charset='.self::$settings["charset"];

try {
$this->pdo = new PDO( $dsn, self::$settings["user"], self::$settings["password"], $options );
$this->link = true;
} catch( PDOException $e ) {
trigger_error( $e->getMessage() );
}
}
//end __construct()



public function filter( $data )
{
if( !is_array( $data ) )
{
$data = $this->pdo->quote( $data );
$data = trim( htmlentities( $data, ENT_QUOTES, 'UTF-8', false ) );
}
else
{
//Self call function to sanitize array data
$data = array_map( array( $this, 'filter' ), $data );
}
return $data;
}
//end filter()



public function escape( $data )
{
if( !is_array( $data ) )
{
$data = $this->pdo->quote( $data );
}
else
{
//Self call function to sanitize array data
$data = array_map( array( $this, 'escape' ), $data );
}
return $data;
}
//end escape()



public function clean( $data )
{
$data = stripslashes( $data );
$data = html_entity_decode( $data, ENT_QUOTES, 'UTF-8' );
$data = nl2br( $data );
$data = urldecode( $data );
return $data;
}
//end clean()



public function prepare_in( $values = array() )
{
return implode( ',', array_fill( 0, count( $values ), '?' ) );
}
//end prepare_in()



public function query( $query, $bindings = array(), $internal_call = false )
{
try {

$this->counter++;
$this->c_query = $this->pdo->prepare( $query );
if( empty( $bindings ) )
{
$this->c_query->execute();
}
else
{
$this->c_query->execute( (array)$bindings );
}

//Alternate the response based on class internal vs. direct call to "query()"
if( $internal_call === true )
{
return $this->c_query;
}
elseif( $this->c_query && $this->lastid() )
{
return $this->lastid();
}
else
{
return false;
}

} catch( PDOException $e ) {

//Handle the error with anything you like
trigger_error( $e->getMessage() );

}
}
//end query()



public function get_results( $query, $bindings = array() )
{
$this->c_query = $this->query( $query, $bindings, true );
if( $this->c_query )
{
return $this->c_query->fetchAll();
}
else
{
return false;
}
}
//end get_results()



public function get_row( $query, $bindings = array() )
{
$this->c_query = $this->query( $query, $bindings, true );
if( $this->c_query )
{
return $this->c_query->fetch();
}
else
{
return false;
}
}
//end get_row()



public function num_rows( $query, $bindings = array() )
{
$this->c_query = $this->query( $query, $bindings, true );
if( $this->c_query )
{
return $this->c_query->fetchColumn();
}
else
{
return false;
}

}
//end num_rows()



public function insert( $table, $vars = array() )
{
//Make sure the array isn't empty
if( empty( $vars ) )
{
return false;
}

$fields = array();
$values = array();

foreach( $vars as $field => $value )
{
$field = trim( $field );
$fields[] = $field;

//If we're dealing with a "NOW()" type statement, we must pass directly and remove from bound params
if( in_array( $value, $this->sql_constants ) )
{
unset( $vars[$field] );
$values[] = $this->unquote( $value );
}
else
{
$values[] = ':'.$field;
}
}

$fields = ' (' . implode(', ', $fields) . ')';
$values = '('. implode(', ', $values) .')';

$sql = "INSERT INTO ".$table;
$sql .= $fields .' VALUES '. $values;

$this->c_query = $this->query( $sql, $vars, true );
if( $this->c_query )
{
return $this->lastid();
}
else
{
return false;
}
}
//end insert()



public function update( $table, $variables = array(), $where = array(), $limit = '' )
{
//Make sure the required data is passed before continuing
//This does not include the $where variable as (though infrequently)
//queries are designated to update entire tables
if( empty( $variables ) )
{
return false;
}

$sql = "UPDATE ". $table ." SET ";

$updates = array();
$clauses = array();
foreach( $variables as $field => $value )
{
$field = trim( $field );

//If we're dealing with a "NOW()" type statement, we must pass directly and remove from bound params
if( in_array( $value, $this->sql_constants ) )
{
unset( $variables[$field] );
$updates[] = "`".$field ."` = ". $this->unquote( $value );
}
else
{
$updates[] = "`".$field .'` = ?';
}
}

$sql .= implode(', ', $updates);

//Add the $where clauses as needed
if( !empty( $where ) )
{
foreach( $where as $field => $value )
{
$field = trim( $field );

//If we're dealing with a "NOW()" type statement, we must pass directly and remove from bound params
if( in_array( $value, $this->sql_constants ) )
{
unset( $vars[$field] );
$clauses[] = "`".$field ."` = ". $this->unquote( $value );
}
else
{
$clauses[] = "`".$field .'` = ?';
}

}
$sql .= ' WHERE '. implode( ' AND ', $clauses );
}

if( !empty( $limit ) )
{
$sql .= ' LIMIT '. (int)$limit;
}

//Merge the arrays to bind to params in query()
$vars = array_merge( array_values( $variables ), array_values( $where ) );

$this->c_query = $this->query( $sql, $vars, true );
if( $this->c_query )
{
return $this->c_query->rowCount();
}
else
{
return false;
}
}
//end update()



public function delete( $table, $where = array(), $limit = '' )
{
//Delete clauses require a where param, otherwise use "truncate"
if( empty( $where ) )
{
return false;
}

$sql = "DELETE FROM ". $table;
foreach( $where as $field => $value )
{
$field = trim( $field );

//If we're dealing with a "NOW()" type statement, we must pass directly and remove from bound params
if( in_array( $value, $this->sql_constants ) )
{
unset( $where[$field] );
$clauses[] = "`".$field ."` = ". $this->unquote( $value );
}
else
{
$clauses[] = "`".$field .'` = ?';
}

}
$sql .= ' WHERE '. implode( ' AND ', $clauses );

if( !empty( $limit ) )
{
$sql .= " LIMIT ". $limit;
}

//Params
$vars = array_values( $where );

$this->c_query = $this->query( $sql, $vars, true );
if( $this->c_query )
{
return $this->c_query->rowCount();
}
else
{
return false;
}
}
//end delete()



public function lastid()
{
return $this->pdo->lastInsertId();
}
//end lastid()



public function table_exists( $name )
{
$this->c_query = $this->query( "SHOW TABLES LIKE '$name'", array(), true );
if( $this->c_query && $this->c_query->rowCount() > 0 )
{
return true;
}
else
{
return false;
}
}
//end table_exists()



public function num_fields( $table )
{
return count( $this->list_fields( $table ) );
}
//end num_fields()



public function list_fields( $table )
{
$this->c_query = $this->query( "DESCRIBE $table", array(), true );
if( $this->c_query )
{
return $this->c_query->fetchAll( PDO::FETCH_COLUMN );
}
else
{
return false;
}
}
//end list_fields()



public function truncate( $tables = array() )
{
if( !empty( $tables ) )
{
$truncated = 0;
foreach( $tables as $table )
{
$this->c_query = $this->query( "TRUNCATE TABLE `".trim($table)."`", array(), true );
if( $this->c_query )
{
$truncated++;
}
}
return $truncated;
}
}
//end truncate()



public function affected()
{
return $this->pdo->rowCount();
}
//end affected()



public function total_queries()
{
return $this->counter;
}
//end total_queries()



private function add_wrap( $value )
{
return "'".$value."'";
}
//end add_wrap()



private function unquote( $value )
{
$mapped = array_map( array( $this, 'add_wrap' ), $this->sql_constants );
return str_replace( $mapped, $this->sql_constants, $value );
}
//end unquote()



static function getInstance()
{
if( self::$inst == null )
{
self::$inst = new SimplePDO();
}
return self::$inst;
}
//end getInstance()



static function set_options( $array = array() )
{
if( !empty( $array ) )
{
foreach( $array as $k => $v )
{
if( isset( self::$settings[$k] ) )
{
self::$settings[$k] = $v;
}
}
}
}
//end set_options()



public function __destruct()
{
if( $this->link )
{
$this->pdo = null;
}
}
//end __destruct()

}
//end SimplePDO

2) Calling Method of File:

SimplePDO Database Wrapper
==========

PDO variant of the [SimpleMySQLi class](https://github.com/bennettstone/simple-mysqli), 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:

```php
//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...

```php
$user = $db->get_row( "SELECT username FROM users WHERE user_id = ? LIMIT 1", array( 10 ) );
echo $user->username;
```

As of 29-Nov-2014, the "insert_multi()" function is **not** implemented in this class from SimpleMySQLi.

##Initialization

Same as simplemysqli, you can initiate this class with a new instance, or the singleton:

```php
require_once( 'SimplePDO.php' );

$params = array(
'host' => 'localhost',
'user' => 'root',
'password' => 'root',
'database' => 'yourmagicdatabase'
);
//Set the options
SimplePDO::set_options( $params );

//Initiate the class as a new instance
$database = new SimplePDO();

//OR use the singleton...
$database = SimplePDO::getInstance();
```

##Available functions and usage

This class can:

- Connect to a given MySQL server using PDO
- Execute arbitrary SQL queries
- Retrieve the number of query result rows, result columns and last inserted table identifier
- Retrieve the query results in a single array
- Escape a single string or an array of literal text values to use in queries
- Determine if one value or an array of values contain common MySQL function calls
- Check of a table exists
- Check of a given table record exists
- Return a query result that has just one row
- Execute INSERT, UPDATE and DELETE queries from values that define tables, field names, field values and conditions
- Truncate a table
- Display the total number of queries performed during all instances of the class

###Straight query

```php
$clear_password = $database->query( "UPDATE users SET user_password = ? WHERE user_id = ?", array( 'NULL',  5 ) );
```

Retrieving Data
===============

###Get Results

```php
$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 />';
}
```

###Get Results with LIKE statement

Using LIKE statements in prepared-statement-land requires that the actual array value be encapsulated with the percentage signs as follows...

```php
//CORRECT
$results = $database->get_results( "SELECT user_name, user_email FROM users WHERE user_name LIKE ? AND user_email = ? LIMIT 10", array( '%some%', '[email protected]' ) );
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', '[email protected]' ) );
```

###Get Results using IN() statements

Unfortunately, to handle IN statements, some extra work is indeed required to handle parameter bindings for security [PHP.net](http://php.net/manual/en/pdostatement.execute.php), but it's not too bad, and in this case, requires only a single extra line of code.

```php
//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 );
```

###Get single row

```php
$user = $database->get_row( "SELECT user_registered FROM users WHERE user_id = ?", array( 5 ) );
echo $user->user_registered;
```

###Get number of rows

```php
echo 'Total users: '. $database->num_rows( "SELECT COUNT(user_id) FROM users" );
```

Managing Data
===============

###Insert a record

```php
//Prepare the insertion array, keys must match column names
$userdata = array(
'user_name' => 'some username',
'user_password' => 'somepassword (should be hashed)',
'user_email' => '[email protected]',
'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;
```

###Update record(s)

```php
//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 );
```

###Delete record(s)

```php
//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 );
```

Supplemental Functions
===================

###Get field names in a given table

Returns array

```php
$table_fields = $database->list_fields( 'your_db_table' );
echo '<pre>';
echo 'Fields in table: '. PHP_EOL;
print_r( $table_fields );
echo '</pre>';
```

###Get the number of fields in a table

Returns int

```php
$col_count = $database->num_fields( 'your_db_table' );
echo 'There are '. $col_count . ' fields in the table';
```

###Truncate database tables

Returns int representing number of tables truncated

```php
$tables = array(
'table1',
'table2'
);
echo $database->truncate( $tables );
```

###Find out if a table exists

Returns bool, useful for automated actions such as making sure tables exist, and if they don't, running auto installers

```php
$table_exists = $database->table_exists( 'nonexistent' );
```

###Output number of total queries

```php
echo 'Total Queries: '. $database->total_queries();
```<a href="/wp-content/uploads/2014/12/simple-pdo-mysql-2014-11-29.zip">simple-pdo-mysql-2014-11-29</a>

Download