48 SQL/PDO Reference

Here are some quick reference reminders about SQL and the PDO interface for PHP to talk to an SQL database. This textbook is just designed to give you a toehold in how to use PHP for server-side web app programming. Once you understand how the language works, you can get more details elsewhere. One good place for tutorial-style basic SQL and PDO (including language references) is w3schools. Try w3schools SQL and w3schools PHP MySQL.

Quick Facts

• SQL is a declarative language for talking to a relational database.
• SQL is a statically typed language
• MySQL is a Relational Database Management System (RDBMS or DBMS) that communicates via SQL
• phpMyAdmin is a web-based MySQL administration tool, written in PHP and bundled with XAMPP
• PDO is an object-oriented API for connecting to a database and sending SQL queries from a PHP program.

SQL Basics   

Square brackets indicate optional clauses.

SELECT field1, field2, … FROM table [WHERE condition] [ORDER BY field [DESC] …] [LIMIT n]
INSERT INTO table (column…) VALUES (value…)
UPDATE table SET field1=value1, field2=value2, … [WHERE condition]
DELETE FROM table [WHERE condition]

PDO Step 1: Connect

Put this code in a connect.php file:

try { 
    $dbh = new PDO("mysql:host=localhost;dbname=___", "login", "password");
} catch(Exception $e) {
    die ($e->getMessage());
}

Then use an include statement to load it into another PHP file.

include "connect.php";

PDO Step 2: Prepare

$command = "SQL command with values replaced by question marks (parameters)";
$stmt = $dbh->prepare($command);

PDO Step 3: Execute

$arguments = [ an array of arguments, one per SQL parameter ];
$success = $stmt->execute($arguments);

PDO Step 4: Check it Worked

if ($success) {
    // it worked
} else {
   // it failed
}

$stmt->rowCount() // for UPDATE, INSERT, DELETE only

Use a try… catch block for complete error checking.

PDO Step 5: Retrieve (SELECT only)

while($row = $stmt->fetch()) { 
    // $row["field"] contains the fields
}