Web App Reference

<form action="script.php" method="post"> </form>

• action – where the form contents will be sent to - usually a PHP script written that processes the data and does something with it.
• method – specifies the way the information will be sent - get or post.

The get Method

This is the default method that is used. It attaches the forms information onto a web address so that it is visible in the URL. For example if a form asks for a first and last name like this:

  <form name="form1" action="myscript.php" method="get">
     <p>First Name:<input type="text" name="firstName"></input></p>
     <p>Last Name:<input type="text" name="lastName"></input></p>
     <input type="submit" name="submit" value="submit">
   </form>

This URL is sent to the web browser:

https://labcatscode.com/myscript.php?firstName=Jack&lastName=Bartlett

Anyone can see the information - used with short forms and forms that do not contain sensitive or private information.  Often used with search queries.

Getting Values using the GET Method

To retrieve and store the form values in a variable name, your PHP would look like this:

<?php 
    $firstName = $_GET[‘firstName’];
    $lastName = $_GET[‘lastName’];
?> 

The Post Method

This method essentially sends the information invisibly to the script which processes it. Used when you are sending lots of information or sensitive information - especially passwords.

Every input element must have a name attribute to be sent correctly with this method. That is so the script that receives it can figure out which field the data belongs to.

Getting Values Using the POST Method

• If we have a form input called first_name that was sent via POST, would be stored as $_POST['first_name'].

In this example, your input field would look like this:

First Name:<input type=“text” name=“firstName”>

In your PHP script, you would retrieve and store the form value in a variable like this:

$firstName = $_POST[‘firstName’];

Use the new HTML5 form input types to required a specific type of value to be entered
These new types are:

• color – contains a hexadecimal field – can be displayed as color picker depending on browser
• date – date
• datetime – date and time with time zone
• datetime-local – date and time with no time zone -– can show date picker depending
• email – valid email address - depending on the browser support it can be checked for a valid email when the form is submitted
• file– a field for a valid file name along with a browse button to go and select one.
• month – can select a month and a year – can show date picker depending on browser
• number – must be a number
• password– keeps the input hidden
• range – a range of numbers with a max and a min – can be displayed as slider depending on browser
• search – behaves like regular text field
• time – can show time picker depending
• url – depending on browser can be automatically validated
• week – can select a week and a year – can show date picker depending

HTML5 Constraint Validation

The second way is adding attributes to the form inputs.

• max– sets maximum value for number or date input
• min – sets minimum value for number or date input
• required – the input filed must be filled out
• step– allowed number intervals for input field
• pattern – this allows you to make up a set pattern like two letters for a state code – DC – or a telephone number – 202-462-2752. If you want more info on this - check out the next topic.

Let's take a look at an example of using HTML5 constraints with a form. Here’s an example:

<form action="myscript.php" method="post"> 
    <p>First Name:<input type="text" name="firstName“ required></p> 
    <p>Number:<input type=“number” max=“10” min=“0” step="2"></input> 
    <p>Country code:<input type="text" name="country_code" pattern="[A-Za-z]{3}" title="Three letter country code">
    <input type=“submit” value=“Submit”> 
</form>

Using pattern

The pattern constraint takes advantage of regular expressions which are basically a sequence of characters that can form a search pattern. They are common across most programming languages, through the exact syntax can vary. Take a look at the full topic for more on this.

In order to validate an HTML form via JavaScript, there are two steps.

1. Attach the JavaScript function to the form in the <form> tag as an attribute.
2. Write the JavaScript function to check the form.

In this example form, the user is asked for their name.

• If that field is empty – the function returns false to prevent the form from being submitted.
• If they did fill out a name -the function returns true and the form will be submitted.

HTML

In a file called form1.html, the form is created.

1   <!DOCTYPE html>
2   <head>
3      <title>Form Validation Example</title>
4      <script src="form.js"></script>
5   </head>
6   <body>
7      <form name=“formQ” action="val.php" onsubmit=“return validateForm()” method="post">
8        <p>Name:<input type="text" name="fullName"></p>
9        <input type=“submit” value=“Submit”>
10     </form>
11  </body>

JavaScript

And here is the JavaScript function in a file called form.js:

1   function validateForm(){
2      //Store the value that is in the input element with an id value of fullName 
3      var x = document.forms[“formQ”].elements[“fullName”].value;
4      if (x == null || x == “”){
5         alert(“Name must be filled out”);
6         return false;
7      }
8      return true;
9   }

Explanation

1. Line 7 of the HTML: the JavaScript function validateForm() is called when the JavaScript event onsubmit happens.
2. Line 2 of the JavaScript: this uses the DOM to access a given element in a given form. In this case, we are access the form element that is identified with formQ and then it finds the element in that form that has the id of fullName.
3. Here's a quick review if you need it.
4. Also, of note in the ongoing flexibility/confusion of JavaScript -there are many different ways to access the form element. The method above is the most acceptable. But you could also use:

var x = document.forms["FormQuiz"].["firstName"].value;	
var x = document.forms[0].elements[0].value;
var x = document.getElementById('fullName').value;

If you need to verify with an outside source (database) - you will need to use PHP. And if you are using PHP anyway...sometimes it is just smarter to do the whole thing with PHP.
The Challenge of PHP Validation:
By clicking on the submit button to call the script which is specified in the action attribute of the form to run the PHP code to run validation, you leave the form so if validation fails how does the user return to the form as it was to fix it?

The PHP_SELF variable

This variable stores the value of the current script that is being executed. We use this by embedding it in the action attribute of our form with some extra code to protect it from hacking.

<form name="loginfrm" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="post">

Tells form to return to itself when user clicks submit button. Use PHP code on the page to verify it and if there are any issues we are still on the page.

Updating Form Elements

Include snippets of PHP for the value attribute of each form elements so that the user will not have to retype any values that have been filled in if the form refreshes. Also add some code to print error messages that will be blank if the form has just been loaded or there is no error.

User Name:<input type="text" name="username" size="40" value="<?php echo htmlspecialchars($usrname);?>" required>
<?php echo $username_err; ?>

Add Code To Verify Input

In your PHP file that contains your HTML form, you'll add verification code - traditionally at the top.

<!DOCTYPE html>
<html>
   <?php include("header.php");?>
<body>
   <?php
   //set up checking
   include("functions.php");
   if($_SERVER["REQUEST_METHOD"] == "POST"){
      $valid = True;
      $err_msg = "";
      $usrname = clean_input($_POST['username']);
      $pwd = clean_input($_POST['pwd']);

      $valid = checkLogin($usrname,$pwd);

      if ($valid){
         redirect("Welcome.php");
      }else{
         $err_msg = "Your login information was not correct. Please try again.";
      }
    }
?>

<h1>LabCatsCoding</h1>
<main>
<h2>Login</h2>
<form name="login" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="post">
  <table class="login_table">
    <tr>
       <td>Username:</td>
       <td><input type="text" name="username" size="20" value="<?php echo htmlspecialchars($usrname);?>" required></td>
    </tr>
    <tr>
      <td>Password: </td>
      <td><input type="password" name = "pwd" size="20" value="<?php echo htmlspecialchars($pwd);?>" required></td>
</tr>
    <tr><td colspan="2" class="errclass"><?php echo $err_msg; ?></td></tr>
    <tr><td class="center"><input type="submit" value="Login" ></td><td class="center"><a href="createnewuser.php">Create a New Account</a></td></tr>
 </table>
</form>
</body>
</html>

Opening a Connection

First, you need to know your connection information. What is that?

 $servername = 'localhost';
 $username = 'mscoding_members';
 $pwd = 'nottelling';
 $dbname = 'mscoding_members';

But...wait where do I find all that information? Do you remember way back in Lesson 2? The Creating a Database topic? Well, you created a database, a username and a password way back then so you could access your databases on your website. That is what you will use here. The only exception is $servername and that is 95% of the time going to be localhost.

Connecting to the Database

Once you have all that information, you can create a connection to your database like this:

<?php
   //set up connection variables
   $servername = 'localhost';
   $username = 'mscoding_members';
   $pwd = 'nottelling';
   $dbname = 'mscoding_members';


   //connect!
   $conn=new mysqli($servername, $username, $pwd, $dbname);

   //Check to see if it connected
   if( $conn->connect_error ){
      die("Connection failed:  ". $conn->connect_error );
   }
   echo "You are connected... ";
?>

Close the Connection

Don't forget when you are done to close the connection though it will automatically be closed when the script ends. But, it is good practice to do so!

 $conn->close();

1. Connect

Now that we know how to connect to a database, we can get data out of it. The first step is from the lesson before....connect to the database.

