Tech School

User Tools

Site Tools

Trace:
en:web_development:forms:insert

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
en:web_development:forms:insert [2022/01/19 16:06]
mag created 		en:web_development:forms:insert [2023/08/16 09:33] (current)
Line 31: Line 31:
                 <legend>Add Hero</legend>                 <legend>Add Hero</legend>
                 <ul>                 <ul>
 +                    <li>
 +                        <label>Hero Alias:</label>
 +                        <input type="text" name="alias" size="30" maxlength="50" hint="Iron Man">
 +                    </li>
                     <li>                     <li>
                         <label>Hero Name:</label>                         <label>Hero Name:</label>
-                        <input type="text" name="name" size="30" maxlength="50">+                        <input type="text" name="identity" size="30" maxlength="50" hint="Tony Stark">
                     </li>                     </li>
                     <li>                     <li>
Line 72: Line 76:
     while($row = $hrslt->fetch()) {     while($row = $hrslt->fetch()) {
 ?> ?>
-                            <option value="<?php echo $row['character_name']; ?>"><?php echo $row['character_name']; ?></option>+                            <option value="<?php echo $row['alias']; ?>"><?php echo $row['alias']; ?></option>
 <?php <?php
     }     }
Line 105: Line 109:
     include('header.php');     include('header.php');
     include('menu.php');     include('menu.php');
- 
-    print_r($_GET); 
 ?> ?>
     <main>     <main>
Line 122: Line 124:
   * 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.   * 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.
  
-^character_name^alias^hero_villain^first_appeared^power^ +^alias^identity^hero_villain^first_appeared^power^ 
-|Thor|Thor|1962|Magic| +|Thor|Thor Odinson|1962|Magic| 
-|Johan Schmidt|Red Skull|1941|Biological| +|Red Skull|Johan Schmidt|1941|Biological| 
-|Bucky Barnes|Winter Soldier|1941|Tech|+|Winter SolBucky Barnes|Winter Soldier|1941|Tech|
 |Clint Barton|Hawkeye|1969|Skill| |Clint Barton|Hawkeye|1969|Skill|
 |Odin|Odin|1962|Magic| |Odin|Odin|1962|Magic|
