Tech School

User Tools

Site Tools

Trace: • insert
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/21 14:05]
mag 		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 196: Line 200:
   * 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.   * 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.   * Replace each value with <html> ? </html> then add the respective user input as elements in the '$args' array.
-<code>            $query = "INSERT INTO `heroes` (`character_name`, `alias`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)"; +<code>            $query = "INSERT INTO `heroes` (`alias`, `identity`, `hero_villain`, `first_appeared`, `power`) VALUES (?,?,?,?,?)"; 
-            $args  = array($_POST['name'], $_POST['alias'], $_POST['side'], $_POST['year'], $_POST['power']);</code>+            $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.   * 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.   * Since we want to know if there's a problem, and to tell the user, let's add a check.
Line 203: Line 207:
 <code>            if ($rslt->execute($args)) { <code>            if ($rslt->execute($args)) {
  
 +            } else {
 +            
             }</code>             }</code>
   * Add a message in this code block.   * Add a message in this code block.
 <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>             }</code>
 +  * The first message will be shown when the query executed successfully.
 +  * The second message will be shown if it wasn't.
  
 ==== Multiple Inserts ==== ==== Multiple Inserts ====
Line 215: Line 225:
  
 ==== Array Push ==== ==== Array Push ====
-  * Let's leave the query for last.+  * 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.   * 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.   * 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>            foreach($_POST['movie'] as $movie) {
  
-            }<code>+            }</code>
   * And we can add them to an existing array using the <html>array_push</html> function.   * And we can add them to an existing array using the <html>array_push</html> function.
 <code>                        array_push($args, $_POST['hero'], $movie);</code> <code>                        array_push($args, $_POST['hero'], $movie);</code>
Line 234: Line 244:
                     $margs  = array($_POST['hero'], $movie);                     $margs  = array($_POST['hero'], $movie);
                     $rslt  = $pdo->prepare($mquery);                     $rslt  = $pdo->prepare($mquery);
-                    if (!$rslt->execute($margs)) {+                    $rslt->execute($margs); 
 +                    if (!$row = $rslt->fetch()) {
                         array_push($args, $_POST['hero'], $movie);                         array_push($args, $_POST['hero'], $movie);
                     }</code>                     }</code>
Line 240: Line 251:
   * $mquery is the query asking whether the hero / movie pair already exists.   * $mquery is the query asking whether the hero / movie pair already exists.
   * $margs is the array of parameters for the query.   * $margs is the array of parameters for the query.
-  * We then run the query and if it doesn't exist (<html>if (!$rslt->execute($margs))</html>) then we add it to the list of arguments. +  * 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?+  * 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'.   * Let's check using an 'if statement'.
 <code>                if ($movie == 'new') { <code>                if ($movie == 'new') {
Line 258: Line 269:
 ==== Count ==== ==== Count ====
   * We could have created the query first and added pieces in the loop, but there is a more elegant way.   * 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>(?,?) at the end of the query.+  * 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.   * 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.   * <html>count</html> is like <html>.length</html> in Javascript, counting the number of elements in an array.
Line 265: Line 276:
  
 ==== Repeating String ==== ==== Repeating String ====
-  * We then need to add exactly that many copies of <html>(?,?) at the end of the query, but they need to be separated by a <html> , </html>.+  * 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> <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.   * 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:   * Now, our query is:
-<code>            $query = "INSERT INTO heroes ('character_name', 'movie') VALUES $values";</code>+<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.   * 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>.   * Add the following before <html>break</html>.
Line 276: Line 287:
   * 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'.   * 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.   * 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.1642802725.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