PHP-PDO-MySQL-Class

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

Initialize

<?php
define('DBHost', '127.0.0.1');
define('DBName', 'Database');
define('DBUser', 'root');
define('DBPassword', '');
require(dirname(__FILE__)."/src/PDO.class.php");
$DB = new Db(DBHost, 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;
?>