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:

			
<?php
	$DB->query("SELECT * FROM fruit WHERE name IN (?)",array('apple','banana'));
?>
			
		

Result:

			
array (
  0 => 
  array (
    'id' => 1,
    'name' => 'apple',
    'color' => 'red',
  ),
)			
		

Fetching Column:

			
<?php
	$DB->column("SELECT color FROM fruit WHERE name IN (?)",array('apple','banana','watermelon'));
?>
			
		

Result:

			
array (
  0 => 'red',
)			
		

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

Result:

			
ID for array("color"=>"yellow","name"=>"mango","id"=>null): 
7			
		

Get the number of queries since the object initialization

			
<?php
	$DB->querycount;
?>
			
		

Result:

			
14 SQL Queries in this page.