PHP & MySQL: Build Your First Database-Driven Website

PHP & MySQL: Build Your First Database-Driven Website

Are you ready to build a real, dynamic website? In this beginner-friendly tutorial, you'll learn how to connect PHP with MySQL to create a database-driven web application. By the end, you'll have a working project that can store, display, and manage data — the foundation of every modern web app.

What You'll Learn

What a database-driven website is
Setting up MySQL database and tables
Connecting PHP to MySQL with PDO
Inserting data into a database
Fetching and displaying data
Updating and deleting records (CRUD basics)
Security best practices (prepared statements)

Prerequisites

PHP installed (version 8.0 or higher recommended)
MySQL or MariaDB installed
A local server like XAMPP, WAMP, or Laragon (or just PHP's built-in server + MySQL CLI)
Basic PHP syntax knowledge (variables, functions, arrays)

Let's dive in!

Step 1: What is a Database-Driven Website?

A database-driven website stores its content in a database instead of hardcoding it in HTML files. When a user visits a page, PHP queries the database and dynamically generates the HTML.

Static site: You edit HTML files manually.
Database-driven site: You edit database records, and PHP builds the pages automatically.

This is how blogs, e-commerce stores, social media, and CMS platforms like WordPress work.

Step 2: Set Up Your Database

First, create a database and a table. Open your MySQL terminal or phpMyAdmin and run:

CREATE DATABASE IF NOT EXISTS blog_demo;

USE blog_demo;

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author VARCHAR(100) DEFAULT 'Anonymous',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates a posts table with an auto-incrementing ID, a title, content body, author name, and a timestamp.

Insert some sample data:

INSERT INTO posts (title, content, author) VALUES
('My First Blog Post', 'Hello world! This is my first database-driven post.', 'John'),
('Learning PHP', 'PHP makes it easy to build dynamic websites.', 'John'),
('MySQL Basics', 'MySQL is a powerful relational database management system.', 'Jane');

Step 3: Connect PHP to MySQL

Create a file called config.php:

<?php
$host = '127.0.0.1';
$dbname = 'blog_demo';
$username = 'root';
$password = '';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    echo "✅ Connected successfully!";
} catch (PDOException $e) {
    die("❌ Connection failed: " . $e->getMessage());
}

Key points:

PDO (PHP Data Objects) works with multiple database types — MySQL, PostgreSQL, SQLite, etc.
charset=utf8mb4 supports emojis and special characters
ERRMODE_EXCEPTION throws exceptions on errors (easier to debug)
FETCH_ASSOC returns results as associative arrays

Step 4: Display All Posts

Create index.php — your homepage:

