facebook google plus twitter
Webucator's Free PHP Tutorial

Lesson: LAB: Inserting, Updating, and Deleting Poems

Welcome to our free PHP tutorial. This tutorial is based on Webucator's Introduction to PHP Training course.

This lesson consists of four exercises:

  1. Creating a page to submit a new poem.
  2. Modifying the poems page so that it shows all the current user's poems, even those not yet approved.
  3. Creating a page to edit an existing poem.
  4. Creating a page to delete a poem.

This lesson has no final quiz.

Lesson Goals

  • To insert, update, and delete records by doing a hands-on lab.

Submitting a New Poem

Duration: 45 to 90 minutes.

In this exercise, you will write a PHP page from scratch for submitting a new poem.

  1. Open a new document and save it as poem-submit.php in the InsertUpdateDelete/Exercises/phppoetry.com folder.
  2. Write code to create a page that looks like the one below:phppoetry.com Submit poem
  3. If the poem title is missing, or no category was selected, or the poem length is shorter than 10 characters, report an error and reshow the form with the user's entries still in it. Otherwise, insert the poem into the database and redirect to the appropriate poem page:phppoetry.com Submit poem - submitted
  4. We have covered all the PHP you need to be able to do this. You will need two SQL queries:
    1. One for getting the list of categories:
      SELECT c.category_id, c.category
      FROM categories c
      LEFT JOIN poems p ON c.category_id = p.category_id
      GROUP BY c.category_id
      ORDER BY c.category
    2. One for inserting the poem:
      INSERT INTO poems
      (title, poem, category_id, user_id, date_approved)
      VALUES (:title, :poem, :category_id, :user_id,
                $dateApproved)
      You will need to bind the parameters to values submitted through the form. $dateApproved should be set to 'null'. In the future, you might want to change this so that when an administrator submits a new poem it is approved immediately. Administrators will be able to approve new poems through an admin interface to be built later.
  5. Be sure to write code to catch and log potential exceptions.
  6. Note that only authenticated users should be able to reach this page. Any other user should be immediately redirected to the login page with a no-access URL parameter set to 1: login.php?no-access=1 phppoetry.com Submit Poem - Not logged in
  7. Open login.php in your editor. After the if (isset($_GET['just-registered'])) check, add an elseif block that checks if no-access exists in the $_GET array. If it does, output the POEM_ACCESS_DENIED constant just as we did with the POEM_REGISTRATION_SUCCESS constant in the if block.
  8. Open poem.php in your editor. Modify it so that it outputs "Pending Approval" instead of "Date Approved:" if the poem has not yet been approved.
  9. Test your solution in a browser by navigating to Visit http://localhost:8888/Webucator/php/InsertUpdateDelete/Exercises/phppoetry.com/poem-submit.php and writing and submitting a new poem.

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/poem-submit.php
<?php
  $pageTitle = 'Submit Poem';
  require 'includes/header.php';
  
  if (!isAuthenticated()) {
    header("Location: login.php?no-access=1");
  }

  $errors = [];
  $f['category'] = $_POST['cat'] ?? 0;
  $f['title'] = trim($_POST['title'] ?? '');
  $f['poem'] = trim($_POST['poem'] ?? '');

  $qCategories = "SELECT c.category_id, c.category
    FROM categories c
    LEFT JOIN poems p ON c.category_id = p.category_id
    GROUP BY c.category_id
    ORDER BY c.category";

  try {
    $stmtCats = $db->prepare($qCategories);
    $stmtCats->execute();
  } catch (PDOException $e) {
    logError($e->getMessage());
    $errors[] = 'Oops. Our bad. Cannot get categories.';
  }

  if (!empty($_POST['submit'])) {
    // Validate Form Entries
    if (!$f['title']) {
      $errors[] = 'You must enter a poem title.';
    }
    if (!$f['category']) {
      $errors[] = 'You must select a category.';
    }
    if (!$f['poem'] || strlen($f['poem']) < 10) {
      $errors[] = 'Your poem must be at least 10 characters.';
    }

    if (!$errors) {
      // Insert poem
      $dateApproved = 'null'; // For now

      $qInsert = "INSERT INTO poems
      (title, poem, category_id, user_id, date_approved)
      VALUES (:title, :poem, :category_id, :user_id,
              $dateApproved);";

      try {
        $stmtInsert = $db->prepare($qInsert);
        $stmtInsert->bindParam(':title', $f['title']);
        $stmtInsert->bindParam(':poem', $f['poem']);
        $stmtInsert->bindParam(':category_id', $f['category']);
        $stmtInsert->bindParam(':user_id', $currentUserId);
        $stmtInsert->execute();

        $lastInsertId = $db->lastInsertId();
        header("Location: poem.php?poem-id=$lastInsertId");
      } catch (PDOException $e) {
        logError($e->getMessage());
        $errors[] = 'Oops. Our bad. Cannot insert poem.';
      }
    }
  }
