Establishing a Database Connection⚑
<?php
// Include config + functions used throughout the project
require_once __DIR__ . '/includes/config.php';
require_once __DIR__ . '/includes/functions.php';
// Make Connection
try {
$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME . ';charset=utf8mb4', DB_USER, DB_PASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (Exception $e) {
showDbError('connect', $e->getMessage());
}Put this in config.php
// Database config
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'Azerty123');
define('DB_NAME', 'fotofactory');Put this in functions.php
// Error Logging and User Redirects
function showDbError($type, $msg) {
file_put_contents(__DIR__ . '/error_log_mysql', PHP_EOL . (new DateTime())->format('Y-m-d H:i:s') . ' : ' . $msg, FILE_APPEND);
header('location: error.php?type=db&detail=' . $type);
exit();
}Building and executing queries, the basic edition
⚠ Don't use this, use prepared statements (see below) instead. Contents only placed here for reference/context.
Use PDO::query()⚑ to execute queries that return a resultset (SELECT, DESCRIBE, SHOW, etc.)
<?php
$stmt = $db->query(
'SELECT * FROM collections WHERE user_id = ' . $db->quote(2, PDO::PARAM_INT) . ' OR name = ' . $db->quote('russia', PDO::PARAM_STR)
);Don't forget to use PDO::quote()⚑ to prevent SQL Injection!
Use a fetch function to fetch the data (see further)
Use PDO::exec()⚑ to execute queries that do not return resultsets (UPDATE, DELETE, INSERT, etc)
<?php
$stmt = $db->exec(
'DELETE FROM collections WHERE user_id = ' . $db->quote(20, PDO::PARAM_INT)
);Don't forget to use PDO::quote()⚑ to prevent SQL Injection!
Use an extra function to fetch meta information about the result (see further)
Building and executing queries, using Prepared Statements
In short: Use PDO::prepare()⚑ to get a PDOStatement, bind parameters onto it using PDOStatement::bindValue()⚑, and then execute that statement using PDOStatement::execute()⚑
Binding parameters one by one
⚠ Don't use this, bind all parameters in one go (see below) instead. Contents only placed here for reference/context.
<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = :user_id OR name = :name');
$stmt->bindValue(':user_id', 2, PDO::PARAM_INT);
$stmt->bindValue(':name', 'russia', PDO::PARAM_STR);
$stmt->execute();<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->bindValue(1, 2, PDO::PARAM_INT);
$stmt->bindValue(2, 'russia', PDO::PARAM_STR);
$stmt->execute();Binding all parameters in one go
<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));Processing the results of queries
Processing queries that produce resultsets (SELECT, DESCRIBE, SHOW, etc.)
In short: Use a fetch function to retrieve data from the statement.
Fetch rows one by one using PDOStatement::fetch()⚑
One row returned:
<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
$stmt->execute(array(1));
$collection = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($collection);array(3) {
'id' =>
string(1) "1"
'name' =>
string(6) "russia"
'user_id' =>
string(1) "4"
}Use a while loop to iterate multiple returned rows:
<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE id != ?');
$stmt->execute(array(1));
while ($collection = $stmt->fetch(PDO::FETCH_ASSOC)) {
var_dump($collection);
}Other interesting Fetch Types: PDO::FETCH_OBJ, PDO::FETCH_NUM and PDO::FETCH_GROUP
Fetch all rows using PDOStatement::fetchAll()⚑
<?php
$stmt = $db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?);
$stmt->execute(array(2, 'russia'));
$collections = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($collections);array(3) {
[0] =>
array(3) {
'id' =>
string(1) "1"
'name' =>
string(6) "russia"
'user_id' =>
string(1) "4"
}
[1] =>
array(3) {
'id' =>
string(1) "2"
'name' =>
string(6) "serbia"
'user_id' =>
string(1) "2"
}
[2] =>
array(3) {
'id' =>
string(1) "4"
'name' =>
string(8) "TestColl"
'user_id' =>
string(1) "2"
}
}Other interesting Fetch Types: PDO::FETCH_OBJ, PDO::FETCH_NUM and PDO::FETCH_GROUP
Fetch a single value/column (from the first row) using PDOStatement::fetchColumn()⚑
<?php
$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));
$value = $stmt->fetchColumn();
var_dump($value);string(6) "russia"
Add a numeric index (starting from 0) to define which column you want to extract
<?php
$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));
$value = $stmt->fetchColumn(1);
var_dump($value);string(1) "4"
Processing queries that do not produce resultsets (UPDATE, DELETE, INSERT, etc)
In short: Use an extra function to fetch meta information about the result
Use PDOStatement::rowCount()⚑ to get to know the number of affected rows made by UPDATE and DELETE queries
<?php
$stmt = $db->prepare('UPDATE collections SET name = REVERSE(name) WHERE user_id = ?');
$stmt->execute(array(2));
$affectedRows = $stmt->rowCount();
var_dump($affectedRows);int(2)
Use PDO::lastInsertId()⚑ to get to know the last inserted id (AUTO_INCREMENT)
<?php
$stmt = $db->prepare('INSERT INTO collections (name,user_id) VALUES (?,?)');
$stmt->execute(array('testalbum', 2));
$insertId = $db->lastInsertId();
var_dump($insertId);string(1) "5"