PHP and MySQL

A database is where a website keeps data that must survive between visits: users, posts, orders. MySQL is the most common database paired with PHP, and phpMyAdmin is the browser tool that comes with XAMPP to manage it. Together they turn static pages into real applications.

PHP talks to MySQL through a driver. The two main choices are mysqli, a classic function library, and PDO, a flexible layer that works with many databases. Both support prepared statements, and prepared statements are the only safe way to put visitor input into a query.

The flow is always the same. You connect with a host, username, password, and database name. You prepare a query with question marks as placeholders. You bind real values to those placeholders and execute. Then you fetch the rows and use them in your page.

XAMPP gives you MySQL out of the box. Start MySQL in the control panel, open http://localhost/phpmyadmin, and create a database. Then your PHP code can connect with the default credentials: root user and an empty password, which is fine for local learning and never for a live site.

Connect and query

mysqli_connect returns a connection, or false when it fails. The die() pattern shows the error so you can fix it. Then a query runs and you fetch rows in a loop.

$conn = mysqli_connect("localhost", "root", "", "shop");
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$result = mysqli_query($conn, "SELECT name FROM products");
while ($row = mysqli_fetch_assoc($result)) {
  echo $row["name"] . "<br>";
}

Prepared statements

Never build a query by gluing strings together. A visitor can break out of a string and run their own SQL, an attack called injection. Prepared statements separate the query from the values, so input can never become code.

$stmt = $conn->prepare(
  "SELECT name FROM products WHERE id = ?"
);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
$product = $result->fetch_assoc();

Syntax

<?php
$conn = new mysqli("localhost", "root", "", "shop");
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->bind_param("s", $name);
$name = "Aya";
$stmt->execute();
echo "User saved with id " . $conn->insert_id;
?>

$stmt = $pdo->prepare("SELECT * WHERE id=?"); $stmt->execute([$id])