PHP-PDO-MySQL-Class 
A PHP MySQL PDO class similar to the Python MySQLdb, which supports iterator and parameter binding when using "WHERE IN" statement.
Install
Copy the files under src/ to your program
OR
composer require lincanbin/php-pdo-mysql-class
Initialize
<?php
define('DBHost', '127.0.0.1');
define('DBPort', 3306);
define('DBName', 'Database');
define('DBUser', 'root');
define('DBPassword', '');
require(__DIR__ . "/src/PDO.class.php");
$DB = new Db(DBHost, DBPort, DBName, DBUser, DBPassword);
?>Preventing SQL Injection Attacks
Safety: Use parameter binding method
Safety Example:
<?php
$DB->query("SELECT * FROM fruit WHERE name=?", array($_GET['name']));
?>Unsafety: Split joint SQL string
Unsafety Example:
<?php
$DB->query("SELECT * FROM fruit WHERE name=".$_GET['name']);
?>Basic Usage
table "fruit"
| id | name | color |
|---|---|---|
| 1 | apple | red |
| 2 | banana | yellow |
| 3 | watermelon | green |
| 4 | pear | yellow |
| 5 | strawberry | red |
Fetching with Bindings (ANTI-SQL-INJECTION):
<?php
$DB->query("SELECT * FROM fruit WHERE name=? and color=?",array('apple','red'));
$DB->query("SELECT * FROM fruit WHERE name=:name and color=:color",array('name'=>'apple','color'=>'red'));
?>Result:
Array
(
[0] => Array
(
[id] => 1
[name] => apple
[color] => red
)
)WHERE IN (needs named placeholder):
<?php
$DB->query("SELECT * FROM fruit WHERE name IN (:fruits)",array(array('apple','banana')));
?>Result:
Array
(
[0] => Array
(
[id] => 1
[name] => apple
[color] => red
)
[1] => Array
(
[id] => 2
[name] => banana
[color] => yellow
)
)<?php
$query = "SELECT * FROM fruit WHERE name IN (:fruits) AND color = :color";
// use multidimensional array as $params
$params = array(
"color" => "red",
"fruits" => array(
"apple",
"banana"
)
);
$DB->query($query, $params);
?>Result:
Array
(
[0] => Array
(
[id] => 1
[name] => apple
[color] => red
)
)Fetching Column:
<?php
$DB->column("SELECT color FROM fruit WHERE name IN (:color)",array('apple','banana','watermelon'));
?>Result:
Array
(
[0] => red
[1] => yellow
[2] => green
)Fetching Row:
<?php
$DB->row("SELECT * FROM fruit WHERE name=? and color=?",array('apple','red'));
?>Result:
Array
(
[id] => 1
[name] => apple
[color] => red
)Fetching single:
<?php
$DB->single("SELECT color FROM fruit WHERE name=? ",array('watermelon'));
?>Result:
greenDelete / Update / Insert
These operations will return the number of affected result set. (integer)
<?php
// Delete
$DB->query("DELETE FROM fruit WHERE id = :id", array("id"=>"1"));
$DB->query("DELETE FROM fruit WHERE id = ?", array("1"));
// Update
$DB->query("UPDATE fruit SET color = :color WHERE name = :name", array("name"=>"strawberry","color"=>"yellow"));
$DB->query("UPDATE fruit SET color = ? WHERE name = ?", array("yellow","strawberry"));
// Insert
$DB->query("INSERT INTO fruit(id,name,color) VALUES(?,?,?)", array(null,"mango","yellow"));//Parameters must be ordered
$DB->query("INSERT INTO fruit(id,name,color) VALUES(:id,:name,:color)", array("color"=>"yellow","name"=>"mango","id"=>null));//Parameters order free
?>Get Last Insert ID
<?php
$DB->lastInsertId();
?>Get the number of queries since the object initialization
<?php
$DB->querycount;
?>Close Connection
<?php
$DB->closeConnection();
?>Transaction
<?php
try {
$DB->beginTransaction();
var_dump($DB->inTransaction()); // print "true"
$DB->commit();
} catch(Exception $ex) {
// handle Error
$DB->rollBack();
}
?>Iterator
Use iterator when you want to read thousands of data from the database for statistical or full update of Elastic Search or Solr indexes.
Iterator is a traversable object that does not read all the data queried from MySQL into memory.
So you can safely use foreach to handle millions of MySQL result sets without worrying about excessive memory usage.
Example:
$iteratorInstance = $DB->iterator("SELECT * FROM fruit limit 0, 1000000;");
$colorCountMap = array(
'red' => 0,
'yellow' => 0,
'green' => 0
);
foreach($iteratorInstance as $key => $value) {
sendDataToElasticSearch($key, $value);
$colorCountMap[$value['color']]++;
}
var_export($colorCountMap);Return:
array(3) {
[red] => 2
[yellow] => 2
[green] => 1
}