In this activity you'll learn how to obtain data from a form and insert it into a database table.

• We'll continue using 'form.php' and 'process_form.php'. If you need the code again, here it is.

form.php

<?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>Hero Name:</label>
                        <input type="text" name="name" size="30" maxlength="50">
                    </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');
?>

process_form.php

<?php
    include('header.php');
    include('menu.php');
?>
    <main>
        <p><?php print_r($_POST); ?></p>
    </main>
<?php
    include('footer.php');
?>

• To see how this works, we'll start in PHPMyAdmin.
• Log in and open the table 'heroes' in the database 'webdev'.
• Open the 'Insert' tab.
• Notice that each of the fields in our table is represented by a row.
• Pick a hero / villain from the following list and add it to the table. If you're working in a class, each pick a different character.

• Enter the information for your hero into the appropriate fields in the 'Value' column and press 'Go'.
• See the query used to add the data just below the yellow row at the top of the page.

INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES ('Bucky Barnes', 'Winter Soldier', 'Villain', '1941', 'Tech'); 

• This is the format we need to use for our insert query in 'process_form.php'.
• An INSERT query always begins with 'INSERT INTO' and the name of the table we will use, then a list of the fields we want to add to.
• This is followed by 'VALUES' and a list of the values to be inserted.

• Remember that we actually have two forms sent to this page by two submit buttons.
• We can identify which form to process by the value of '$_POST['submit']'.
• We could do this with an 'if' statement, like this.

    // check which form
    if ($_POST['submit'] == 'Add Hero') {

    } else if ($_POST['submit'] == 'Add Appearance') {

    } else {
        
    }

• This would work, but there is a better way.
• A 'switch statement' isn't as flexible as an 'if statement', which allows for completely new conditions on each line, but it is more powerful in its own way.
• Open 'process_form.php' in Visual Studio Code.
• At the end of the first PHP block, type 'switch' and select the second option. It will add the following code automatically.

    switch ($variable) {
        case 'value':
            # code...
            break;
        
        default:
            # code...
            break;
    }

• This statement checks the value of '$variable' and looks down the list of 'cases' until it finds a match, then runs the code until it hits a break command.
• If it doesn't find a match, then it runs the code in default:.
• This means that if don't include break at the end of a case, the code from the next case will also run. This can be useful if you want the same code to run for multiple cases like this. (Don't add this code)

switch ($age) {
    case 1:
    case 17:
    case 43:
        # code...
        break;
    case 22:
    case 39:
        # code...
        break;
}

• We want to run a different query depending on the value of $_POST['submit'], so replace '$variable' with $_POST['submit'].
• Now set up a case each for 'Add Hero' and 'Add Appearance'.

    switch ($_POST['submit']) {
        case 'Add Hero':
            break;
        case 'Add Appearance':
            break;
        default:
            break;
    }

• Insert our code that we use for running database queries after case 'Add Hero':.

            $query = "";
            $args  = array();
            $rslt  = $pdo->prepare($query);
            $rslt->execute($args)

• We can now copy our query from PHPMyAdmin to the query string.
• Because hackers can perform 'SQL Injection' to get information about your database or even to add themselves as an admin user by adding SQL code as data, we're going to strip the data out of our query.
• Replace each value with ? then add the respective user input as elements in the '$args' array.

            $query = "INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)";
            $args  = array($_POST['alias'], $_POST['identity'], $_POST['alias'], $_POST['side'], $_POST['year'], $_POST['power']);

• This code will work unless there is a problem accessing the database or invalid user input.
• Since we want to know if there's a problem, and to tell the user, let's add a check.
• Change $rslt->execute($args) to the following.

            if ($rslt->execute($args)) {

            }

• Add a message in this code block.

            if ($rslt->execute($args)) {
                $message = "{$_POST['name']} was inserted successfully.";
            }

• The Add Appearances form allows the user to add multiple movies, but each one is its own line in the database table.
• We could create a separate query for each movie, but its also possible to add multiple lines in the same query doing something like this.

INSERT INTO appearances (character_name, movie) VALUES ('Thor Odinson', 'Thor: Dark World'), ('Thor Odinson', 'Avengers: Age of Ultron')

• Let's leave the query for last.
• First let's build our array of arguments - a pair for each movie.
• Each pair of arguments is the name of our hero and the name of the movie, which we can do in a foreach loop.

            foreach($_POST['movie'] as $movie) {

            }<code>
  * And we can add them to an existing array using the <html>array_push</html> function.
<code>                        array_push($args, $_POST['hero'], $movie);

• The first parameter in this function must be an array, and any values after it are added to the end of the array.
• Of course, we need to create the array first, and we don't want it recreated every time we loop so we need to create it before foreach.

            $args = array();
            foreach($_POST['movie'] as $movie) {
                        array_push($args, $_POST['hero'], $movie);
            }

• But we don't want to add movies that are already listed in the database, so we should check whether they exist first.

                    // check if it exists already
                    $mquery = "SELECT * FROM appearances WHERE character_name=? AND movie=?";
                    $margs  = array($_POST['hero'], $movie);
                    $rslt  = $pdo->prepare($mquery);
                    if (!$rslt->execute($margs)) {
                        array_push($args, $_POST['hero'], $movie);
                    }

• Can you read this code for yourself?
• $mquery is the query asking whether the hero / movie pair already exists.
• $margs is the array of parameters for the query.
• We then run the query and if it doesn't exist (if (!$rslt->execute($margs))) then we add it to the list of arguments.
• What if it's a new movie?
• Let's check using an 'if statement'.

                if ($movie == 'new') {
                    array_push($args, $_POST['hero'], $_POST['new_movie']);
                } else {
                    // check if it exists already
                    $mquery = "SELECT * FROM appearances WHERE character_name=? AND movie=?";
                    $margs  = array($_POST['hero'], $movie);
                    $rslt  = $pdo->prepare($mquery);
                    if (!$rslt->execute($margs)) {
                        array_push($args, $_POST['hero'], $movie);
                    }
                }

• If it's new, then we use $_POST['new_movie'] as the movie name instead of the value stored in $_POST['movie'].

• We could have created the query first and added pieces in the loop, but there is a more elegant way.
• If we know how many movies have been added, we can simply add the right number of (?,?) at the end of the query. * We can get the number of elements in the <html>$args array using count and then halve it.
• count is like .length in Javascript, counting the number of elements in an array.
• Add the following code after the foreach loop.

            $movies = count($args)/2;

• We then need to add exactly that many copies of (?,?) at the end of the query, but they need to be separated by a <html> , .

            $values = str_repeat('(?,?), ', $movies-1) . '(?,?)';

• This technique uses a function called str_repeat to repeat a set string a number of times. If we repeated '(?,?),' the correct number of times, we'd have a hanging , at the end which would cause an error. Instead, we add one less than the required amount (count($movies) -1), then add (?,?) at the end.
• Now, our query is:

            $query = "INSERT INTO heroes ('character_name', 'movie') VALUES $values";

• Before we run our query, we should check that code works, that both query and arguments look as they should.
• Add the following before break.

            error_log("MURRAY: $query\n" . print_r($args, 1);

• Save and upload the code, then open 'form.php' in your browser.
• Choose a hero and select a number of movies including the new one. Enter a movie that isn't already in our list anywhere and click 'Add Appearance'.
• Now check the error logs in Putty.