?>
<main id="poem-submit">
  <h1><?= $pageTitle ?></h1>
  <?php 
    if ($errors) {
      echo '<h3>Please correct the following errors:</h3>
      <ol class="error">';
      foreach ($errors as $error) {
        echo "<li>$error</li>";
      }
      echo '</ol>';
    }
  ?>
  <form method="post" action="poem-submit.php" novalidate>
    <label for="title">Title*:</label>
    <input name="title" id="title"
      value="<?= $f['title'] ?>" required>
    <label for="cat">Category:</label>
    <select name="cat" id="cat">
      <option value="0">--Choose a Category</option>
      <?php
        while ($row = $stmtCats->fetch()) {
          $category = $row['category'];
          $categoryId = $row['category_id'];
          $selected = $categoryId === $f['category'] ?
            ' selected' : '';
          echo "<option value='$categoryId'$selected>
            $category
          </option>";
        }
      ?>
    </select>
    <label for="poem">Poem*:</label>
    <textarea id="poem" name="poem"><?= $f['poem'] ?></textarea>
    <button name="submit" value="1" class="wide">Submit Poem</button>
  </form>
</main>
<?php
  require 'includes/footer.php';
?>

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/poem-1.php
---- C O D E   O M I T T E D ----
  <div id="approval-status">
    <?php
      if ($dateApproved) {
        echo 'Approved: ' . date('m/d/Y', strtotime($dateApproved));
      } else {
        echo 'Pending Approval';
      }
    ?>
  </div>
---- C O D E   O M I T T E D ----

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/login.php
---- C O D E   O M I T T E D ----
    if (isset($_GET['just-registered'])) {
      echo '<article class="poem success">' .
          nl2br(POEM_REGISTRATION_SUCCESS) .
        '</article>';
    } elseif (isset($_GET['no-access'])) {
      echo '<article class="poem success">' .
          nl2br(POEM_ACCESS_DENIED) .
        '</article>';
    } else {
      echo '<p>Need an account? 
        <a href="register.php">Register</a></p>';
    }
---- C O D E   O M I T T E D ----

Showing All User's Poems in on the Poems Page

Duration: 25 to 40 minutes.

In this exercise, you will modify poems.php so that users can see all their own poems, even the ones that have not yet been approved, in the poems table.

  1. Change the WHERE clause in both the $query and $qPoemCount variables to:
    WHERE (p.date_approved IS NOT NULL OR u.user_id = ?)
  2. When first initiation $params, include $currentUserId in the array, like this:
    $params = [$currentUserId];
    This will result in an effective WHERE clause of:
    WHERE (p.date_approved IS NOT NULL OR u.user_id = $currentUserId)
  3. Within the while loop within the tbody, use the ternary operator to define a $cls variable. The value should be 'normal' if the $row['date_approved'] is not null (remember that null is falsy). Otherwise, the value should be 'pending-approval'.
  4. Currently, $published is getting a date based on the value of $row['date_approved']. But if $row['date_approved'] is null, then $published should get "N/A", for not applicable.
  5. Finally, change the open <tr> tag to use the $cls class.

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/poems.php
---- C O D E   O M I T T E D ----

$query = "SELECT p.poem_id, p.title, p.date_approved, 
c.category, u.username
        FROM poems p
        JOIN categories c ON c.category_id = p.category_id
        JOIN users u ON u.user_id = p.user_id
        WHERE (p.date_approved IS NOT NULL
          OR u.user_id = ?)";