2. Write and Run the SQL

Next, we store the SQL that we want to run in a variable. If it's complicated SQL you can test it on the live database first to make sure it works in phpMyAdmin.

In the example below we are gong to start with just a simple select statement.

//write SQL
$sql = "SELECT * FROM members";

Next, we are going to test and see if a result was returned by invoking the query() method of the mysqli class. This will create an instantiation of the mysqli_result class.

//runs the query to get data 
//stores the results in a mysqli_result class
$result = $conn->query($sql);

//test to see if the query ran - will be NULL if failed
if($result){

   //test to see if any records matched the query
   //uses $num_rows property of mysqli_result object
   if($result->num_rows > 0){
      echo "Here's where we will print out our results";
   }else {     
      echo "No records matching your query were found."; 
   } 
}else{ 
   echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
}
?>

3. Display the data results

Final thing to figure out is how to loop through the data returned. We do this using another method of the mysqli_result class - the fetch_array method. This method pulls each row of the results into an array for accessing until there is no data left.

The first line prints out the beginning of an HTML table with the row headers defined. Each row of data is stored in the $row variable and then printed using HTML table elements. And we print out the end table element after the loop is finished!

That part of the code looks like this:

echo "<table><tr><th>First</th><th>Last</th><th>Age</th></tr> ";
while($row =  $result ->fetch_array()){ 
   echo "<tr> "; 
   echo "<td>". $row['FirstName'] . "</td>";
   echo "<td>" . $row['LastName'] . "</td>";
   echo "<td>" . $row['Age'] . "</td> ";
   echo "</tr> ";
}
echo "</table> ";

4. Put it all together!

Here's the code in its entirety!

Now that you know how to do a basic connection to the database and retrieve data, let's revisit how you would do more complex SQL queries with WHERE clauses, ORDER BY, GROUP BY, and even joining tables.

All you have to do is replace the SQL with more advanced SQL.

Using a basic WHERE clause

In the example below we are gong to start with just a simple WHERE clause. Need to review?

//write SQL
$sql = "SELECT * FROM members WHERE Language ='Spanish'";

Notice anything different above? It has to do with quotes. We put single quotes around the string that we are trying to match the field to while we have double quotes around the entire value of the string stored in the variable $sql.

Here's an example with multiple conditions:

//write SQL
$sql = "SELECT * FROM members WHERE Age= 12 OR Age = 13";

Using Multiple Tables..or Joins!

These work the same way that all the rest above had. The only difference can sometimes come in the processing of the data. What if two tables have a field with the same name? Here it can be very helpful to use column aliases for that situation as well as just to shorten column fields for the code. We'll do a full example below! Need to review?

<?php
   //set up connection variables
   $servername = 'localhost';
   $username = 'mscoding_members';
   $pwd = 'nottelling';
   $dbname = 'mscoding_members';

   //connect!
   $conn= new mysqli($servername,$username, $pwd, $dbname);

   //Check to see if it connected
   if( $conn->connect_error ){
     die("Connection failed:  ". $conn->connect_error );
   } 

   //write SQL
   $sql = "SELECT m.FirstName as First, m.LastName as last, p.PhoneNumber as phone, p.Type 
      FROM members m 
      JOIN phonenumbers p on m.MemberId = p.MemberID";

   $result = $conn->query($sql);
   if($result = $conn->query($sql)){
      if($result->num_rows > 0){
         echo "<table><tr><th>First</th><th>Last</th><th>Age</th></tr> ";
         while($row =  $result ->fetch_array()){ 
           echo "<tr> "; 
           echo "<td>". $row['first'] . "</td>";
           echo "<td>" . $row['last'] . "</td>";
           echo "<td>" . $row['phone'] . "</td> ";
           echo "<td>" . $row['Type'] . "</td> "; 
           echo "</tr> ";
         }
         echo "</table> ";
      }else {     
        echo "No records matching your query were found."; 
      } 
   }else{ 
      echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
   }

   //close connection
   mysqli_close($conn);
?>

Just like before - all you are changing is the SQL.  And maybe a little of how you manage it.

Inserting Data

First, you might want to check your SQL on a live database via phpMyAdmin to make sure it is correct. Once you are sure it is right - plug it into the value for $sql below.

<?php
...

   //write SQL
   $sql = "INSERT INTO members (FirstName, LastName, Age) 
         VALUES ('Hadley', 'CatWoman', 13)";

   //Run SQL and check results
   if(mysqli_query($conn, $sql)){
     echo "Success!  Records inserted.";
   }else {    
      echo "ERROR: Could not execute SQL $sql. ".mysqli_error($conn); 
   }
...
?>

You can also insert multiple rows by creating a SQL statement like this:

//write SQL
   $sql = "INSERT INTO members (FirstName, LastName, Age) 
      VALUES ('Maxwell', 'SecretAgent', 14),
             ('Luke', 'CoolHand', 13),
             ('Margot', 'Monkey', 13),
";

Updating Data

Updating data works similarly to insert new data...setup your SQL the way you want it - test first. And then plug it into code like below.

<?php
   
...

   //write SQL
   $sql = "UPDATE members SET LastName = 'SoccerStar' 
       WHERE memberID = 6";

   //Run SQL and check results
   if(mysqli_query($conn, $sql)){
     echo "Success!  Records updated.";
   }else {    
      echo "ERROR: Could not execute SQL $sql. ".mysqli_error($conn); 
   }
...
?>

Deleting Records

And surprise ....delete works very similarly. Just be extra careful in deleting records and make sure there is a WHERE clause!!!

<?php
...

   //write SQL
   $sql = "DELETE FROM members WHERE LastName = 'SoccerStar'";

   //Run SQL and check results
   if(mysqli_query($conn, $sql)){
     echo "Success!  Records updated.";
   }else {    
      echo "ERROR: Could not execute SQL $sql. ".mysqli_error($conn); 
   }
...
?>

First, create your form as you normally would in HTML. The only difference is we need to add a link to the PHP script that the form will send the information to. This will look like:

  <form action="insert.php">

Next we need to write the PHP to connect to the database, create record and store the information from the form into that record. Make sure you read the comments in the code - they explain what is going on!

<?php

  //Get fields from the form and store them in variables
  $firstName = $conn->real_escape_string($_REQUEST['firstName']);
  $lastName = $conn->real_escape_string($_REQUEST['lastName']);
  $age = $conn->real_escape_string($_REQUEST['age']);
  $email = $conn->real_escape_string($_REQUEST['email']);
  $language = $conn->real_escape_string($_REQUEST['language']);
  $description = $conn->real_escape_string($_REQUEST['desc']);

  //Double check that all data returned - remove this before production
  echo "first name is ", $firstName, " last name is ", $lastName, " age is ", $age, " email is ", $email, " lang is ", $language, " and description is ", $description;

  //attempt to insert data into database
  $sql = "INSERT INTO members(FirstName, LastName, Age, Email, Language, Description)
    VALUES('$firstName', '$lastName', '$age', '$email', '$language', '$description')";

  //Check to make sure data was added
  if(mysqli_query($conn, $sql)){
    echo "Record added.";
  }else{
    echo "Error - could not execute".$conn->error;
  }
?>

Done!

What About Multiple Tables?

One more thing...what would you do if you needed to enter the phone number too? As you might remember, it is in a separate table. Let's take a look again:

In order to create a record of the phone number for a student - you would need:

• MemberID
• Phone Number
• Phone Type

Remember, that the ContactID field will automatically be created when you create the record since it is an auto incremented field. Or it should be.

You can get their phone number and phone type by adding those fields to the form...but how do you get their MemberID? By using the PHP mysqli_insert_id() function to retrieve the the most recency generated ID. Here's what the PHP would look like - you would add this bit of code AFTER you created the record in the members table.

//Check to make sure data was added
if(mysqli_query($conn, $sql)){
   $last_id = $conn->insert_id;
   echo "Record added. Newest ID is: ".$last_id;
}else{
   echo "Error - could not execute".$conn->error;
}

//now add phone numbers - first is required so should be there
$sqlphone = "INSERT INTO phone(MemberID, PhoneNumber, Type) VALUES ('$last_id','$phone1', '$type1')";

Reading data is essentially just pulling data from the database.

Data Table or Grid

One of the most useful structures in looking at data is the grid view or a table or a spreadsheet view.

• The columns represent the fields in the database table.
• The rows represent the records.

We will start with a basic grid but you can add additional functionality like this on later:

• Edit - the ability to edit a given row of data
• Delete - the ability to delete a given row of data
• Search option to find specific fields
• Sort - the ability to sort by column
• Filter - the ability filter what data is displayed based on certain criteria

Here's the HTML to create a basic grid:

<?php
  //connect & check database
  $conn= new mysqli('localhost','mscoding_members', 'nottelling', 'mscoding_members');
  if( $conn->connect_error ){
     die("Connection failed:  ". $conn->connect_error );
  }

  //write SQL
  $sql = "SELECT * FROM members";
  $result = $conn->query($sql);
  if($result){
     if($result->num_rows > 0){
        echo "<table><tr><th>First</th><th>Last</th><th>Age</th></tr> ";
        while($row =  $result ->fetch_array()){ 
           echo "<tr> "; 
           echo "<td>". $row['FirstName'] . "</td>";
           echo "<td>". $row['LastName'] . "</td>";
           echo "<td>". $row['Age'] . "</td> ";
           echo "</tr> ";
         }
         echo "</table> ";
      }else {     
         echo "No records matching your query were found."; 
      } 
   }else{ 
      echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
   }
   //close connection
   mysqli_close($conn);
?>

And here's what that would look like in your web browser:

Adding Data Management Links

So, now we can see the data - but a table like this is often the best place to add the capability to edit or change an existing record, delete a record or even add a new record. Let's start with the easiest one first.

Adding a Record

All you really need for this is a link to the code you wrote in the previous topic. Here we are going to add some CSS to make it look like a button. Here's the HTML:

 <p><a href="add_member.html" class="btn">Add Member</a></p>

Updating a Record

One of the most common tasks is to allow a user to update their information. Sometimes you pull that up using search form and sometimes they click on an entry in a table. If we want to make all the rows in these table updateable, we need to add a link to each one that will take the user to a page where that can happen.

To do this, we are going to add a link to each row that is formatted like this:

 $Mid = $row['MemberID'];
 echo "<td> <a href='edit.php?id=$Mid'>Edit</a></td>";

What does that do?

• First, it creates a variable which stores the value for the primary ID for this table - in this instance it is MemberID.
• Then, it creates a link which goes to the page edit.php and passes that variable to it.

Adding Edit Link to Data Grid

Finally, in our example - we'll add a separate column to edit entries. You can also just link to one or more values in the row as well but this is a little more obvious! Here's our HTML:

<?php
  //connect & check database
  $conn= new mysqli('localhost','mscoding_members', 'nottelling', 'mscoding_members');
  if( $conn->connect_error ){
     die("Connection failed:  ". $conn->connect_error );
  }

  //write SQL
  $sql = "SELECT * FROM members";
  $result = $conn->query($sql);
  if($result){
     if($result->num_rows > 0){
        echo "<table><tr><th>First</th><th>Last</th><th>Age</th></tr> ";
        while($row =  $result ->fetch_array()){ 
           echo "<tr> "; 
           echo "<td>". $row['FirstName'] . "</td>";
           echo "<td>". $row['LastName'] . "</td>";
           echo "<td>". $row['Age'] . "</td> ";
           $Mid = $row['MemberID'];
           echo "<td> <a href='edit.php?id=$Mid'>Edit</a></td>";
           echo "</tr> ";
         }
         echo "</table> ";
      }else {     
         echo "No records matching your query were found."; 
      } 
   }else{ 
      echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
   }
   //close connection
   mysqli_close($conn);
?>

Updating a record - or changing the the information that is stored - is very similar to creating a new record. The only twist is that you have to populate the form with the data first

First, we are going to add a link to our table that shows all the member data. There will be a link in each row of data. By clicking on this link, the user will be able to edit the information for that record.

<?php
..
  //write SQL
  $sql = "SELECT * FROM members";
  $result = $conn->query($sql);
  if($result){
     if($result->num_rows > 0){
        echo "<table>&lt;tr><th>First</th><th>Last</th><th>Age</th></tr> ";
        while($row =  $result ->fetch_array()){ 
           echo "<tr> "; 
           echo "<td>". $row['FirstName'] . "</td>";
           echo "<td>". $row['LastName'] . "</td>";
           echo "<td>". $row['Age'] . "</td> ";
           $Mid = $row['MemberID'];
           echo "<td> <a href='edit.php?id=$Mid'>Edit</a></td>";
           echo "</tr> ";
         }
         echo "</table> ";
      }else {     
         echo "No records matching your query were found."; 
      } 
   }else{ 
      echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
   }
..
?>

Notice anything different? If you look - we added a column to our table and in that column we display the link to edit the record.

• We do this by first storing the ID of the member in a variable.
• Then we create a link which calls another script and passes the value of the variable to it.

Displaying the Record

Once the user clicks on the link to edit a record - a new web page is called which will display a form with all of the record's information. Take a good look at the PHP portion of the code - a few things to note:

• retrieves the MemberID which is passed via the URL and stores it in the variable $id
• creates a SQL statement to retrieve the record that matches that Member ID
• retrieves the language taken by the member so it can be used to set the correct language in a drop down box in the form
• Fills in the form fields by echoing the database results found in the $row variable.

Here's the HTML:

<!DOCTYPE html>
<html>
<head>
   <title>Members Form</title>
   <link rel="stylesheet" href="style.css" type="text/css"></link>
</head>
<body>
  <?php
    //establish and test connection to database
    $conn = new mysqli("localhost", "mscoding_members", "nottelling","mscoding_members");
    if($conn->connect_error){
      die("Connection failed: ".$conn->connect_error);
    }

    //Get Member ID 
    $id = $_GET['id'];

    //get record from database
    $sql = "SELECT * FROM members WHERE MemberID = ".$id;
    $result = $conn->query($sql);
    if($result){
      $row = $result->fetch_array();
      //gets the language
      $lang = $row['Language'];
    }
  ?>
  <h1>Members Form </h1>
  <!-- Start of FORM -->
  // This calls the update.php file and passes the Member ID - when the user hits Submit this program will run
  <form action="update.php?id=<?php echo $id;?>" method="post">
    <table>
    <tr>
      <td>First Name</td>
      <td><input type="text" name=“firstName" value="<?php echo $row['FirstName'];?>"></td>
    </tr>
    <tr>
      <td>Last Name:</td>
      <td><input type="text" name=“lastName" value="<?php echo $row['LastName'];?>" ></td>
    </tr>
    <tr>
      <td>Age</td> 
      <td><input type="number"  min ="1" max="110" name=“age" value="<?php echo $row['Age'];?>"></td>
    </tr>
    <tr>
      <td>Email:</td>
      <td><input type="email" name=“email" value="<?php echo $row['Email'];?>"></td>
    </tr>
    <tr>
      <td>Language:</td>
      <td>
        <select class="radio" name="language">
          <option value="Chinese" <?php if ($lang == "Chinese"){echo "selected";}?>>Chinese</li>
          <option value="French" <?php if ($lang == "French"){echo "selected";}?>>French</li>
          <option value="Spanish" <?php if ($lang == "Spanish"){echo "selected";}?>>Spanish</li>
        </ul>
      </td>
    </tr>
    <tr>
      <td>Description:</td>
      <td><textarea name=“desc" rows="3" cols="50 <?php echo $row['Description'];?> "></textarea></td>
    </tr>
    <tr><td rowspan="2"><input type="submit" name="submit" value="submit"></td></tr>
    </table>
  </form>
  <!-- End of FORM -->
</body>
</html>

Next we need to write the PHP to connect to the database, and update the information from the form into the correct record. Make sure you read the comments in the code - they explain what is going on!

Note: create a separate file for the code below. This should be the same file that the action of the forms points to.

<?php
  // Get the Member ID of the record you are updating from the URL
  $id = $_GET['id']; 

  //Get fields from the form and store them in variables
  $firstName = $conn->real_escape_string($_REQUEST['firstName']);
  $lastName = $conn->real_escape_string($_REQUEST['lastName']);
  $age = $conn->real_escape_string($_REQUEST['age']);
  $email = $conn->real_escape_string($_REQUEST['email']);
  $language = $conn->real_escape_string($_REQUEST['language']);
  $description = $conn->real_escape_string($_REQUEST['desc']);

  //Double check that all data returned - remove this before production
  echo "first name is ", $firstName, " last name is ", $lastName, " age is ", $age, " email is ", $email, " lang is ", $language, " and description is ", $description;

  //attempt to update data into database
  $sql = "UPDATE members SET FirstName = $firstName, LastName=$lastName, Age=$age, Email=$email, Language=$language, Description=$description";

  //Check to make sure data was added
  if(mysqli_query($conn, $sql)){
    echo "Record update.";
  }else{
    echo "Error - could not execute $conn. " . $conn->error;
  }
?>

Done!

