====== Web Development Lesson 8 - Forms ======
===== UPDATE Queries =====
==== Objective ====
In this activity you'll take the user's input from the form and use it to update information in a database.

==== Setup ====
  * We'll continue to use 'form.php' and 'process_form.php'. Here is the code again in case 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="bio">Biological</option>
                            <option value="magic">Magic</option>
                            <option value="tech">Technology</option>
                            <option value="mutant">Mutant</option>
                        </select>
                    </li>
                </ul>
                <input type="submit" name="submit" value="Add 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="hero">
<?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 Hero':
            $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['hero'], $_POST['new_movie']);
                } else {
                    error_log($movie);
                    // check if it exists already
                    $mquery = "SELECT * FROM appearances WHERE alias=? AND movie=?";
                    $margs  = array($_POST['hero'], $movie);
                    $rslt  = $pdo->prepare($mquery);
                    $rslt->execute($margs);
                    if (!$row = $rslt->fetch()) {
                        array_push($args, $_POST['hero'], $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>

==== Updating Form ====
  * What do we do when hero data changes or we need to correct a mistake in the data?
  * We need to be able to update a particular record in the database.
  * Let's start by looking at the form. How do we make this easy for the user?
  * We could create another form that lists the actual data for a chosen hero and has its own 'submit' button to begin the process. This helps if we want to change the actual hero alias, which we're using as the key.
  * That seems like overkill in this case, though. For the moment, lets trust that the user has correct information and will enter it all again.
  * All we need to do is change the 'Add Hero' button to reflect the additional capability.
  * Update the code for the button in 'form.php'.
<code>                <input type="submit" name="submit" value="Add / Update Hero"></code>
  * Remember to update the respective code in 'process_form.php' or your code won't be processed.
<code>    // check which form
    switch ($_POST['submit']) {
        case 'Add / Update Hero':</code>

==== UPDATE Query ====
  * Now we need to find the code for updating a database record.
  * Open PHPMyAdmin, then select the 'heroes table in the 'webdev' database.
  * Now click on 'Edit' next to any hero. Choose different heroes if you're working in a class.
  * Change one element and click 'Go'. For example change from 'Hero' to 'Villain' or vice versa.
  * Look at the query at the top of the screen just below the yellow bar. It should look something like this.
<code>UPDATE `heroes` SET `hero_villain` = 'Villain', `power` = 'Technology' WHERE `heroes`.`alias` = 'Captain America'</code>
  * So the format to update a record is 'UPDATE table SET field1=new_value, field2=new_value WHERE field=existing_value'.
  * You can add as many fields as you like in 'SET'. This is where you change the values.
  * We've used the alias to identify the record, but you could have multiple conditions such as "WHERE alias='Captain America' AND identity='Steve Rogers'".
  * We'll use this format in 'process.php' to update a record.

==== To Update or Insert? ====

  * Next, we need to identify that the hero exists when we're processing the form.
  * Add the following code at the top of the 'Add Hero' case in 'process_form.php'.
<code>            // 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
            
            } else {
                
            }</code>
  * First we need to query the database for any record where the hero alias is the same as the one submitted by the user.
  * If one exists, we'll run an update query.
  * If not, we'll run an insert query.
  * For the second case, move the existing code inside the <html>else</html> block.
<code>        case 'Add 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
                
            } 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;</code>
  * Now we need to add the code for updating the hero inside the <html>if($row = $hrslt->fetch()) code block.
<code>                $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);
                $rslt->execute($args);</code>
  * So we'll update the identity, hero/villain, year first appeared and the source of power for a particular alias given by the user.
  * The arguments in $args must be in the same order as they appear in the query.
  * We then send the query to the database to perform the update.

==== Feedback to User ====
  * As with the insert queries, we need to let the user know whether it worked.
  * Move the 'execute' code inside an 'if statement' and return a relevant message in both cases.
<code>                if($rslt->execute($args)) {
                    $message = "{$_POST['alias']} was updated successfully.";
                } else {
                    $message = "There was a problem updating {$_POST['alias']}.";
                }</code>
  * Save upload and check that your code works.

[[en:web_development:forms:delete|DELETE Query]]