$selCatId = $_GET['cat'] ?? 0; // category_id
$selUserId = $_GET['user'] ?? 0; // user_id
$whereConditions = [];
$params = [$currentUserId];

---- C O D E   O M I T T E D ----

$qPoemCount = "SELECT COUNT(p.poem_id) AS num
FROM poems p
  JOIN categories c ON c.category_id = p.category_id
  JOIN users u ON u.user_id = p.user_id
WHERE (p.date_approved IS NOT NULL
          OR u.user_id = ?)";

---- C O D E   O M I T T E D ----

  <tbody>
    <?php
      while ($row = $stmt->fetch()) { 
        $cls = ($row['date_approved'])
              ? 'normal'
              : 'pending-approval';
        if ($row['date_approved']) {
          $approved = strtotime($row['date_approved']);
          $published = date('m/d/Y', $approved);
        } else {
          $published = 'N/A';
        }
    ?>
      <tr class="<?= $cls ?>">
        <td>
          <a href="poem.php?poem-id=<?= $row['poem_id'] ?>">
            <?= $row['title'] ?>
          </a>
        </td>
        <td><?= $row['category'] ?></td>
        <td><?= $row['username'] ?></td>
        <td><?= $published ?></td>
      </tr>
    <?php } ?>
  </tbody>

  ---- C O D E   O M I T T E D ----

Editing an Existing Poem

Duration: 45 to 90 minutes.

In this exercise, you will write a PHP page from scratch for editing an existing poem. Note that you will be making use of a new isPoemAuthor() function in utilities.php:

Code Sample:

InsertUpdateDelete/Solutions/phppoetry.com/includes/utilities.php
---- C O D E   O M I T T E D ----
  function isPoemAuthor($poemId, $userId = null) {
    /*
      Check if user is author of poem
      $userID defaults to logged-in user id
    */
    $db = dbConnect();
    if (!$userId && !isAuthenticated()) {
      return false;
    }
    $userId = $userId ?? $_SESSION['user-id'];
    $q = 'SELECT user_id FROM poems WHERE poem_id = ?';
    try {
      $stmt = $db->prepare($q);
      $stmt->execute([$poemId]);
      $row = $stmt->fetch();
    } catch (PDOException $e) {
      logError($e->getMessage());
      return false;
    }

    return($row['user_id'] === $userId);
  }
