Daily Blog Post: July 25th, 2023

July 25th, 2023

Building a Simple Web Application with PHP and MySQL Integration

Welcome back to our SQL blog series! In the last post, we covered how to connect to a MySQL database using PHP and the PDO extension. In this post, we will cover how to use PHP to create a simple web application that interacts with a MySQL database.

Creating a Simple Web Application

To create a simple web application that interacts with a MySQL database using PHP, we will use the following steps:

Establish a connection to the MySQL database using PDO.
Define an HTML form that allows users to input data.
Use PHP to process the form data and insert it into the MySQL database.
Use PHP to retrieve data from the MySQL database and display it on the web page.

<?php
// Establish a connection to the MySQL database
$host = 'localhost';
$dbname = 'mydatabase';
$username = 'myusername';
$password = 'mypassword';

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// Process form data and insert it into the MySQL database
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $age = $_POST['age'];

    $stmt = $pdo->prepare('INSERT INTO mytable (name, age) VALUES (:name, :age)');
    $stmt->execute(['name' => $name, 'age' => $age]);
}

// Retrieve data from the MySQL database and display it on the web page
$stmt = $pdo->prepare('SELECT * FROM mytable');
$stmt->execute();
$rows = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html>
<head>
    <title>My Web Application</title>
</head>
<body>
    <form method="post">
        <label>Name:</label>
        <input type="text" name="name">
        <br>
        <label>Age:</label>
        <input type="text" name="age">
        <br>
        <input type="submit" value="Submit">
    </form>
    <br>
    <table>
        <thead>
            <tr>
                <th>Name</th>
                <th>Age</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($rows as $row): ?>
            <tr>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['age']; ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</body>
</html>

In this example, we first establish a connection to our MySQL database using PDO. We then define an HTML form that allows users to input data. When the form is submitted, we use PHP to process the form data and insert it into our MySQL database.

Finally, we retrieve data from the MySQL database and display it on the web page using PHP. We use a foreach loop to iterate through each row returned by the SELECT statement and display the name and age in an HTML table.

Conclusion

In this post, we covered how to use PHP to create a simple web application that interacts with a MySQL database. By following these steps, you can create a basic CRUD (Create, Read, Update, Delete) application that allows users