====== Web Development Lesson 8 - Forms ======
===== Deleting Data =====
==== Objective ====
In this activity you'll learn how to delete data from a database.

==== Setup ====
  * We'll continue to work with 'form.php' and 'process_form.php' in this activity. Here is the code again if you need it.

**form.php**
<code><?php
    include('database.php');

    include('header.php');
    include('menu.php');

    // get list of heroes
    $query = "SELECT * FROM heroes";
    $args  = array();
    $hrslt = $pdo->prepare($query);
    $hrslt->execute($args);

    // get list of movies
    $query = "SELECT movie FROM appearances GROUP BY movie";
    $args  = array();
    $mrslt = $pdo->prepare($query);
    $mrslt->execute($args);
?>
    <main>
        <form name="add_hero" action="process_form.php" method="post">
            <fieldset>
                <legend>Add Hero</legend>
                <ul>
                    <li>
                        <label>Alias:</label>
                        <input type="text" name="alias" size="30" maxlength="50" hint="Iron Man">
                    </li>
                    <li>
                        <label>Identity:</label>
                        <input type="text" name="identity" size="30" maxlength="50" hint="Tony Stark">
                    </li>
                    <li>
                        <label>Side:</label>
                        <ul>
                            <li><input type="radio" name="side" value="Hero" checked> Hero</li>
                            <li><input type="radio" name="side" value="Villain"> Villain</li>
                        </ul>
                    </li>
                    <li>
                        <label>First Appearance:</label>
                        <input type="number" name="year">
                    </li>
                    <li>
                        <label>Source of Power:</label>
                        <select name="power">
                            <option value="Skill">Skill</option>
                            <option value="Biological">Biological</option>
                            <option value="Magic">Magic</option>
                            <option value="Technology">Technology</option>
                            <option value="Mutant">Mutant</option>
                        </select>
                    </li>
                </ul>
                <input type="submit" name="submit" value="Add / Update Hero">
            </fieldset>
        </form>

        <form name="add_appearances" action="process_form.php" method="post">
            <fieldset>
                <legend>Add Appearances</legend>
                <ul>
                    <li>
                        <label>Hero</label>
                        <select name="alias">
<?php
    // display list of heroes
    while($row = $hrslt->fetch()) {
?>
                            <option value="<?php echo $row['alias']; ?>"><?php echo $row['alias']; ?></option>
<?php
    }
?>
                        </select>
                    </li>
                    <li>
                        <label>Movie</label>
                        <ul>
<?php
    // display list of movies
    while($row = $mrslt->fetch()) {
?>
                            <li><input type="checkbox" name="movie[]" value="<?php echo $row['movie']; ?>" id=""> <?php echo $row['movie']; ?></li>
<?php
    }
?>
                            <li><input type="checkbox" name="movie[]" value="new" id=""> <input type="text" name="new_movie"></li>
                        </ul>
                    </li>
                </ul>
                <input type="submit" name="submit" value="Add Appearance">
            </fieldset>
        </form>
    </main>
<?php
    include('footer.php');
?></code>

