Índice

Facts

Database Query

Analyzing Queries

EXPLAIN SELECT * FROM 07_amazon_monitor_rank_results WHERE asin = '0672324547';

Prepared Statements

Mysqli Extension

Procedural

<?php
$link = mysqli_connect("localhost", "username", "password", "database");
$city = "Montreal";
$stmt = mysqli_stmt_init($link);
if ($stmt = mysqli_stmt_prepare ($stmt, "SELECT Province FROM City WHERE Name=?"))
{
mysqli_stmt_bind_param($stmt, "s", $city);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $province);
mysqli_stmt_fetch($stmt);
printf("%s is in district %s\n", $city, $province);
mysqli_stmt_close($stmt);
}
mysqli_close($link);

OOP

<?php
$db = new mysqli("localhost", "username", "password", "database");
$city = "Montreal";
$stmt = $db->stmt_init();
if ($stmt->prepare ("SELECT Province FROM City WHERE Name=?"))
{
$stmt->bind_param($city);
$stmt->execute();
$stmt->bind_result($province);
$stmt->fetch();
printf("%s is in district %s\n", $city, $province);
$stmt->close();
}
$db->close();

Transactions

PDO (PHP Data Objects)

Connection and Disconnection

try {
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
foreach($dbh->query('SELECT * from FOO') as $row) {
print_r($row);
}
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}

Query vs Execute

$pdoConnection->exec("INERT INTO … ); // returns the number of rows affected on success, after exec need to use fetch/fetchAll
$pdoConnection->query("SELECT * FROM … ); // returns the query result

Prepared statements

$query="SELECT * FROM posts WHERE topicID = :tid AND poster = :userid";
$statement=$pdoConnection->prepare($query);

$statement->execute(array(':tid'=>100,':userid'=>12));
$userAPosts=$statement->fetchAll();

OR

$query="SELECT * FROM posts WHERE topicID = ? AND poster = ?";
$statement=$pdoConnection->prepare($query);
$statement->bindParam(1,$name);
$statement->bindParam(2,$value);
$name='one';$value=1;
$statement->execute();
$userAPosts=$statement->fetchAll();
foreach($userAPosts as $key=>$value){…}
try {
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh->beginTransaction();
$dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
$dbh->exec("insert into salarychange (id, amount, changedate)
values (23, 50000, NOW())");
$dbh->commit();

} catch (Exception $e) {
$dbh->rollBack();
echo "Failed: " . $e->getMessage();
}

Error Handling

try {
$dbh = new PDO($dsn, $user, $pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Handling Large Files

$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
header("Content-Type: $type");
fpassthru($lob);

$stmt = $db->prepare("insert into images (contenttype, imagedata) values (?, ?)");
$fp = fopen($_FILES['file']['tmp_name'], 'rb');
$stmt->bindParam(1, $_FILES['file']['type']);
$stmt->bindParam(2, $fp, PDO::PARAM_LOB);
$stmt->execute();

SQLite