PHP and MySQL
Most PHP websites save their data in a MySQL (or MariaDB) database. PHP talks to it using PDO.
What is PDO?
PDO (PHP Data Objects) is the built-in way to connect PHP to databases. It works with MySQL, PostgreSQL, SQLite and others, and it supports prepared statements, which keep your site safe.
If you installed XAMPP, MySQL is already there. Start it in the XAMPP Control Panel and open phpMyAdmin at http://localhost/phpmyadmin to create a database called shop.
Creating a Table
Run this SQL in phpMyAdmin:
Example
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);Connecting
Example
<?php
$dsn = "mysql:host=localhost;dbname=shop;charset=utf8mb4";
try {
$pdo = new PDO($dsn, "root", "", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
echo "Connected!";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}Connected!
On XAMPP the default user is root with an empty password. On a real server, always use a strong password and keep it in a separate config file.
Inserting Data with Prepared Statements
Never put user input straight into SQL. Use placeholders and pass the values separately:
Example
<?php
$stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
$stmt->execute(["Rice 5kg", 1650]);
$stmt->execute(["Cooking Oil 1L", 620]);
$stmt->execute(["Sugar 1kg", 160]);
echo "New product id: " . $pdo->lastInsertId();New product id: 3
You can also use named placeholders:
Example
<?php
$stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (:name, :price)");
$stmt->execute(["name" => "Tea 950g", "price" => 1450]);SQL Injection
Look at this dangerous code:
Example
<?php
// DANGEROUS - do not do this
$sql = "SELECT * FROM users WHERE email = '" . $_POST["email"] . "'";An attacker could type ' OR '1'='1 as email and read every user. Prepared statements prevent this, because the database treats the value only as data, never as SQL code.
Rule: if a value comes from a user, a URL or a form, it must go through a placeholder.
Reading Data
Example
<?php
$stmt = $pdo->prepare("SELECT name, price FROM products WHERE price < ? ORDER BY price");
$stmt->execute([1000]);
foreach ($stmt->fetchAll() as $row) {
echo htmlspecialchars($row["name"]) . " - Rs " . $row["price"] . "<br>";
}Sugar 1kg - Rs 160.00 Cooking Oil 1L - Rs 620.00
| Method | Returns |
|---|---|
fetch() | The next row, or false when none are left |
fetchAll() | All rows as an array |
fetchColumn() | One value from the next row |
rowCount() | Number of rows changed by INSERT, UPDATE or DELETE |
Updating and Deleting
Example
<?php
$update = $pdo->prepare("UPDATE products SET price = ? WHERE id = ?");
$update->execute([1700, 1]);
echo $update->rowCount() . " product updated<br>";
$delete = $pdo->prepare("DELETE FROM products WHERE id = ?");
$delete->execute([3]);
echo $delete->rowCount() . " product deleted";1 product updated 1 product deleted
Exercise
Exercise
What is the safest way to use form input in an SQL query?