PHP: List/Detail and CRUD functions

May 1, 2021

On this post we are creating sample code that implement the CRUD functions -- Create, Read, Update, Delete -- using basic php code.
This code does not use any framework or external library besides standard php.

We start by creating a connection to our database.
In this sample we use a "notes" table whose structure can be found at the bottom of this page.


define('DB_HOST', 'Your-database-host');
define('DB_USER', 'Your-database-username');
define('DB_PASS', 'Your-database-password');
define('DB_NAME', 'Your-database-name');

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

echo '<html>' . "\n";
echo '<head>' . "\n";
echo '<title>Notes</title>' . "\n";
echo '</head>' . "\n";
echo '<body>' . "\n";

We define a url parameter named "action" which will be used to select the operation we want to execute.
On a switch statement, we determine which CRUD function will be called based on the "action" parameter.



$action = !empty($_REQUEST['action']) ? $_REQUEST['action'] : '';

switch ($action) {
    case 'view':
        view_note();
        break;
    case 'form':
        form_note();
        break;
    case 'store':
        store_note();
        break;
    case 'update':
        update_note();
        break;
    case 'delete':
        delete_note();
        break;
    default:
        list_notes();
}

echo '</body>' . "\n";
echo '</html>' . "\n";

We define a function to list the records from our table.


function list_notes() {
    global $mysqli;

    $sql = 'select * from notes order by id';
    $query = $mysqli->query($sql) or die($mysqli->error);

    echo '<table border=1>' . "\n";

    echo '<tr>' . "\n";
    echo '<th>ID</th>' . "\n";
    echo '<th>Title</th>' . "\n";
    echo '<th>View</th>' . "\n";
    echo '<th>Edit</th>' . "\n";
    echo '<th>Delete</th>' . "\n";
    echo '</tr>' . "\n";

    while ($row = $query->fetch_object()) {
        echo '<tr>' . "\n";
        echo '<td>' . $row->id . '</td>' . "\n";
        echo '<td>' . htmlentities($row->title) . '</td>' . "\n";
        echo '<td><a href="?action=view&id=' . $row->id . '">View</a></td>' . "\n";
        echo '<td><a href="?action=form&id=' . $row->id . '">Edit</a></td>' . "\n";
        echo '<td><a href="?action=delete&id=' . $row->id . '" onclick="return confirm(\'Are you sure you wish to delete this record?\');">Delete</a></td>' . "\n";
        echo '</tr>' . "\n";
    }
    echo '</table>' . "\n";

    echo '<p><a href="?action=form">Add</a></p>' . "\n";
}

We define a function to view the selected record.



function view_note() {
    global $mysqli;

    $id = !empty($_REQUEST['id']) ? intval($_REQUEST['id']) : '';

    $sql = 'select * from notes where id = ' . $id;
    $query = $mysqli->query($sql) or die($mysqli->error);
    $row = $query->fetch_object();

    $title = $row->title;
    $content = $row->content;
    $created_at = $row->created_at;
    $updated_at = $row->updated_at;

    echo '<h1>' . htmlentities($title) . '</h1>' . "\n";

    echo '<table border=1>' . "\n";

    echo '<tr>' . "\n";
    echo '<td>' . nl2br(str_replace(' ', ' ', htmlentities($content))) . '</td>' . "\n";
    echo '</tr>' . "\n";

    echo '</table>' . "\n";

    echo '<p>Created At: ' . $created_at . '</p>' . "\n";
    echo '<p>Updated At: ' . $updated_at . '</p>' . "\n";

    echo '<p><a href="' . $_SERVER['PHP_SELF'] . '">Back</a></p>' . "\n";
}

We define a function to edit the selected record.