---- C O D E   O M I T T E D ----
  1. Open a new document and save it as poem-edit.php in the InsertUpdateDelete/Exercises/phppoetry.com folder.
  2. Write code to create a page that looks like the one below:phppoetry.com Edit poem
  3. The author should be able to update the title, category, and text of the poem.
  4. When the poem is updated, the date_approved field in the poems table should be set to null.
  5. When the user presses the Update Poem button, the page should submit to itself. The changes should take effect and there should be a message letting the user know the poem has been updated and will be reviewed:phppoetry.com Edit poem - submitted The user could make additional updates and resubmit.
  6. Be sure to write code to catch and log potential exceptions.
  7. Note that only the author of the poem should be able to reach this page. Any other user should be immediately redirected to the home page.
  8. If poem-id is not passed in on the URL then the user should be immediately redirected to the home page.
  9. Modify poem.php so that the Edit and Delete links show up only when the logged-in user is the author of the poem. Use isPoemAuthor($poemId) to check this. You will also need to modify those links to point to poem-edit.php and poem-delete.php, respectively, and to pass in the poem id.
  10. Test your solution in a browser by navigating to http://localhost:8888/Webucator/php/InsertUpdateDelete/Exercises/phppoetry.com/poems.php, clicking on a poem you wrote, clicking the Edit button, making changes, and submitting the form.

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/poem-edit.php
<?php
  $pageTitle = 'Edit Poem';
  require 'includes/header.php';

  if (!isset( $_REQUEST['poem-id'] )) {
    header("Location: index.php");
  }

  $poemId = $_REQUEST['poem-id'];
  if (!isPoemAuthor($poemId)) {
    header("Location: index.php");
  }

  $errors = [];
  $f['category'] = $_POST['cat'] ?? 0;
  $f['title'] = trim($_POST['title'] ?? '');
  $f['poem'] = trim($_POST['poem'] ?? '');

  if (!empty($_POST['update'])) {

    // Validate Form Entries
    if (!$f['title']) {
      $errors[] = 'You must enter a poem title.';
    }
    if (!$f['poem'] || strlen($f['poem']) < 10) {
      $errors[] = 'Your poem must be at least 10 characters.';
    }

    if (!$errors) {
      $dateApproved = 'null'; // For now

      // Update poem
      $qUpdate = "UPDATE poems
        SET title = :title,
          poem = :poem,
          category_id = :category_id,
          date_approved = $dateApproved
        WHERE poem_id = :poem_id";

      try {
        $stmtUpdate = $db->prepare($qUpdate);
        $stmtUpdate->bindParam(':title', $f['title']);
        $stmtUpdate->bindParam(':poem', $f['poem']);
        $stmtUpdate->bindParam(':category_id', $f['category']);
        $stmtUpdate->bindParam(':poem_id', $poemId);
        $poemUpdated = $stmtUpdate->execute();
      } catch (PDOException $e) {
        $errors[] = 'Update failed. Please try again.';
        logError($e->getMessage());
      }
    }
  }

  $qPoem = "SELECT p.poem_id, p.title, p.poem, 
    p.date_submitted, p.date_approved,
    c.category_id, u.username
    FROM poems p
      JOIN users u ON u.user_id = p.user_id
      JOIN categories c ON c.category_id = p.category_id
    WHERE p.poem_id = ?"; // Pass SQL Param

  $qCategories = "SELECT c.category_id, c.category
    FROM categories c
    LEFT JOIN poems p ON c.category_id = p.category_id
    GROUP BY c.category_id
    ORDER BY c.category";

  try {
    $stmtPoem = $db->prepare($qPoem);
    $stmtPoem->execute([$poemId]);
    $row = $stmtPoem->fetch();
    $title = $row['title'];
    $poemCatId = $row['category_id'];
    $poem = $row['poem'];
    $authorUserName = $row['username'];
    $dateSubmitted = $row['date_submitted'];
    $dateApproved = $row['date_approved'];
    $approvedChecked = $dateApproved ? ' checked' : '';
  } catch (PDOException $e) {
    logError($e->getMessage(), true);
  }

  try {
    $stmtCategories = $db->prepare($qCategories);
    $stmtCategories->execute();
  } catch (PDOException $e) {
    logError($e->getMessage(), true);
  }
?>
<main id="poem-edit">
  <h1>
    <?= $pageTitle ?>: 
    <a href="poem.php?poem-id=<?= $poemId ?>"><?= $title ?></a>
  </h1>
  <div id="submission-status">
    Submitted on <?= date('m/d/Y', strtotime($dateSubmitted)) ?>
    at <?= date('g:iA', strtotime($dateSubmitted)) ?>
    by <?= $authorUserName ?>
    <?php
      if (isPoemAuthor($poemId)) {
        echo "<a href='poem-delete.php?poem-id=$poemId'>Delete</a>";
      }
    ?>
  </div>
  <div id="approval-status">
  <?php
    if (isPoemAuthor($poemId)) {
      if ($dateApproved) {
        echo 'Approved: ' . date('m/d/Y', strtotime($dateApproved));
      } else {
        echo 'Pending Approval';
      }
    }
  ?>
  </div>
  <?php

    if (!empty($poemUpdated)) {
      echo '<p class="success">Poem updated.</p>';
      echo '<p>We will review your updated poem soon.</p>';
    }

    if ($errors) {
      echo '<h3>Please correct the following errors:</h3>
      <ol class="error">';
      foreach ($errors as $error) {
        echo "<li>$error</li>";
      }
      echo '</ol>';
    }
  ?>
  <form method="post" action="poem-edit.php" novalidate>
    <input type="hidden" name="poem-id" value="<?= $poemId?>">
    <label for="title">Title*:</label>
    <input name="title" id="title"
      value="<?= $title ?>" required>
    <label for="cat">Category:</label>
    <select name="cat" id="cat">
      <?php
        while ($row = $stmtCategories->fetch()) {
          $category = $row['category'];
          $rowCatId = $row['category_id'];
          $selected = $poemCatId === $rowCatId ? ' selected' : '';
          echo "<option value='$rowCatId'$selected>
            $category
          </option>";
        }
      ?>
    </select>
    <label for="poem">Poem*:</label>
    <textarea id="poem" name="poem"><?= $poem ?></textarea>
    <button name="update" value="1" class="wide">Update Poem</button>
  </form>
