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

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)) {

            } else {
            
            }

• Add a message in this code block.

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

• The first message will be shown when the query executed successfully.
• The second message will be shown if it wasn't.

• 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 because we will need to filter out already existing appearances.
• 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) {

            }

• And we can add them to an existing array using the array_push function.

                        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);
                    $rslt->execute($margs);
                    if (!$row = $rslt->fetch()) {
                        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 (!$row = $rslt->fetch())) then we add it to the list of arguments.
• What if it's a new movie? We don't want to add the word 'new' as the movie title. We need to change it to $_POST['new_movie'] instead.
• 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 $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 , .

            $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 ('alias', '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.
• If the query and arguments array look good, it's time to send them to the database.

                $rslt  = $pdo->prepare($query);
                if ($rslt->execute($args)) {
                    $message = "$movies appearances were inserted successfully.";
                } else {
                    $message = "There was a problem inserting $movies appearances.";
                }

• This code also includes the check to see if the query was successful and adds an appropriate message in either case.
• Save and upload your code, then open 'form.php' and submit some new appearances to check that everything works.
• The one problem we still have is that if the user only adds movies that are already in the database, we're creating a query and trying to submit it with no values.
• Let's fix that by stopping if there are no new movies to add.

            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.";
            }

• Rather than referring to $movies, which will be 0 in this case, I've changed the message to refer to the original number of movies submitted and stating that they were all already in the database.
• This has been a big activity, but there's more to do in the next activity.

Next: UPDATE Queries