Fork me on GitHub

Databases



Facts


Analyzing Queries


Prepared Statements

1
2
3
4
5
6
7
8
9
10
11
<?php
boolean mysqli_stmt_bind_param (mysqli_stmt stmt, string types, mixed &var1 [, mixed &varN)
class mysqli_stmt {
    boolean bind_param (string types, mixed &var1 [, mixed &varN])
}

boolean mysqli_stmt_bind_result (mysqli_stmt stmt, mixed &var1 [, mixed &varN])

class mysqli_stmt {
    boolean bind_result (mixed &var1 [, mixed &varN])
}

Mysqli

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
$link = mysqli_connect("localhost", "u", "p", "ex");
$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);

Transactions

Allow you to merge multiple queries into one atomic operation, either they ALL execute successfully, or none do

1
2
3
BEGIN TRANSACTION #name;
 ... queries here
COMMIT;

PDO

1
2
3
<?php
$pdoConnection = new PDO('mysql:host=localhost;dbname=example', USERNAME, PASSWORD);
foreach ($pdoConnection->query("SELECT * FROM users") AS $user) { echo $user['id']; }

Prepared statements

1
2
3
4
5
6
7
<?php
$query = "SELECT * FROM posts WHERE topicID = :tid AND poster = :userid";
$statement = $pdoConnection->prepare($query, array(PDO::ATTR_CURSOR, PDO::CURSOR_FWDONLY));
$statement->execute(array(':tid' => 100, ':userid' => 12));
$userAPosts = $statement->fetchAll();
$statement->execute(array(':tid' => 100, ':userid' => 13));
$userBPosts = $statement->fetchAll();

closing connection

1
2
<?php
$pdoConnection = null; // 
1
2
<?php
PDOStatement->nextRowset()

array PDO::errorInfo ( void ) * 0 - SQLSTATE error code (a five characters alphanumeric identifier defined in the ANSI SQL standard) * 1 - Driver-specific error code. * 2 - Driver-specific error message.


SQLite

blog comments powered by Disqus