Skip to content

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

License

Notifications You must be signed in to change notification settings

lincanbin/PHP-PDO-MySQL-Class

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

69 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

PHP-PDO-MySQL-Class Build Status

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:

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

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
}

About

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

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages