Page 194 - PHP Novice to Ninja

PHP
1

In page 194, How code could I use to delete an author set author id to NULL?

“When we delete an author, we set the authorid of any jokes attributed to that
author to NULL to indicate that they have no author”

Thank you

2

Just a wild guess, since I don’t have the book, but

$authorid = '4';
$id = null;
$sql = "UPDATE jokes 
        SET authorid=?
        WHERE authorid=? ";
$q = $conn->prepare($sql);
$q->execute(array($authorid,$id));

Or something to that effect.

Scott

3

Thank you, these my files:

Controller index.php

 // Delete jokes belonging to author
  try
  {
    $sql = 'DELETE FROM joke WHERE authorid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error deleting jokes for author.';
    include 'error.html.php';
    exit();
  }

  // Delete the author
  try
  {
    $sql = 'DELETE FROM author WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $error = 'Error deleting author.';
    include 'error.html.php';
    exit();
  }

  header('Location: .');
  exit();
}

// Display author list
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try
{
  $result = $pdo->query('SELECT id, name FROM author');
}
catch (PDOException $e)
{
  $error = 'Error fetching authors from the database!';
  include 'error.html.php';
  exit();
}

foreach ($result as $row)
{
  $authors[] = array('id' => $row['id'], 'name' => $row['name']);
}

include 'authors.html.php';

authors.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .
    '/includes/helpers.inc.php'; ?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Manage Authors</title>
  </head>
  <body>
    <h1>Manage Authors</h1>
    <p><a href="?add">Add new author</a></p>
    <ul>
      <?php foreach ($authors as $author): ?>
        <li>
          <form action="" method="post">
            <div>
              <?php htmlout($author['name']); ?>
              <input type="hidden" name="id" value="<?php
                  echo $author['id']; ?>">
              <input type="submit" name="action" value="Edit">
              <input type="submit" name="action" value="Delete">
            </div>
          </form>
        </li>
      <?php endforeach; ?>
    </ul>
    <p><a href="..">Return to JMS home</a></p>
  </body>
</html>

In this case when I delete an author I also delete any jokes attributed to that. I would rather delete only the author, a book solution for this is set authorid to NULL.

4

So instead of this

 $sql = 'DELETE FROM joke WHERE authorid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();

you’d use Scott’s code above, though I think the parameters in the execute call are transposed. So to use bindValue you’d use something like

 $sql = 'UPDATE jokes SET authorid = :newid WHERE authorid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':newid', null);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
1 Like
5

Ok thank you

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.