<?php require 'config.php'; ?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>My Blog</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
        .post { border-bottom: 1px solid #eee; padding: 15px 0; }
        .post h2 { margin: 0 0 5px; }
        .post .meta { color: #666; font-size: 0.9em; }
        a { color: #3498db; text-decoration: none; }
        a:hover { text-decoration: underline; }
    </style>
</head>
<body>
    <h1>My Blog</h1>
    <a href="create.php">+ New Post</a>

    <?php
    $stmt = $pdo->query("SELECT * FROM posts ORDER BY created_at DESC");
    $posts = $stmt->fetchAll();

    foreach ($posts as $post):
    ?>
        <div class="post">
            <h2>
                <a href="post.php?id=<?= $post['id'] ?>">
                    <?= htmlspecialchars($post['title']) ?>
                </a>
            </h2>
            <p><?= nl2br(htmlspecialchars(substr($post['content'], 0, 200))) ?>...</p>
            <div class="meta">
                By <?= htmlspecialchars($post['author']) ?> |
                <?= date('F j, Y', strtotime($post['created_at'])) ?>
            </div>
        </div>
    <?php endforeach; ?>
</body>
</html>

Security tip: Always use htmlspecialchars() when outputting data from the database — it prevents XSS (cross-site scripting) attacks.

Step 5: Add New Posts (INSERT)

Create create.php:

<?php require 'config.php'; ?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>New Post</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px; }
        input, textarea { width: 100%; padding: 8px; margin: 5px 0 15px; border: 1px solid #ddd; border-radius: 4px; }
        button { background: #3498db; color: white; border: none; padding: 10px 20px; cursor: pointer; border-radius: 4px; }
        .success { background: #2ecc71; color: white; padding: 10px; border-radius: 4px; margin-bottom: 15px; }
    </style>
</head>
<body>
    <h1>Create New Post</h1>
    <a href="index.php">← Back to Blog</a>

    <?php if ($_SERVER['REQUEST_METHOD'] === 'POST'):
        $title = $_POST['title'] ?? '';
        $content = $_POST['content'] ?? '';
        $author = $_POST['author'] ?? 'Anonymous';

        // Validate
        $errors = [];
        if (empty(trim($title))) $errors[] = 'Title is required';
        if (empty(trim($content))) $errors[] = 'Content is required';

        if (empty($errors)) {
            // Use prepared statement to prevent SQL injection
            $stmt = $pdo->prepare(
                "INSERT INTO posts (title, content, author) VALUES (:title, :content, :author)"
            );
            $stmt->execute([
                ':title' => htmlspecialchars($title),
                ':content' => htmlspecialchars($content),
                ':author' => htmlspecialchars($author),
            ]);
            echo '<div class="success">✅ Post created successfully!</div>';
        } else {
            foreach ($errors as $error) {
                echo "<p style='color:red'>❌ $error</p>";
            }
        }
    endif; ?>

    <form method="POST">
        <label>Title *</label>
        <input type="text" name="title" required>

        <label>Content *</label>
        <textarea name="content" rows="6" required></textarea>

        <label>Author</label>
        <input type="text" name="author" value="Anonymous">

        <button type="submit">Publish Post</button>
    </form>
</body>
</html>

Why prepared statements? They separate SQL logic from data, making SQL injection impossible. Never insert user data directly into SQL strings with concatenation.

Step 6: View a Single Post

Create post.php:

<?php require 'config.php'; ?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>View Post</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
        .meta { color: #666; font-size: 0.9em; margin-bottom: 20px; }
        a { color: #3498db; }
    </style>
</head>
<body>
    <a href="index.php">← Back to Blog</a>

    <?php
    $id = $_GET['id'] ?? 0;

    // Prepared statement — safe from SQL injection
    $stmt = $pdo->prepare("SELECT * FROM posts WHERE id = :id");
    $stmt->execute([':id' => $id]);
    $post = $stmt->fetch();

    if (!$post):
    ?>
        <h1>Post not found</h1>
        <p>Sorry, that post doesn't exist.</p>
    <?php else: ?>
        <h1><?= htmlspecialchars($post['title']) ?></h1>
        <div class="meta">
            By <?= htmlspecialchars($post['author']) ?> |
            <?= date('F j, Y \a\t g:i A', strtotime($post['created_at'])) ?>
        </div>
        <div><?= nl2br(htmlspecialchars($post['content'])) ?></div>
        <p style="margin-top:20px">
            <a href="edit.php?id=<?= $post['id'] ?>">✏️ Edit</a> |
            <a href="delete.php?id=<?= $post['id'] ?>"
               onclick="return confirm('Delete this post?')">🗑️ Delete</a>
        </p>
    <?php endif; ?>
</body>
</html>

Step 7: Edit Posts (UPDATE)

Create edit.php:

<?php require 'config.php'; ?>

<?php
$id = $_GET['id'] ?? 0;

// Fetch existing post
$stmt = $pdo->prepare("SELECT * FROM posts WHERE id = :id");
$stmt->execute([':id' => $id]);
$post = $stmt->fetch();

if (!$post) {
    die('Post not found');
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare(
        "UPDATE posts SET title = :title, content = :content, author = :author WHERE id = :id"
    );
    $stmt->execute([
        ':title' => htmlspecialchars($_POST['title']),
        ':content' => htmlspecialchars($_POST['content']),
        ':author' => htmlspecialchars($_POST['author'] ?? 'Anonymous'),
        ':id' => $id,
    ]);
    $success = '✅ Post updated!';
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Edit Post</title>
    <style>
        body { font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px; }
        input, textarea { width: 100%; padding: 8px; margin: 5px 0 15px; border: 1px solid #ddd; border-radius: 4px; }
        button { background: #27ae60; color: white; border: none; padding: 10px 20px; cursor: pointer; border-radius: 4px; }
        .success { background: #2ecc71; color: white; padding: 10px; border-radius: 4px; margin-bottom: 15px; }
    </style>
</head>
<body>
    <h1>Edit Post</h1>
    <a href="post.php?id=<?= $id ?>">← View Post</a>

    <?php if (isset($success)) echo "<div class='success'>$success</div>"; ?>

    <form method="POST">
        <label>Title</label>
        <input type="text" name="title" value="<?= htmlspecialchars($post['title']) ?>" required>

        <label>Content</label>
        <textarea name="content" rows="6" required><?= htmlspecialchars($post['content']) ?></textarea>

        <label>Author</label>
        <input type="text" name="author" value="<?= htmlspecialchars($post['author']) ?>">

        <button type="submit">Update Post</button>
    </form>
</body>
</html>

Step 8: Delete Posts (DELETE)

Create delete.php:

<?php
require 'config.php';

$id = $_GET['id'] ?? 0;

$stmt = $pdo->prepare("DELETE FROM posts WHERE id = :id");
$stmt->execute([':id' => $id]);

header('Location: index.php');
exit;

Putting It All Together

Your project structure:

blog-demo/
├── config.php      # Database connection
├── index.php       # Homepage — list all posts
├── create.php      # Add new post
├── post.php        # View single post (with edit/delete links)
├── edit.php        # Edit a post
└── delete.php      # Delete a post

Run it with PHP's built-in server:

php -S localhost:8000

Then visit http://localhost:8000 in your browser.

Security Recap

Practice	Why
Prepared statements	Prevents SQL injection
htmlspecialchars()	Prevents XSS attacks
Validate input	Stops empty/broken data
Never trust user input	Filter and sanitize everything

What's Next?

Now that you've built your first database-driven website, you can expand it with:

User authentication — login/register system
Categories and tags — organize posts
Search functionality — search posts by keyword
Image uploads — allow post images
SEO-friendly URLs — change ?id=1 to /my-post-title
A framework — Laravel or Symfony for larger projects

Summary

Congratulations! You've just built a complete CRUD application with PHP and MySQL. You now understand:

✅ How to connect PHP to MySQL using PDO
✅ How to create, read, update, and delete database records
✅ How to secure your app against SQL injection and XSS
✅ How to structure a simple PHP project

This is the same pattern used by WordPress, Laravel, and every major PHP application. Master these fundamentals, and you can build anything on the web.

Happy coding! 🚀