**process_form.php**
<code><?php
    include('header.php');
    include('menu.php');
    include('database.php');
    
    // check which form
    switch ($_POST['submit']) {
        case 'Add / Update Hero':
            // check whether hero already exists
            $hquery = "SELECT * FROM heroes WHERE alias=?";
            $hargs  = array($_POST['alias']);
            $hrslt  = $pdo->prepare($hquery);
            $hrslt->execute($hargs);
            if($row = $hrslt->fetch()) { // the hero exists
                $query = "UPDATE heroes SET identity=?, hero_villain=?, first_appeared=?, power=? WHERE alias=?";
                $args  = array($_POST['identity'], $_POST['side'], $_POST['year'], $_POST['power'], $_POST['alias']);
                $rslt  = $pdo->prepare($query);
                if($rslt->execute($args)) {
                    $message = "{$_POST['alias']} was updated successfully.";
                } else {
                    $message = "There was a problem updating {$_POST['alias']}.";
                }
            } else {
                $query = "INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)";
                $args  = array($_POST['alias'], $_POST['identity'], $_POST['side'], $_POST['year'], $_POST['power']);
                $rslt  = $pdo->prepare($query);
                if ($rslt->execute($args)) {
                    $message = "{$_POST['alias']} was inserted successfully.";
                } else {
                    $message = "There was a problem inserting {$_POST['alias']}.";
                }
            }
            break;
        case 'Add Appearance':
            $args = array();
            foreach($_POST['movie'] as $movie) {
                if ($movie == 'new') {
                    array_push($args, $_POST['alias'], $_POST['new_movie']);
                } else {
                    error_log($movie);
                    // check if it exists already
                    $mquery = "SELECT * FROM appearances WHERE alias=? AND movie=?";
                    $margs  = array($_POST['alias'], $movie);
                    $rslt   = $pdo->prepare($mquery);
                    $rslt->execute($margs);
                    if (!$row = $rslt->fetch()) {
                        array_push($args, $_POST['alias'], $movie);
                    }
                }
            }
            error_log(print_r($args, 1));
            $movies = count($args)/2;
            if ($movies > 0) {
                $values = str_repeat('(?,?), ', $movies-1) . '(?,?)';
                $query = "INSERT INTO appearances (alias, movie) VALUES $values";
                error_log("MURRAY: $query    " . print_r($args, 1));
                $rslt  = $pdo->prepare($query);
                if ($rslt->execute($args)) {
                    $message = "$movies appearances were inserted successfully.";
                } else {
                    $message = "There was a problem inserting $movies appearances.";
                }
            } else {
                $checked = count($_POST['movie']);
                $message = "All $checked appearances are already in the database.";
            }
            
            break;
        default:
            break;
    }
?>
    <main>
        <p><?php echo $message; ?></p>
    </main>
<?php
    include('footer.php');
?></code>

==== Prepare the Form ====
  * Let's say we want to be able to remove some movie appearances for a particular hero.
  * We can use the same form we have for appearances, but add a new button.
<code>                <input type="submit" name="submit" value="Add Appearance">
                <input type="submit" name="submit" value="Delete Appearances"></code>
  * The user will either mark the movies to add or mark the movies to delete, then click the appropriate button.
  * Then we need to add a case in 'process_form.php' to process this request.
<code>        case 'Delete Appearances':
            break;
        default:
            break;</code>
            
==== DELETE Query ====
  * Now open PHPMySQL so we can observe the format used to delete an entry.
  * Open the 'appearances' table in the 'webdev' database.
  * Select an entry and click 'Delete'.
  * This time it won't show at the top of the screen. Instead, it shows a pop-up asking for confirmation that you want to run the given query.
  * You can see the structure of the delete query in the popup. It looks something like this.
<code>DELETE FROM `appearances` WHERE `appearances`.`id` = 17 </code>
  * We only need to specify the table and the details of the specific record(s) we want deleted.
  * We don't have the 'id' in the data from the form, but we can specify the exact record using both the alias and the movie.

==== WHERE IN ====
  * As was the case when we were adding appearances, we can choose between deleting each movie individually or deleting a whole selection.
  * We could identify all the checked movies like <html>(movie='Captain America' OR movie='Iron Man')</html> or we can do it more efficiently with <html>(movie IN ('Captain America', 'Iron Man')</html>. I prefer the second option.
  * We need as many options for <html>IN</html> as we have movies, but we'll replace them with <html> ? </html> for security.
  * Add the following code in the 'Delete Appearances' case.
<code>            $movies = count($_POST['movie']);
            $values = str_repeat('?,', $movies-1) . '?';
            $query = "DELETE FROM appearances WHERE alias=? AND movie IN ($values)";</code>
  * The first line gets the number of movies.
  * The second line creates the string of arguments for <html>IN</html>.
  * The third line creates the query string.
  * Now we need to create the array of arguments.
<code>            $args  = array($_POST['alias']);
            foreach($_POST['movie'] as $movie) {
                array_push($args, $movie);
            }</code>
  * First we create the array, then we loop through the movies and add each one to the array.
  * Now we send the query to the database, with a check on whether it worked or not.
<code>            $rslt = $pdo->prepare($query);
            if ($rslt->execute($args)) {
                $message = "$movies appearances were deleted successfully.";
            } else {
                $message = "There was a problem deleting $movies appearances";
            }</code>
  * Save and upload the code and check that everything works as expected.

[[en:web_development:forms:javascript|Next: Javascript for Forms]]