What if we want to get rid of some information? Luckily this is the easiest to do...but the most dangerous!!

If you choose to delete a record - make sure you delete any records that depend on the record. For example, if you deleted a member from the Members table - then you would need to delete any records for that member in the PhoneNumbers and Grades tables.

First, we are going to add a link to our table that allows a user to delete a single member. There will be a link in each row of data. By clicking on this link, the user will be able to delete the member.

Let's update the HTML from the previous topic to look like this:

<?php
  //connect & check database
  $conn= new mysqli('localhost','mscoding_members', 'nottelling', 'mscoding_members');
  if( $conn->connect_error ){
     die("Connection failed:  ". $conn->connect_error );
  }
  
  //write SQL
  $sql = "SELECT * FROM members";
  $result = $conn->query($sql);
  if($result){
     if($result->num_rows > 0){
        echo "<table>&lt;tr><th>First</th><th>Last</th><th>Age</th></tr> ";
        while($row =  $result ->fetch_array()){ 
           echo "<tr> "; 
           echo "<td>". $row['FirstName'] . "</td>";
           echo "<td>". $row['LastName'] . "</td>";
           echo "<td>". $row['Age'] . "</td> ";
           $Mid = $row['MemberID'];
           echo "<td> <a href='edit.php?id=$Mid'>Edit</a></td>";
           echo "<td> <a href='delete.php?id=$Mid'>Delete</a></td>";
           echo "</tr> ";
         }
         echo "</table> ";
      }else {     
         echo "No records matching your query were found."; 
      } 

      echo "ERROR: Could not execute SQL $sql. ".$conn->error; 
   }
   //close connection
   mysqli_close($conn);
?>

Just like we did when we added the ability to edit a record - we added a column to our table and in that column we display the link to delete the record.

• • Again, we will use the variable $Mid that we created which stores the ID of the member.
  • Then we create a link which calls another script and passes the value of the variable to it. Except this script is called delete.php.

Deleting the Record

Before we actually delete any data - it's always a good idea to check and make sure the user really wants to delete it. Sometimes, we accidentally click on the wrong spot. To do that, we add a little JavaScript to our HTML. Bascially we change this line:

echo "<td> <a href='delete.php?id=$Mid'>Delete</a></td>";

To this line:

 echo "<td> <a onClick=\"javascript: return confirm('Please confirm deletion');\" href='delete.php?id=$Mid'>Delete</a></td>";

We learned about the onclick event back in Advanced Website building. Here, we are adding a wrinkle to return a value of true or false via an Alert box.

If the user clicks OK, the script will be called to delete the record. If the user clicks Cancel, the script will not be called.

Next we need to write the PHP to connect to the database, and delete the requested record. Make sure you read the comments in the code - they explain what is going on! Our code will double check with our user and then will

• retrieve the MemberID which is passed via the URL and stores it in the variable $id
• creates a SQL statement to delete the record that matches that Member ID
• delete that record

<?php
  // Get the Member ID of the record you are updating from the URL
  $id = $_GET['id']; 

  //establish and test connection to database
  $conn = new mysqli("localhost", "mscoding_members", "nottelling","mscoding_members");
  if($conn->connect_error){
    die("Connection failed: ".$conn->connect_error);
  }

  //attempt to delete record
  $sql = "DELETE FROM members WHERE MemberID ='$id'";
   
  //Check to make sure data was added
  if(mysqli_query($conn, $sql)){
    echo "Record deleted.";
  }else{
    echo "Error - could not execute $conn. " . $conn->error;
  }
?>

Done!

explode() Function

This takes in a string and breaks it into an array based on a specified character - can be a single space, a comma, or any other character.

explode(separator, string, limit)

• separator: required -  what character to use the break string into sub strings
• string: required - the string to explode
• limit: optional - this can tell the function how many array elements to return

See this example.

<?php
   $str = "Today is Monday.  I wear pink.";
   $pieces = explode(" ", $str);
   echo "Piece1 is $pieces[0] and Piece 4 is $pieces[3] and Piece 7 is $pieces[6]";  
?>

 Output is: 
Piece1 is Today and Piece 4 is and Piece 7 is pink. 

list() Function

Assigns variables from an array.

<?php
   $my_array = array("Dog","Cat","Horse");
    list($a, $b, $c) = $my_array;
   echo "I have several animals, a $a, a $b and a $c.";
?>