PHP-PDO-MySQL-Class

A PHP MySQL PDO class similar to the the Python MySQLdb.

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(dirname(__FILE__)."/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']);
?>

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:

green

Delete / 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();
?>

Iterator

Use iterators when you want to read thousands of data from the database for full update of Elastic Search or Solr.

An 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:

$it = $DB->iterator("SELECT * FROM fruit limit 0, 1000000;");
$colorCountMap = array(
    'red' => 0,
    'yellow' => 0,
    'green' => 0
);
foreach($it as $key => $value) {
    sendDataToElasticSearch($key, $value);
    $colorCountMap[$value['color']]++;
}