Documentation

MySQL

PHP

Tutorials

http://phpmaster.com/avoid-the-original-mysql-extension-1/

http://phpmaster.com/migrate-from-the-mysql-extension-to-pdo/

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

http://www.dreamincode.net/forums/topic/214733-introduction-to-pdo/

PDO

It's quite amazing how long it has taken the web to catch-up to best practices adopted by client/server computing from 20 years ago—PDO is a perfect example. PDO is a data access interface that works with several databases, and it should be your first choice when creating a new PHP site.

Making a Database Connection

define ('ENVIRONMENT', 'PxPro Dev'); //PxPro Server || PxPro Dev || Production
 	
//Tweak these lines for your database server
$dbPort = '3306';
if (ENVIRONMENT === 'PxPro Server') {
	$dbHost = 'mysql:host=localhost';
	$dbDb = 'YourDatabaseName';
	$dbUserId = 'UserName';
	$dbPassword = 'YourPassword';
} else if (ENVIRONMENT === 'PxPro Dev') {
	$dbHost = 'mysql:host=localhost';
	$dbDb = 'YourDatabaseName';
	$dbUserId = 'UserName';
	$dbPassword = 'YourPassword';
} else if (ENVIRONMENT === 'Production') {
	$dbHost = 'mysql:host=subdomain.domain.com';
	$dbDb = 'YourDatabaseName';
	$dbUserId = 'UserName';
	$dbPassword = 'YourPassword';
}
 
//Probably ought not touch these.
$dbDSN = "$dbHost;port=$dbPort;dbname=$dbDb";
$db; //this will be a global variable
 
if (ENVIRONMENT === 'PxPro Server') {
	define('DOMAIN', 'http://' . $_SERVER['HTTP_HOST'] . '/');
} else if (ENVIRONMENT === 'PxPro Dev') {
	define('DOMAIN', 'http://' . $_SERVER['HTTP_HOST'] . '/~Craig/DART/');
} else if (ENVIRONMENT === 'Production') {
	define('DOMAIN', 'http://' . $_SERVER['HTTP_HOST'] . '/');
}
define('APP_ROOT', dirname(dirname(__FILE__)) . '/'); //double-pumping to go up one level since this file is in a sub-directory.
define('INCLUDE_ROOT', APP_ROOT . "includes/");
 
function makeDbConnection() {
	global $dbDSN;
	global $dbUserId;
	global $dbPassword;
	global $db;
	
	try {
		$db = new PDO($dbDSN, $dbUserId, $dbPassword, array(
    		PDO::ATTR_PERSISTENT => true,
			PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
		));
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$db->exec("SET CHARACTER SET utf8");
	} catch(PDOException $e) {
		debug("unable to make connection: ".$e->getMessage());
		$delayedError .= print($e->getMessage());
	}
}

PDO Data Access Using a Variable

This works for both reads (Select) and writes (Insert/Update/Delete).

global $db;
try {
	$stmt = $db->prepare("Select * From departments Where deptId = ?;");
	$stmt->execute(array($this->deptId));
	if ($row = $stmt->fetchObject()) {
		$this->deptId = $row->deptId;
		$this->department = $row->department;
	}
	$stmt->closeCursor();
	return $this->deptId;
} catch(PDOException $e) {
	debug('Dept / load() / ' . $e->getMessage());
	return false;
}

PDO Read Using Static Statement

This works for reading from the database.

try {
	$stmt = $db->query("Select count(*) as aNumber from pages;");
	if ($row = $stmt->fetchObject()) {
		$aNumber = $row->aNumber;
	}
} catch(PDOException $e) {
	echo "<!-- Unable to query table pages: " . $e->getMessage() . ' -->' . PHP_EOL;
}

This works for writes (Insert/Update/Delete).

try {
	$stmt = $db->exec("Insert pages (pageName, pageType) values ('myPage', 'STD');");
	$newPageId = $db->lastInsertId();
} catch(PDOException $e) {
	echo "<!-- Unable to query table pages: " . $e->getMessage() . ' -->' . PHP_EOL;
}

Formatting Values for Data Access

if (!function_exists("GetSQLValueString")) {
	function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") {
		if (PHP_VERSION < 6) {
			$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
		}
		$link = mysql_connect('localhost', 'UserId', 'Password'); //YOU MUST SET YOUR CONNECTION INFORMATION HERE OR USE A PRIOR CONNECTION
		$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue, $link) : mysql_escape_string($theValue);
		switch ($theType) {
			case "text":
				$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
				break; 
			case "long":
			case "int":
				$theValue = ($theValue != "") ? intval($theValue) : "NULL";
				break;
			case "double":
				$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
				break;
			case "date":
				$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
				break;
			case "defined":
				$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
				break;
		}
		return $theValue;
	}
}
$updateSQL = sprintf("Delete From videoTracking WHERE clientId=%s and email=%s and courseId=%s",
	GetSQLValueString($_GET['clientId'], "int"),
	GetSQLValueString($_GET['email'], "text"),
	GetSQLValueString($_GET['courseId'], "int")
);
$rst = @$db->query($sqlLogin); //@ is an error suppression operator.
if($db->errno) { 
	echo "alert('Unable to run query $sql because $db->errno $db->error');";
}
while ($row = $rst->fetch_object()) {
	//so something with $row->$columnName
}
$db->close();
		

Literals

Literals need to be delimited by:

Strings: single quotes (it is possible to use double quotes if ANSI_QUOTES is turned off).

Numbers: Just type the number. Valid characters are 0-9 + − e . (period).

Dates: Date and time values can be represented in several formats, such as quoted strings or as numbers, depending on the exact type of the value and other factors. For example, in contexts where MySQL expects a date, it interprets any of '2015-07-21', '20150721', and 20150721 as a date. The time format is 'YYYY-MM-DD HH:MM:SS'.

You should not use a column alias (presumable table aliases are OK) in the Where clause in MySQL.

Basic MySQL DML Syntax

Select column1, column2
From tableName
Where column3 = 'a string value';
 
Select a.col1 As first, a.col2, b.col1
From table1 As a Left Join table2 As b
  On a.pk = b.fk
Where a.dateCol >= '2012-03-29'
Order by a.col2 Desc;
 
Insert tableName (col1, col2, col3)
Values (123, 'abc', '2012-03-28'); 
Insert tableName (col1, col2, col3)
Select colA, colB, colC
From myTable; 
 
Update tableName
Set col1=123, col2='abc', col3='2012-03-28');
 
Delete from tableName
Where col1<900;

Basic MySQL DDL Syntax

Create table myTableName (
	col1 int not null AUTO_INCREMENT,
	col2 char(3),
	col3 varchar(255),
	col4 datetime,
Constraint myTableNamePK Primary Key (col1)
);

Common Data Types