lincanbin/php-pdo-mysql-class

A PHP MySQL PDO class similar to the the Python MySQLdb, which supports parameter binding when using 'WHERE IN' statement.

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., _(*1)

• Install, _(*2)

• Initialize, _(*3)

• Preventing SQL Injection Attacks, _(*4)

• Basic Usage, _(*5)

• Transaction, _(*6)

• Iterator, _(*7)

Install

Copy the files under src/ to your program, _(*8)

OR, _(*9)

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:, _(*10)

<?php
$DB->query("SELECT * FROM fruit WHERE name=?", array($_GET['name']));
?>

Unsafety: Split joint SQL string

Unsafety Example:, _(*11)

<?php
$DB->query("SELECT * FROM fruit WHERE name=".$_GET['name']);
?>

Basic Usage

table "fruit"

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:, _(*12)

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:, _(*13)

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:, _(*14)

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:, _(*15)

Array
(
    [0] => red
    [1] => yellow
    [2] => green
)

Fetching Row:

<?php
$DB->row("SELECT * FROM fruit WHERE name=? and color=?",array('apple','red'));
?>

Result:, _(*16)

Array
(
    [id] => 1
    [name] => apple
    [color] => red
)

Fetching single:

<?php
$DB->single("SELECT color FROM fruit WHERE name=? ",array('watermelon'));
?>

Result:, _(*17)

green

Delete / Update / Insert

These operations will return the number of affected result set. (integer), _(*18)

<?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., _(*19)

Iterator is a traversable object that does not read all the data queried from MySQL into memory., _(*20)

So you can safely use foreach to handle millions of MySQL result sets without worrying about excessive memory usage., _(*21)

Example:, _(*22)

$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:, _(*23)

array(3) {
  [red] => 2
  [yellow] => 2
  [green] => 1
}