[PHP for Beginners] The Basics of Linking PHP and Databases (SQL) to Display a List
In previous articles, we've learned the basic syntax of PHP, such as arrays, loops, and functions. However, on actual websites, data like notices and product lists are generally not written directly into PHP files but are managed in a special vault called a database.
"How do I connect PHP and a database?" and "How do I display data retrieved with SQL on a web page?" These are major hurdles that many web creators face. This article will clear up those questions. Let's experience the entire process of connecting to a database from PHP, and displaying the data retrieved with SQL as a table using a `foreach` loop, with a complete, copy-and-paste-ready code. Once you feel the excitement of the moment PHP and SQL connect, you'll surely want to try it with your own data!
To get your PHP running (development environment), if you haven't set up XAMPP yet, please refer to [PHP for Beginners] A Complete Guide to Downloading and Installing XAMPP!. After installation, place your PHP file in the specified folder (like `htdocs`), and you're all set!
1. The 3 Steps of PHP and Database Collaboration
The flow of how PHP interacts with a database is broadly composed of the following three steps.
- Connect: PHP says "Hello, may I talk to you?" to the database and establishes a connection.
- Execute Query: PHP gives a command to the database in a language called SQL, saying "Please give me this data from this table."
- Display Results: PHP converts the data received from the database into HTML and displays it on the web page.
This time, we will use a mechanism called PDO (PHP Data Objects) to achieve these three steps in a safe and modern way. PDO is a standard feature of PHP that can handle various types of databases and is also excellent in terms of security, making it essential knowledge in current PHP development.
2. In Practice! Displaying a Product List from a Database
Now, let's actually write the code to retrieve and display a product list from a database.
Step A: Preparing the Database and Table
First, you need to prepare a table to store the data. In a database management tool like phpMyAdmin, execute the following SQL statement to create a `products` table and register three sample data items.
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO products (name, price) VALUES
('High-performance Laptop', 120000),
('Wireless Earphones', 15000),
('4K Monitor', 45000);Step B: Retrieving and Displaying Data with PHP
Once the table is ready, it's PHP's turn. The following code is a complete sample that includes everything from connecting to the database, retrieving data, to displaying it in HTML. If you upload this file to your server, you can check the result in your browser.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Product List</title>
<style>
body { font-family: sans-serif; padding: 20px; }
h1 { border-bottom: 2px solid #0056b3; }
.product-table { width: 100%; border-collapse: collapse; margin-top: 20px; }
.product-table th, .product-table td { border: 1px solid #ddd; padding: 12px; text-align: left; }
.product-table th { background-color: #f2f2f2; }
.product-table tr:nth-child(even) { background-color: #f9f9f9; }
.error-message { color: red; font-weight: bold; border: 1px solid red; padding: 10px; }
</style>
</head>
<body>
<h1>Product List</h1>
<?php
// --- 1. Database Connection ---
$db_host = 'localhost'; // Database server hostname
$db_name = 'your_database_name'; // Database name
$db_user = 'your_username'; // Database username
$db_pass = 'your_password'; // Database password
try {
// Create a PDO object to connect to the database
$pdo = new PDO("mysql:host={$db_host};dbname={$db_name};charset=utf8", $db_user, $db_pass);
// Set error mode to throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// --- 2. SQL Execution ---
// Query to get all data from the products table
$stmt = $pdo->prepare("SELECT id, name, price FROM products ORDER BY id");
$stmt->execute();
// --- 3. Retrieving and Displaying Results ---
// Fetch all data as an associative array
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($products) {
echo '<table class="product-table">';
echo '<tr><th>Product ID</th><th>Product Name</th><th>Price</th></tr>';
// Loop with foreach
foreach ($products as $product) {
echo '<tr>';
echo '<td>' . htmlspecialchars($product['id']) . '</td>';
echo '<td>' . htmlspecialchars($product['name']) . '</td>';
echo '<td>$&' . number_format($product['price']) . '</td>';
echo '</tr>';
}
echo '</table>';
} else {
echo '<p>No product data found.</p>';
}
} catch (PDOException $e) {
// Handling for connection or query errors
echo '<div class="error-message">Database Error: ' . htmlspecialchars($e->getMessage()) . '</div>';
}
?>
</body>
</html>Important: Before running the code above, be sure to replace `your_database_name`, `your_username`, and `your_password` with your own environment's settings.
If you access this file in your browser and the product list is displayed in the table, you've succeeded! This is the moment when PHP interacts with the database and dynamically generates the result as HTML.
3. Points to Be Careful About
Database integration is very powerful, but there are a few points to be careful about. Security and error handling are particularly important.
Error Handling (try...catch syntax)
If the database password is wrong or there's a mistake in the SQL syntax, the program will stop with an error. By enclosing the entire process in a `try...catch` block, even if an error occurs, you can catch it and display a custom error message instead of the site going blank.
<?php
try {
// Process to connect to the database and retrieve data
// If an error occurs within this block...
$pdo = new PDO("mysql:host=localhost;dbname=wrong_db;charset=utf8", "user", "pass");
} catch (PDOException $e) {
// ...the processing here will be executed
echo "An error occurred: " . $e->getMessage();
// On a real site, you would show a simple message to the user,
// and log the error details to a file.
// exit(); // Stop the process
}
?>This mechanism prevents confusing users when an unexpected error occurs and makes it easier for developers to identify the cause of the error.
SQL Injection Countermeasures
When constructing an SQL statement based on user input (like search keywords), you are at risk of a very dangerous attack called **SQL injection**. This is an attack where malicious SQL statements are injected to manipulate the database improperly.
PDO's **prepared statements** (the method using `prepare()` and `execute()`) are the most effective countermeasure against this SQL injection. Even if there's no user input this time, you should get into the habit of always using this method.
4. Summary
In this article, we learned the three basic steps of integrating PHP and databases (Connect, Command, Display) from start to finish using PDO. Being able to dynamically retrieve and display data from an external storage like a database, instead of writing it directly in a PHP file, is a major step forward in web application development.
Once you master this basic form, you can apply it in various ways, such as adding a `WHERE` clause to filter data by a specific condition, or integrating it with a form to register data with an `INSERT` statement. We encourage you to try various things with your own tables and data!