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

Next revision 		Previous revision
en:web_development:forms:insert [2022/01/19 14:42]
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 120: 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 Odinson|Thor|Hero|1962|Magic| +|Thor|Thor Odinson|1962|Magic| 
-|Johan Schmidt|Red Skull|Villain|1941|Biological| +|Red Skull|Johan Schmidt|1941|Biological| 
-|Bucky Barnes|Winter Soldier|Villain|1941|Tech| +|Winter SolBucky Barnes|Winter Soldier|1941|Tech| 
-|Clint Barton|Hawkeye|Hero|1969|Skill| +|Clint Barton|Hawkeye|1969|Skill| 
-|Odin|Odin|Hero|1962|Magic| +|Odin|Odin|1962|Magic| 
-|Nick Fury|Nick Fury|Hero|1963|Skill| +|Nick Fury|Nick Fury|1963|Skill| 
-|Phil Coulson|Agent Coulson|Hero|2008|Skill| +|Phil Coulson|Agent Coulson|2008|Skill| 
-|Peter Quill|Starlord|Hero|1976|Tech| +|Peter Quill|Starlord|1976|Tech| 
-|Gamora|Gamora|Hero|1975|Skill +|Gamora|Gamora|1975|Skill 
-|Pepper Potts|Rescue|Hero|1963|Tech|+|Pepper Potts|Rescue|1963|Tech|
  
-  * Enter the data for the chosen character and click 'Go' to submit. +  * Enter the information for your hero into the appropriate fields in the 'Value' column and press 'Go'. 
-  * Notice at the top of the screen just below the yellow bar it shows the query used to add the data. +  * See the query used to add the data just below the yellow row at the top of the page. 
-  * It will look something like this. +<code>INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES ('Bucky Barnes', 'Winter Soldier', 'Villain', '1941', 'Tech'); </code> 
-<code>INSERT INTO `heroes` (`character_name`, `alias`, `hero_villain`, `first_appeared`, `power`) VALUES ('Thor', 'Thor', 'Hero', '1962', 'Magic'); </code> +  * This is the format we need to use for our insert query in 'process_form.php'. 
-  * This is the query format we need to use in our page to add new information. +  * 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. 
-  * It starts with <html>INSERT INTO</html> then the name of the table. +  * This is followed by 'VALUES' and a list of the values to be inserted.
-  * Next, inside <html> () </html> we add the list of fields that we have data for. +
-  * Then <html>VALUES ()</html> with the data inside the <html> () </html>. +
-  * Actually, we'll change this a little. Hackers will sometimes use [[https://www.w3schools.com/sql/sql_injection.asp|SQL Injection]] to get information from, or change information in, your database. To avoid this, we replace the data with <html> ? </html> and put the actual values in an array to be sent separate to the query. +
-  * The query then looks like this. +
-<code>INSERT INTO `heroes` (`character_name`, `alias`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)</code> +
-  * With an arguments array like this. +
-<code>('Thor', 'Thor', 'Hero', 1962, 'Magic')</code>+
  
 ==== Switch Statement ==== ==== Switch Statement ====
-  * Return to Visual Studio Code. +  * Remember that we actually have two forms sent to this page by two submit buttons. 
-  * Open 'process_form.php' and switch it to PHP mode (bottom right corner). +  * We can identify which form to process by the value of '$_POST['submit']'. 
-  * First we must determine which form to process. We can tell by checking the value of $_POST['submit'], which will be either 'Add Hero' or 'Add Appearance' depending on which form has been submitted. +  * We could do this with an 'if' statement, like this.
-  * You know how to check each one using <html> if </html>, but there's another more powerful option you can use when you are checking for different values of the same variable. +
-  * At the end of the first PHP block, type switch and select 'switch block' (the second option). +
-  * You'll see this.+
 <code>    // check which form <code>    // check which form
-    switch ($variable) {+    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':         case 'value':
             # code...             # code...
Line 163: Line 168:
             break;             break;
     }</code>     }</code>
-  * <html>switch</html> allows us to check the value of a particular variable (in this case $_POST['submit']) and run different code based on the result. +  * 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. 
-  * The expected results are listed in separate <html>case</html> values. +  * If it doesn't find a match, then it runs the code in <html>default:</html>. 
-  * <html>default:</html> will run if the result is something unexpected / undefined. +  * 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) 
-  * Also note <html>break;</html> at the end of each <html>case</html> code block. If you leave this out, the code in the next case will keep running until eventually it hits <html>break;</html> or the end of the <html>switch</html> statement. This can be useful if you want the same code to be run in multiple options. +<code>switch ($age) { 
-  * Replace '$variable' with '$_POST['submit']'. +    case 1: 
-  * Replace 'value' with 'Add Hero'. +    case 17: 
-  * Add another <html>case</html> block for 'Add Appearance'. +    case 43: 
-  * Delete '# code...' from each block. +        # code... 
-  * The code should look like this so far. +        break; 
-<code>    // check which form +    case 22: 
-    switch ($_POST['submit']) {+    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':         case 'Add Hero':
             break;             break;
-        case 'Add Hero':+        case 'Add Appearance':
             break;             break;
         default:         default:
             break;             break;
     }</code>     }</code>
-     +  * Insert our code that we use for running database queries after <html>case 'Add Hero':</html>. 
-==== PDO Query ====+<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)) {
  
-  * Let's start with the code to add a hero. Add a new line after <html>case 'Add Hero':</html>. +            } else { 
-  * Add the query and arguments. +             
-<code>            $query = "INSERT INTO `heroes` (`character_name`, `alias`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)"; +            }</code> 
-                        $args  = array($_POST['name'], $_POST['alias'], $_POST['side'], $_POST['year'], $_POST['power']); +  * Add a message in this code block.
-</code> +
-  * Now add the code to execute our query. +
-<code>            $rslt = $pdo->prepare($query); +
-            $rslt->execute($args);</code> +
-  * Because we want to know whether it worked, let's update the last line to return a status message.+
 <code>            if ($rslt->execute($args)) { <code>            if ($rslt->execute($args)) {
                 $message = "{$_POST['name']} was inserted successfully.";                 $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>             }</code>
-  * Note that <html>$_POST['name']</html> is wrapped in <html> {} </html>. This is to tell PHP that we want the value printed, not the text as is. Try removing <html>{}</html> to see what the message says without them. +  * 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. 
-  * Then we can display the message in <html><main></html>. +  * This has been a big activity, but there's more to do in the next activity.
-<code>    <main> +
-        <p><?php echo $message; ?></p> +
-    </main></code> +
-   +
-==== New Movie ====+
  
 +[[en:web_development:forms:update|Next: UPDATE Queries]]
en/web_development/forms/insert.1642632121.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