function form_note() {
    global $mysqli;

    $id = !empty($_REQUEST['id']) ? intval($_REQUEST['id']) : '';

    // Add
    if (empty($id)) {
        $title = '';
        $content = '';
        $created_at = '';
        $updated_at = '';
    }
    // Edit
    else {
        $sql = 'select * from notes where id = ' . $id;
        $query = $mysqli->query($sql) or die($mysqli->error);
        $row = $query->fetch_object();

        $title = $row->title;
        $content = $row->content;
        $created_at = $row->created_at;
        $updated_at = $row->updated_at;
    }

    // Submitted data
    if (!empty($_REQUEST['submit'])) {
        $title = trim($_POST['title']);
        $content = trim($_POST['content']);
    }

    echo '<form action="?' . (empty($id) ? 'action=store' : 'action=update') . '" method="post">' . "\n";

    echo '<table border=1>' . "\n";

    echo '<tr>' . "\n";
    echo '<td>Title</td>' . "\n";
    echo '<td><input type="text" name="title" value="' . $title . '" size="58"/></td>' . "\n";
    echo '</tr>' . "\n";

    echo '<tr>' . "\n";
    echo '<td valign="top">Content</td>' . "\n";
    echo '<td><textarea name="content" cols="50" rows="20">' . htmlentities($content) . '</textarea></td>' . "\n";
    echo '</tr>' . "\n";

    echo '<tr>' . "\n";
    echo '<td>Created At</td>' . "\n";
    echo '<td>' . $created_at . '</td>' . "\n";
    echo '</tr>' . "\n";

    echo '<tr>' . "\n";
    echo '<td>Updated At</td>' . "\n";
    echo '<td>' . $updated_at . '</td>' . "\n";
    echo '</tr>' . "\n";

    echo '</table>' . "\n";

    echo '<input type="hidden" name="id" value="' . $id . '" />' . "\n";
    echo '<p><input type="submit" name="submit" value="Submit" /></p>' . "\n";
    echo '</form>' . "\n";

    echo '<p><a href="' . $_SERVER['PHP_SELF'] . '">Back</a></p>' . "\n";
}

We define a function to validate the data submitted in the above form.


function validate_note() {
    $errors = [];

    $title = trim($_POST['title']);
    $content = trim($_POST['content']);

    if (empty($title)) {
        $errors[] = 'Title is empty';
    }
    if (empty($content)) {
        $errors[] = 'Content is empty';
    }

    return $errors;
}

We define a function to save a new record.


function store_note() {
    global $mysqli;

    $errors = validate_note();

    if (!$errors) {
        $title = trim($_POST['title']);
        $content = trim($_POST['content']);

        $sql = 'insert into notes (title, content, created_at, updated_at) 
            values (
            "' . $mysqli->real_escape_string($title) . '", 
            "' . $mysqli->real_escape_string($content) . '",
            NOW(),
            NOW()
            )';
        $query = $mysqli->query($sql) or die($mysqli->error);

        header('Location: ' . $_SERVER['PHP_SELF']);
        exit;
    }
    else {
        foreach ($errors as $e) {
            echo '<div style="color:red;">' . $e . '</div>' . "\n";
        }

        form_note();
    }
}

We define a function to update an existing record.


function update_note() {
    global $mysqli;

    $errors = validate_note();

    if (!$errors) {
        $id = intval($_POST['id']);
        $title = trim($_POST['title']);
        $content = trim($_POST['content']);

        $sql = 'update notes set 
            title = "' . $mysqli->real_escape_string($title) . '", 
            content = "' . $mysqli->real_escape_string($content) . '",
            updated_at = NOW()
            where id = ' . $id;
        $query = $mysqli->query($sql) or die($mysqli->error);

        header('Location: ' . $_SERVER['PHP_SELF']);
        exit;
    }
    else {
        foreach ($errors as $e) {
            echo '<div style="color:red;">' . $e . '</div>' . "\n";
        }

        form_note();
    }
}

We define a function to delete the selected record.


function delete_note() {
    global $mysqli;

    $id = !empty($_GET['id']) ? intval($_GET['id']) : '';

    $sql = 'delete from notes where id = ' . $id;
    $query = $mysqli->query($sql) or die($mysqli->error);

    header('Location: ' . $_SERVER['PHP_SELF']);
    exit;
}

The following is the structure of the "notes" table.


CREATE TABLE `notes` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `notes`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

« Previous

PHP: Pagination of an array of data

How to install VirtualBox on Mac OS X

PHP: List/Detail and CRUD functions

Related Posts