PHP and MySQL Integration: Storing Data in Web Applications

![[PHP 강의] PHP 8.4 웹 프로그래밍 강의를 시작하며 – 초보도 끝까지 완주할 수 있는 실전 강의](/uploads/editor/PHP-%EA%B0%95%EC%9D%98-PHP-8-4-%EC%9B%B9-%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%B0%8D-%EA%B0%95%EC%9D%98%EB%A5%BC-%EC%8B%9C%EC%9E%91%ED%95%98%EB%A9%B0-%EC%B4%88%EB%B3%B4%EB%8F%84-%EB%81%9D%EA%B9%8C%EC%A7%80-%EC%99%84%EC%A3%BC%ED%95%A0-%EC%88%98-%EC%9E%88%EB%8A%94-%EC%8B%A4%EC%A0%84-%EA%B0%95%EC%9D%98-20260702-5787b7.webp)
Why Do We Need Databases?
So far, we've stored data in variables.
However, when the program ends, all variable values disappear.
For example, if a post on a bulletin board disappears immediately after refreshing the page, it would be an unusable service.
This is why databases are used to store data persistently even after the program terminates.
What Is MySQL?
MySQL is one of the most widely used open-source relational database management systems (RDBMS).
It's commonly used with PHP and can store various types of data including:
User information
Posts
Comments
Product information
Order history
Admin settings
In this lesson, we'll use PDO (PHP Data Objects), the recommended method for connecting PHP to MySQL.
Why Use PDO?
Previously, mysqli was commonly used, but PDO is now recommended.
The advantages of PDO include:
Enhanced security
Support for Prepared Statements
Ability to work with multiple databases using the same approach
Easier maintenance and updates
PDO is widely used in professional projects.
Preparing Your Database
First, create a database in MySQL.
For example:
CREATE DATABASE php84;
Select the database:
USE php84;
Creating a Board Table
Let's create a simple bulletin board table:
CREATE TABLE board (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This structure is commonly used in bulletin board projects.
Connecting PHP to MySQL
First, write the database connection information:
<?php
$host = "localhost";
$dbname = "php84";
$user = "root";
$password = "";
Now create the PDO object:
<?php
$pdo = new PDO(
"mysql:host={$host};dbname={$dbname};charset=utf8mb4",
$user,
$password
);
If the connection is successful, your database is ready to use.
Handling Connection Errors
In professional development, exception handling should always be implemented:
<?php
try {
$pdo = new PDO(
$dsn,
$user,
$password
);
}
catch (PDOException $e) {
exit($e->getMessage());
}
This allows you to see error details if the connection fails.
What Is INSERT?
INSERT is an SQL command for storing new data.
For example, to store a post:
INSERT INTO board
(title, content)
VALUES
('First Post', 'PHP lesson.');
In practice, PHP would handle this storage process directly.
Executing INSERT in PHP
<?php
$sql = "
INSERT INTO board
(title, content)
VALUES
(?, ?)
";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"First Post",
"PHP lesson."
]);
A single post is now stored in the database.
What Are Prepared Statements?
This method prevents SQL injection attacks.
Instead of directly inserting values, you prepare a statement and pass values separately:
$stmt = $pdo->prepare($sql);
And then:
$stmt->execute([
$title,
$content
]);
This is the safest method for database operations.
What Is SELECT?
SELECT is the command for retrieving stored data.
SELECT *
FROM board;
This retrieves all posts.
Retrieving Data in PHP
<?php
$sql = "
SELECT *
FROM board
";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll();
Now $rows contains the list of posts.
Outputting with foreach
<?php
foreach ($rows as $row) {
echo $row["title"];
echo "<br>";
}
Output:
First Post
Second Post
Third Post
Bulletin board listings are created using the same principle.
fetch()
This method retrieves a single row:
<?php
$row = $stmt->fetch();
echo $row["title"];
Commonly used for viewing individual posts.
Using WHERE Conditions
You can retrieve specific posts:
SELECT *
FROM board
WHERE id = 1;
In PHP:
<?php
$sql = "
SELECT *
FROM board
WHERE id = ?
";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
$post = $stmt->fetch();
UPDATE
This command modifies a post:
UPDATE board
SET title='Updated'
WHERE id=1;
In PHP:
$stmt = $pdo->prepare(
"UPDATE board
SET title=?
WHERE id=?"
);
$stmt->execute([
"Updated",
1
]);
DELETE
This command deletes a post:
DELETE
FROM board
WHERE id=1;
In PHP:
$stmt = $pdo->prepare(
"DELETE FROM board
WHERE id=?"
);
$stmt->execute([1]);
What Is CRUD?
CRUD (Create, Read, Update, Delete) represents the fundamental operations in web development.
Operation | SQL Command |
|---|---|
Create | INSERT |
Read | SELECT |
Update | UPDATE |
Delete | DELETE |
Bulletin boards, user management systems, and e-commerce platforms all use CRUD as their foundation.
Separating Database Connection Code
In professional development, connection code is managed separately:
db.php
<?php
$pdo = new PDO(
$dsn,
$user,
$password
);
Usage:
<?php
require_once __DIR__ . "/db.php";
This makes project management much easier.
Practical Example
Let's create a simple post storage program:
<?php
require_once "db.php";
$sql = "
INSERT INTO board
(title, content)
VALUES
(?, ?)
";
$stmt = $pdo->prepare($sql);
$stmt->execute([
$_POST["title"],
$_POST["content"]
]);
echo "Saved successfully";
This structure is nearly identical to a standard bulletin board post creation feature.
Common Beginner Mistakes
New learners often make these mistakes when working with MySQL:
Incorrect database connection information
Forgetting to set the charset
Not using Prepared Statements
Failing to check SQL errors
Mixing up fetch() and fetchAll()
Not verifying INSERT results
Being careful with these points can resolve most common issues.
What's Next for Our Bulletin Board?
The concepts covered in this lesson will form the foundation of our upcoming bulletin board project.
For example:
Writing posts
Displaying lists
Viewing details
<Editing posts
Deleting posts
User registration
Login functionality
All these features will be implemented using database connections.
Understanding CRUD means you've mastered about half of web development fundamentals.
In Conclusion
This lesson covered how to connect PHP with MySQL for data storage and retrieval.
Databases are fundamental to all web services.
Using PDO and Prepared Statements from the beginning is crucial for security and maintainability.
In our next lesson, we'll implement a full bulletin board system including post creation, listing, editing, and deletion (CRUD) as a practical application of these concepts.
FAQ
What is the best way to connect PHP to a database?
PDO (PHP Data Objects) is recommended for its security features, support for prepared statements, and compatibility with multiple databases.
Why should I use Prepared Statements?
Prepared Statements prevent SQL injection attacks by separating data from the SQL command, making your application more secure.
What does CRUD stand for in web development?
CRUD stands for Create, Read, Update, and Delete - the four fundamental operations for managing data in a database.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [PHP 강좌] 9강 PHP에서 MySQL 연결하고 데이터 저장하기