Let's Get Started with an Advanced PHP/MySQL Form Page!

  1. Open Notepad++
  2. Choose: File -> new
  3. Copy the PHP Form Template (PHP) code (below)
  4. Paste that code into your new file
  5. After the // Create the SELECT query line add:
    $query = "SELECT dish_id, dish_name, price, is_spicy ";
    $query .= "FROM dishes ";
    $query .= "WHERE dish_id = $id;";
  6. After the // Create the DELETE query line add:
    $query = "DELETE ";
    $query .= "FROM dishes ";
    $query .= "WHERE dish_id = $id;";
  7. After the // Create the INSERT query line add:
    $query = "INSERT INTO dishes (dish_name, price, is_spicy) ";
    $query .= "VALUES ('$name', $price, $isSpicy);";
  8. After the // Create the UPDATE query line add:
    $query = "UPDATE dishes ";
    $query .= "SET dish_name = '$name', price = $price, is_spicy = $isSpicy ";
    $query .= "WHERE dish_id = $id;";
  9. Add the value="<?php echo($name); ?>" and value="<?php echo($price); ?>" code to the appropriate <input> tags
  10. In each <option> add the appropriate <?php if ($isSpicy == 0) { echo(" selected"); } ?> code
  11. Click: File -> Save As
  12. Name your file: /public_html/mysqlform2.php
  13. Change the Save as type: to: PHP Hypertext Preprocessor (*.php)
  14. Click: Save
  15. Visit your page (http://bscacad3.buffalostate.edu/~your-BSC-username/form.php) in your favorite web browser
  16. Make any changes
  17. Save your changes
  18. Fill in the form fields
  19. Submit your form
  20. View your mysqlform.php page to see if the addition worked
  21. Change: $debug = 0;
    to: $debug = 1;
  22. Add another record to see the debug messages

PHP Form Template (PHP)

<!doctype html>
<html>
  <head>
    <title>put-title-of-this-page-here</title>
  </head>
  <body>
  <?php 
$debug = 0;
$id = 0;
$name = "";
$price = "";
$is_spicy = 0;
$dbUsername = "root";
$dbPassword = "";
$dbDatabase = "wnylrc";
$dbServer   = "localhost";
// Create a new server connection;
$dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
if (isset($_GET['action'])) {
  $action = $_GET['action'];
  $id = $_GET['id'];
  if ($debug) { echo("action=$action id=$id<br/>"); }
  if ($action == "mod") {
    // Create the SELECT query            
    // add the SELECT query code here
    if ($debug) { echo("$query<br/>\n"); }
    // Send the query to MySQL
    $result = $dbConn->query($query,MYSQLI_STORE_RESULT);
    list($id, $name, $price, $isSpicy) = $result->fetch_row();
    if ($debug) { echo("id=$id name=$name price=$price isSpicy = $isSpicy<br/>\n"); }
  } elseif ($action == "del") {
    // Create the DELETE query            
    // add the DELETE query code here
    if ($debug) { echo("$query<br/>\n"); }
    // Send the query to MySQL
    $result = $dbConn->query($query,MYSQLI_STORE_RESULT);
    echo("Record $id deleted!<br/>\n");
  }
}
if (isset($_POST['submit'])) {
  if ($debug) {
    foreach ($_POST as $postElement => $postValue) {
      echo("\$_POST[$postElement] = $postValue<br/>\n");
    }
  }
  $name    = $_POST['Name'];
  $price   = $_POST['Price'];
  $isSpicy = $_POST['isSpicy'];
  // Create the UPDATE query
  if (isset($_GET['action'])) {
    $action = $_GET['action'];
    $id = $_GET['id'];
    if ($action == "mod") {
      // Create the UPDATE query            
    // add the UPDATE query code here
      if ($debug) { echo("$query<br/>\n"); }
      // Send the query to MySQL
      $result = $dbConn->query($query,MYSQLI_STORE_RESULT);
      echo("$name ($price) record modified!<br/>\n");
    } elseif ($action == "add") {
      // Create the INSERT query            
    // add the INSERT query code here
      if ($debug) { echo("$query<br/>\n"); }
      // Send the query to MySQL
      $result = $dbConn->query($query,MYSQLI_STORE_RESULT);
      echo("$name ($price) record inserted!<br/>\n");
    } 
  }
  $dbConn->close();
} 
?>
  <h1>My Dish Items Advanced Maintenance</h1>
  <form id="dishForm" method="POST" 
    action="mysqlform2.php?action=<?php echo($action); ?>&id=<?php echo($id); ?>>">
    <fieldset> 
    <legend>Dish Information</legend> 
    <label for="Name">Name:</label> 
    <!-- add appropriate value="" here -->
    <input id="Name" name="Name" type="text" /><br class="clear"/>
    <label for="Price">Price:</label> 
    <!-- add appropriate value="" here -->
    <input id="Price" name="Price" type="text" /><br class="clear"/>
    <label for="isSpicy">Is Spicy?</label>
    <select id="isSpicy" name="isSpicy">
    <!-- add appropriate if test here -->
      <option value="1">Yes</option>
    <!-- add appropriate if test here -->
      <option value="0">No</option>
    </select><br class="clear"/>
    </fieldset> 
    <label for="submit"></label>
    <button id="submit" name="submit" type="submit" value="Do it!">Do it!</button>
  </form>
  </body>
</html>

Output