PHP CRUD with PDO in MySQL

This project will demonstrate how to use PDO to implement CRUD (Create, read, update and delete) operations with MySQL database.

  • CRUDPHPwithPDO
    • database.php
    • edit.php
    • index.php
    • register.php
<?php
    $conn = new PDO("mysql:host=localhost;dbname=mydemo", 'root', '');
?>
<?php
require 'database.php';
if(isset($_POST['save'])) {                
    // Find account by username
    $stmt = $conn->prepare('select * from account where username = :username');
    $stmt->bindValue('username', $_POST['username']);
    $stmt->execute();
    $account = $stmt->fetch(PDO::FETCH_OBJ);
                    
    // Update account information
    $stmt = $conn->prepare('update account set password = :password, fullname = :fullname, email = :email where username = :username');
    $stmt->bindValue('username', $_POST['username']);
    $stmt->bindValue('password', $_POST['password'] == '' ? $account->password : $_POST['password']);
    $stmt->bindValue('fullname', $_POST['fullname']);
    $stmt->bindValue('email', $_POST['email']);
    $stmt->execute();
    header('location:index.php');
}    
$stmt = $conn->prepare('select * from account where username = :username');
$stmt->bindValue('username', $_GET['username']);
$stmt->execute();
$account = $stmt->fetch(PDO::FETCH_OBJ);
?>
<form method="post">
    <fieldset>
        <legend>Account Information</legend>
        <table cellpadding="2" cellspacing="2">
            <tr>
                <td>Username</td>
                <td><?php echo $account->username; ?>
                    <input type="hidden" name="username" value="<?php echo $account->username; ?>"></td>
            </tr>
            <tr>
                <td>Password</td>
                <td><input type="password" name="password"></td>
            </tr>
            <tr>
                <td>Full Name</td>
                <td><input type="text" name="fullname" value="<?php echo $account->fullname; ?>"></td>
            </tr>
            <tr>
                <td>Email</td>
                <td><input type="text" name="email" value="<?php echo $account->email; ?>"></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td><input type="submit" name="save" value="Save"></td>
            </tr>
        </table>
    </fieldset>
</form>
                
<?php
require 'database.php';
if(isset($_POST['save'])) {
    $stmt = $conn->prepare('insert into account(username, password, fullname, email)
    values(:username, :password, :fullname, :email)');
    $stmt->bindValue('username', $_POST['username']);
    $stmt->bindValue('password', $_POST['password']);
    $stmt->bindValue('fullname', $_POST['fullname']);
    $stmt->bindValue('email', $_POST['email']);
    $stmt->execute();
    header('location:index.php');
}
?>
<form method="post">
    <fieldset>
        <legend>Account Information</legend>
        <table cellpadding="2" cellspacing="2">
            <tr>
                <td>Username</td>
                <td><input type="text" name="username"></td>
            </tr>
            <tr>
                <td>Password</td>
                <td><input type="password" name="password"></td>
            </tr>
            <tr>
                <td>Full Name</td>
                <td><input type="text" name="fullname"></td>
            </tr>
            <tr>
                <td>Email</td>
                <td><input type="text" name="email"></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td><input type="submit" name="save" value="Save"></td>
            </tr>
        </table>
    </fieldset>
</form>
        
<?php
require 'database.php';
if(isset($_GET['action']) && $_GET['action'] == 'delete'){
    $stmt = $conn->prepare('delete from account where username = :username');
    $stmt->bindValue('username', $_GET['username']);
    $stmt->execute();
}
$stmt = $conn->prepare('select * from account');
$stmt->execute();
?>
<a href="register.php">Register</a>
<br><br>
<table cellpadding="2" cellspacing="2" border="1">
    <tr>
        <th>Username</th>
        <th>Full Name</th>
        <th>Email</th>
        <th>Option</th>
    </tr>
    <?php while($account = $stmt->fetch(PDO::FETCH_OBJ)) { ?>
        <tr>
            <td><?php echo $account->username; ?></td>
            <td><?php echo $account->fullname; ?></td>
            <td><?php echo $account->email; ?></td>
            <td><a
                href="index.php?username=<?php echo $account->username; ?>
                    &action=delete" onclick="return confirm('Are you sure?')">Delete</a>
                | <a href="edit.php?username=<?php echo $account->username; ?>">Edit</a>
            </td>
        </tr>
    <?php } ?>
</table>
            

Screenshots