</main>
<?php
  require 'includes/footer.php';
?>

Deleting a Poem

Duration: 20 to 30 minutes.

In this exercise, you will write a PHP page from scratch for deleting an existing poem.

  1. Open a new document and save it as poem-delete.php in the InsertUpdateDelete/Exercises/phppoetry.com folder.
  2. Write code to create a page that provides a Confirm Delete button when first visited:phppoetry.com Delete Poem
  3. When the Confirm Delete button is clicked, the poem should get deleted and a success message should appear:phppoetry.com Delete Poem ConfirmedYou can use the POEM_DELETE_SUCCESS constant for the success message.
  4. Be sure to write code to catch and log potential exceptions.
  5. Note that only the author of the poem should be able to reach this page. Any other user should get an access denied.
  6. Test your solution in a browser by navigating to http://localhost:8888/Webucator/php/InsertUpdateDelete/Exercises/phppoetry.com/poems.php, clicking on a poem you wrote, clicking the Delete button and then the Confirm Delete button. Does the poem get deleted?

Solution:

InsertUpdateDelete/Solutions/phppoetry.com/poem-delete.php
<?php
  $pageTitle = 'Delete Poem';
  require 'includes/header.php';

  if (!isset( $_REQUEST['poem-id'] )) {
    header("Location: index.php");
  }

  $poemId = $_REQUEST['poem-id'];
  if (!isPoemAuthor($poemId)) {
    header("Location: index.php");
  }

  $confirmDelete = isset($_POST['poem-id']);
  $errors = [];

  if ($confirmDelete) {
    $qDelete = 'DELETE FROM poems WHERE poem_id = ?';
    try {
      $stmt = $db->prepare($qDelete);
      if (!$stmt->execute( [$poemId] )) {
        $errorMsg = $stmt->errorInfo()[2];
        logError($errorMsg , true); 
      }
      $deleteResult = 1;
    } catch (PDOException $e) {
      logError($e->getMessage());
      $deleteResult = 0;
    }
  } else {
    $qSelect = 'SELECT title FROM poems WHERE poem_id = ?';
    try {
      $stmt = $db->prepare($qSelect);
      if (!$stmt->execute( [$poemId] )) {
        $errorMsg = $stmtPoemCount->errorInfo()[2];
        logError($errorMsg , true ); 
      } else {
        $row = $stmt->fetch();
        $poemTitle = $row['title'];
      }
    } catch (PDOException $e) {
      logError( $e->getMessage(), true);
    }
  }
?>
<main id="poem-delete" class="narrow">
  <h1><?= $pageTitle ?></h1>

  <?php

    if (!empty($deleteResult)) {
      $deleteResultMsg = nl2br(POEM_DELETE_SUCCESS);
      $cls = 'success';
    } elseif (isset($deleteResult)) {
      logError("Failed to delete poem id $poemId using: $qDelete");
      $deleteResultMsg = nl2br(POEM_DELETE_FAIL);
      $cls = 'error';
    }

    if (isset( $deleteResultMsg )) {
      // Output delete result
  ?>
    <article class="poem <?= $cls ?>">
      <?= $deleteResultMsg ?>
    </article>
  <?php
    } else {
      // Output delete form
  ?>
    <form method="post" action="poem-delete.php">
      <p>Are you sure you want to delete <em><?= $poemTitle ?></em>?</p>
      <button name="poem-id" value="<?=$poemId?>" class="wide">
        Confirm Delete
      </button>
    </form>
  <?php
    }
  ?>
</main>
<?php
  require 'includes/footer.php';
?>