What Are We Doing in This Post?
Every application you have ever used stores data somewhere. Your Instagram posts, your bank transactions, your college results, your Amazon orders — all of it lives in a database.
Until now our PHP programs stored data in variables. Variables die the moment the page reloads. A database stores data permanently — it survives page reloads, server restarts, and years of use.
This episode is where PHP becomes a real backend language. We connect PHP to MySQL, create a database, insert records, and fetch and display them on a webpage.
What is MySQL?
MySQL is a relational database management system. It stores data in tables — just like an Excel spreadsheet, but far more powerful, faster, and built for thousands of simultaneous users.
Real world analogy: Think of MySQL as a massive filing cabinet. Each drawer is a database. Each folder inside the drawer is a table. Each sheet of paper inside the folder is a row of data. Each column on the sheet is a field — like Name, Age, Email.
MySQL comes pre-installed with XAMPP. You already have it on your machine. We just need to start it and create our first database.
Step 1 — Start MySQL in XAMPP
Open the XAMPP Control Panel. Click Start next to MySQL. The row turns green and you see port 3306 appear. MySQL is now running.
Now click the Admin button next to MySQL. This opens phpMyAdmin in your browser — a visual interface for managing your databases. You will use this to create databases and tables during development.
Step 2 — Create a Database
In phpMyAdmin, look at the left sidebar. Click New at the top.
In the "Create database" field, type: phplearning
Leave the collation as utf8mb4_general_ci and click Create.
You now have a database called phplearning. This is where all the tables for our practice projects will live.
Step 3 — Create a Table
After creating the database, phpMyAdmin asks you to create a table. Let us create a users table.
Type users in the Name field. Set number of columns to 4. Click Go.
Now fill in the four columns exactly like this:
Column 1: Name = id, Type = INT, set it as PRIMARY KEY, check AUTO_INCREMENT
Column 2: Name = name, Type = VARCHAR, Length = 100
Column 3: Name = email, Type = VARCHAR, Length = 150
Column 4: Name = created_at, Type = TIMESTAMP, Default = CURRENT_TIMESTAMP
Click Save. Your users table is created.
The id column is the primary key — a unique identifier for every row. AUTO_INCREMENT means MySQL automatically assigns the next available number whenever a new row is inserted. You never have to set it manually.
Step 4 — Connect PHP to MySQL Using MySQLi
PHP gives you two ways to connect to MySQL: MySQLi and PDO. In this episode we use MySQLi — it is straightforward and beginner-friendly. We will cover PDO later when we go deeper into PHP.
Create a new file called db.php in your phplearning folder. This file will hold our database connection — we will include it in every file that needs database access.
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "phplearning";
$conn = mysqli_connect($host, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>
mysqli_connect() takes four arguments: the host, MySQL username, MySQL password, and database name.
In XAMPP, the default MySQL username is root and the password is empty — no password set. This is fine for local development. On a real live server you always set a strong password.
mysqli_connect() returns a connection object on success or false on failure.
die() stops the script immediately and displays a message. If the connection fails, there is no point continuing — so we stop and show the error.
We save this connection in $conn. Every database operation we do will use this $conn variable.
Step 5 — Insert Data Into the Database
Create a new file called insert.php:
<?php
require_once "db.php";
$name = "Gagan Sharma";
$email = "gagan@example.com";
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if (mysqli_query($conn, $sql)) {
echo "User added successfully.";
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
require_once "db.php" includes our connection file. require_once means: include this file, but only once even if this line runs multiple times. If the file is not found, stop the script entirely.
mysqli_query() executes an SQL query against the database. It takes two arguments: the connection object and the SQL string.
mysqli_error() returns the last error message from MySQL — useful for debugging when a query fails.
mysqli_close() closes the database connection when we are done. Good practice, though PHP closes it automatically at the end of the script anyway.
Visit http://localhost:8080/phplearning/insert.php
You should see: User added successfully.
Go back to phpMyAdmin, click on your phplearning database, click on the users table, and click Browse. You will see the row you just inserted — with an auto-assigned id of 1 and a created_at timestamp.
Step 6 — Fetch and Display Data
Now let us read that data back and display it on a webpage.
Create fetch.php:
<?php
require_once "db.php";
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Users List</title>
</head>
<body>
<h2>All Users</h2>
<?php if (mysqli_num_rows($result) > 0): ?>
<table border="1" cellpadding="10" cellspacing="0">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Joined</th>
</tr>
<?php while ($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo htmlspecialchars($row["name"]); ?></td>
<td><?php echo htmlspecialchars($row["email"]); ?></td>
<td><?php echo $row["created_at"]; ?></td>
</tr>
<?php endwhile; ?>
</table>
<?php else: ?>
<p>No users found in the database.</p>
<?php endif; ?>
</body>
</html>
<?php mysqli_close($conn); ?>
mysqli_num_rows() returns the number of rows in the result. We check if it is greater than 0 before trying to display any data.
mysqli_fetch_assoc() fetches one row from the result as an associative array, then moves the internal pointer to the next row. We call it in a while loop — it keeps fetching rows one by one until there are no more rows, at which point it returns false and the loop ends.
Each $row looks like this:
<?php
// $row = [
// "id" => 1,
// "name" => "Gagan Sharma",
// "email" => "gagan@example.com",
// "created_at" => "2025-01-15 10:30:00"
// ]
?>
Notice we are using the alternative syntax for if and while inside HTML — if(): endif; and while(): endwhile; instead of curly braces. This is the standard PHP practice when mixing PHP and HTML. It makes the code far more readable.
Step 7 — A Complete CRUD Example
CRUD stands for Create, Read, Update, Delete. These four operations are the foundation of every database-driven application. Let us build a complete users management page.
Create users.php:
<?php
require_once "db.php";
$message = "";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
if (isset($_POST["action"])) {
if ($_POST["action"] == "add") {
$name = trim($_POST["name"] ?? "");
$email = trim($_POST["email"] ?? "");
if (!empty($name) && !empty($email)) {
$name = mysqli_real_escape_string($conn, $name);
$email = mysqli_real_escape_string($conn, $email);
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if (mysqli_query($conn, $sql)) {
$message = "User added successfully.";
}
}
}
if ($_POST["action"] == "delete") {
$id = (int) $_POST["id"];
$sql = "DELETE FROM users WHERE id = $id";
if (mysqli_query($conn, $sql)) {
$message = "User deleted.";
}
}
}
}
$result = mysqli_query($conn, "SELECT * FROM users ORDER BY id DESC");
?>
<!DOCTYPE html>
<html>
<head>
<title>User Management</title>
</head>
<body style="font-family:sans-serif; max-width:700px; margin:40px auto; padding:0 16px;">
<h2>User Management</h2>
<?php if ($message): ?>
<p style="color:green; font-weight:bold;"><?php echo $message; ?></p>
<?php endif; ?>
<h3>Add New User</h3>
<form method="POST" action="users.php">
<input type="hidden" name="action" value="add" />
<input type="text" name="name" placeholder="Full Name" style="padding:8px; margin-right:8px;" required />
<input type="email" name="email" placeholder="Email Address" style="padding:8px; margin-right:8px;" required />
<button type="submit" style="padding:8px 16px; background:#6366f1; color:#fff; border:none; border-radius:4px; cursor:pointer;">Add User</button>
</form>
<h3 style="margin-top:32px;">All Users</h3>
<?php if (mysqli_num_rows($result) > 0): ?>
<table border="1" cellpadding="10" cellspacing="0" style="width:100%; border-collapse:collapse;">
<tr style="background:#f1f5f9;">
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Joined</th>
<th>Action</th>
</tr>
<?php while ($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo htmlspecialchars($row["name"]); ?></td>
<td><?php echo htmlspecialchars($row["email"]); ?></td>
<td><?php echo $row["created_at"]; ?></td>
<td>
<form method="POST" action="users.php" style="display:inline;">
<input type="hidden" name="action" value="delete" />
<input type="hidden" name="id" value="<?php echo $row['id']; ?>" />
<button type="submit" style="background:#ef4444; color:#fff; border:none; padding:4px 10px; border-radius:4px; cursor:pointer;" onclick="return confirm('Delete this user?')">Delete</button>
</form>
</td>
</tr>
<?php endwhile; ?>
</table>
<?php else: ?>
<p>No users yet. Add one above.</p>
<?php endif; ?>
</body>
</html>
<?php mysqli_close($conn); ?>
Visit http://localhost:8080/phplearning/users.php
Add a few users using the form. Watch them appear in the table immediately. Delete one. This is a fully functional database-driven application.
Two important things to highlight from this example.
mysqli_real_escape_string():
<?php
$name = mysqli_real_escape_string($conn, $name);
?>
This escapes special characters in user input before inserting it into a SQL query. It prevents a serious security vulnerability called SQL Injection — where a malicious user types SQL code into a form field to manipulate or destroy your database. For example, if someone types ' OR '1'='1 as their name, it could break your query and expose all records. mysqli_real_escape_string() neutralizes this.
We will cover a better solution called Prepared Statements in the next episode. Prepared statements are the modern standard — but understanding why escaping exists first helps you appreciate prepared statements properly.
(int) casting for IDs:
<?php
$id = (int) $_POST["id"];
?>
When deleting by ID, we cast the value to an integer. This guarantees that even if someone tampers with the hidden input field and sends something like DROP TABLE users, it gets converted to 0 — a harmless integer. Always cast numeric inputs to int before using them in queries.
What Did We Learn in This Post?
MySQL is a relational database that stores data in tables with rows and columns. It comes with XAMPP and is managed through phpMyAdmin.
mysqli_connect() establishes a connection from PHP to MySQL. Store the connection in a variable and include db.php in every file that needs database access.
mysqli_query() executes SQL statements. mysqli_fetch_assoc() fetches one row at a time as an associative array inside a while loop.
mysqli_num_rows() tells you how many rows a query returned.
mysqli_real_escape_string() escapes user input before inserting it into SQL queries to prevent SQL Injection. Casting numeric inputs to (int) adds another layer of safety.
CRUD — Create, Read, Update, Delete — are the four fundamental database operations that every application is built around.
What is Coming in Episode 12?
In Episode 12 we level up our database skills with Prepared Statements and PDO.
Prepared statements are the modern, secure, and professional way to run database queries in PHP. They completely eliminate SQL injection vulnerabilities, work with both MySQL and other databases, and are what Laravel's Eloquent ORM uses under the hood. This is the last major Core PHP topic before we transition into Object Oriented PHP and then Laravel.
See you in the next one.
Next Episode: Prepared Statements and PDO — The Professional Way to Handle Databases
This is Episode 11 of the PHP and Laravel — Zero to Hero series.
No comments:
Post a Comment