Line 134: Line 136:
 |Pepper Potts|Rescue|1963|Tech| |Pepper Potts|Rescue|1963|Tech|
  
 +  * 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.
 +<code>INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES ('Bucky Barnes', 'Winter Soldier', 'Villain', '1941', 'Tech'); </code>
 +  * 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.
 +
 +==== Switch Statement ====
 +  * 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.
 +<code>    // check which form
 +    if ($_POST['submit'] == 'Add Hero') {
 +
 +    } else if ($_POST['submit'] == 'Add Appearance') {
 +
 +    } else {
 +        
 +    }</code>
 +  * 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.
 +<code>    switch ($variable) {
 +        case 'value':
 +            # code...
 +            break;
 +        
 +        default:
 +            # code...
 +            break;
 +    }</code>
 +  * 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 <html>break</html> command.
 +  * If it doesn't find a match, then it runs the code in <html>default:</html>.
 +  * This means that if don't include <html>break</html> at the end of a <html>case</html>, the code from the next <html>case</html> 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)
 +<code>switch ($age) {
 +    case 1:
 +    case 17:
 +    case 43:
 +        # code...
 +        break;
 +    case 22:
 +    case 39:
 +        # code...
 +        break;
 +}</code>
 +  * We want to run a different query depending on the value of $_POST['submit'], so replace '$variable' with <html>$_POST['submit']</html>.
 +  * Now set up a case each for 'Add Hero' and 'Add Appearance'.
 +<code>    switch ($_POST['submit']) {
 +        case 'Add Hero':
 +            break;
 +        case 'Add Appearance':
 +            break;
 +        default:
 +            break;
 +    }</code>
 +  * Insert our code that we use for running database queries after <html>case 'Add Hero':</html>.
 +<code>            $query = "";
 +            $args  = array();
 +            $rslt  = $pdo->prepare($query);
 +            $rslt->execute($args)</code>
 +  * 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 <html> ? </html> then add the respective user input as elements in the '$args' array.
 +<code>            $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']);</code>
 +  * 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 <html>$rslt->execute($args)</html> to the following.
 +<code>            if ($rslt->execute($args)) {
 +
 +            } else {
 +            
 +            }</code>
 +  * Add a message in this code block.
 +<code>            if ($rslt->execute($args)) {
 +                $message = "{$_POST['name']} was inserted successfully.";
 +            } else {
 +                $message = "There was a problem inserting {$_POST['name']}.";
 +            }</code>
 +  * The first message will be shown when the query executed successfully.
 +  * The second message will be shown if it wasn't.
 +
 +==== Multiple Inserts ====
 +  * 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.
 +<code>INSERT INTO appearances (character_name, movie) VALUES ('Thor Odinson', 'Thor: Dark World'), ('Thor Odinson', 'Avengers: Age of Ultron')</code>
 +
 +==== Array Push ====
 +  * 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 <html>foreach</html> loop.
 +<code>            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);</code>
 +  * 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 <html>foreach</html>.
 +<code>            $args = array();
 +            foreach($_POST['movie'] as $movie) {
 +                        array_push($args, $_POST['hero'], $movie);
 +            }</code>
 +  * But we don't want to add movies that are already listed in the database, so we should check whether they exist first.
 +<code>                    // 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);
 +                    }</code>
 +  * 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 (<html>if (!$row = $rslt->fetch())</html>) 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 <html>$_POST['new_movie']</html> instead.
 +  * Let's check using an 'if statement'.
 +<code>                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);
 +                    }
 +                }</code>
 +  * If it's new, then we use <html>$_POST['new_movie']</html> as the movie name instead of the value stored in <html>$_POST['movie']</html>.
 +
 +==== Count ====
 +  * 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 <html>(?,?)</html> at the end of the query.
 +  * We can get the number of elements in the <html>$args</html> array using <html>count</html> and then halve it.
 +  * <html>count</html> is like <html>.length</html> in Javascript, counting the number of elements in an array.
 +  * Add the following code after the <html>foreach</html> loop.
 +<code>            $movies = count($args)/2;</code>
  
 +==== Repeating String ====
 +  * We then need to add exactly that many copies of <html>(?,?)</html> at the end of the query, but they need to be separated by a <html> , </html>.
 +<code>            $values = str_repeat('(?,?), ', $movies-1) . '(?,?)';</code>
 +  * This technique uses a function called <html>str_repeat</html> to repeat a set string a number of times. If we repeated '(?,?),' the correct number of times, we'd have a hanging <html> , </html> at the end which would cause an error. Instead, we add one less  than the required amount (<html>count($movies) -1</html>), then add <html>(?,?)</html> at the end.
 +  * Now, our query is:
 +<code>            $query = "INSERT INTO heroes ('alias', 'movie') VALUES $values";</code>
 +  * Before we run our query, we should check that code works, that both query and arguments look as they should.
 +  * Add the following before <html>break</html>.
 +<code>            error_log("MURRAY: $query\n" . print_r($args, 1);</code>
 +  * 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.
 +<code>
 +                $rslt  = $pdo->prepare($query);
 +                if ($rslt->execute($args)) {
 +                    $message = "$movies appearances were inserted successfully.";
 +                } else {
 +                    $message = "There was a problem inserting $movies appearances.";
 +                }</code>
 +  * 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.
 +<code>            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.";
 +            }</code>
 +  * Rather than referring to <html>$movies</html>, 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.
  
 +[[en:web_development:forms:update|Next: UPDATE Queries]]
en/web_development/forms/insert.1642637198.txt.gz · Last modified: 2023/08/16 09:33 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki