Learnit Training

7. Extended Example

In this chapter we are going to put together everything we have learned before: we will use forms, programming techniques and SQL to communicate with the database.

We continue with the form we already created in chapter 4 (about forms). When processing this response form, which is intended for visitors to a website, we are going to store data in the database. We will also create a system for the administrator of a website to view, modify and discard responses.

7.1. Saving in the database

In process.php We checked the entries and displayed them again in the browser. We did not save the data yet. We will do that now.

Task 7.1

  1. Create a folder 7/ in phpcourse/.
  2. Save form.html in phpcourse/7/ (right click, save target as).
  3. Save this program code as process.php in phpcourse/7/.
  4. View the source of phpcourse/7/process.php by opening it in Notepad
  5. Look at what is happening, some things have changed:
    • There are pieces of text that comment are added, they always begin with //. This code is not executed and only serves to make the code more readable for a programmer.
    • The variable $correct has been added. This variable keeps track of errors. As soon as we encounter an error, we set the bolean variable $correct at false (it is not correct).
    • The bottom piece of code is completely new:
      //Was alles correct ingevuld?
      if ($correct) { // Opslaan!
      	// Vul hier de code in om de reactie op te slaan!
      	echo "

      Bovenstaande informatie is opgeslagen!
      \n"; } else { // Er is ergens een foute waarde ingevoerd, geef de bezoeker de // mogelijkheid om terug te gaan echo "

      Er is een foute waarde ingevoerd, ga terug.
      \n"; }
      At the site of //Fill in the code here to save the reaction! We are about to insert the code that will store the data.
  6. Check what the form does by going to http://localhost/phpcursus/7/form.html in your browser.
  7. Change the line //Fill in the code here to save the reaction! in:
    $db = new PDO('mysql:host=localhost;dbname=reacties', 'root', '');
    
    //kijk of een persoon al bestaat
    $query = "SELECT id FROM personen WHERE naam = ? AND email = ?";
    $stmt = $db->prepare($query);
    $stmt->execute(array( $naam, $email));	
    
    if ($stmt->rowCount() > 0){
    	$row = $stmt->fetch(PDO::FETCH_ASSOC);
    	$persoon_id	= $row['id'];
    } else { // Voeg de naam en e-mail toe in de tabel personen
    	$query = "INSERT INTO personen(naam, email) VALUES (?, ?)";
    	$stmt = $db->prepare($query);
    	$stmt->execute(array( $naam, $email));	
    	
    	//vraag de id van de nieuwe persoon op
    	$persoon_id = $db->lastInsertId();
    }
    
    // voeg de reactie toe in de tabel reacties. Gebruik de id van de zojuist toegevoegde persoon
    $query = "INSERT INTO reacties(persoon_id, reactie, datum) VALUES (?, ?, ?)";
    $stmt = $db->prepare($query);
    $stmt->execute(array( $persoon_id, $reactie, date('Y-m-d H:i:s')));	
    

    The above contains a lot of new things that we will discuss here. First, a connection is made with the MySQL server. For this, we make use of the PHP Data Object extension. This PHP extension makes it possible to communicate with the database from our PHP script.

    Our MySQL server runs on the localhost and we log in with the root account (actually this is not very nice, but for this demonstration it is the easiest), here no password is needed. The code to log in therefore looks like volt:

    $db = new PDO('mysql:host=localhost;dbname=reactions', 'root', '');
    

    The result is a new database object. It is stored in the variable $db. We can use this object to communicate with the database.

    Next, we create SQL queries, which you will probably recognise from the previous chapter. However, there is one major difference. The placeholder for the name and e-mail address is a ?. By using this placeholder we protect ourselves against SQL injections. This is a common attack, where hackers try to add their own SQL code to the query. Unfortunately, we cannot go into detail about this. Fortunately, there is a lot of information available online about SQL injections.

    We ask the database to prepare the query with $stmt = $db->prepare($query);. Then we run the queries with $stmt->execute(array( $name, $email));. Only now, the variables $name and $email are provided during execution. The SQL server places these in the query at the position of the question marks.

    Before we add the person, we first look, with the help of a SELECT whether a person already exists with the name and e-mail address entered.

    The function $db->lastInsertId(); ensures that the id of the last query performed is used, thus associating a response with the correct person.

  8. Test the new script by going to the form again and adding some responses. Make sure that there are people who have multiple messages in the database. You can view the added record using PHPMyAdmin http://localhost/phpmyadmin.
  9. If necessary, download the new version of the program code for verWork.php here (overwrite the old verWork.php).
If all went well, you have filled the database with new data, we will now create a simple script to display this data.

7.2. Displaying data

We remember from the previous chapter how to combine data from different tables. We modify the query a little by adding a ORDER BY command:

SELECT persons.name, persons.email, reactions.response, reactions.date
FROM persons, reactions
WHERE reactions.person_id = person.id
ORDER BY reactions.date

If we do not, the rows are returned in an undefined order.

The following php script displays all responses:



	Reacties


query($query);
	
	while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
		echo "\n";
		echo "\n";
		echo "\n"; //pas deze regel aan!
	}
?>
van " . $row["naam"] . " op " . $row["datum"] . "
" . $row["reactie"] . "
 
In this script, we again connect to the MySQL server, select a server and then run SELECT command. We then loop through the results of the query using the while loop. We print the result in an HTML table.

Assignment 7.2.

  1. Save reactions.php in phpcourse/7/ (right click, save target as).
  2. phpcourse/7/reactions.php corresponds to the above script.
  3. Go to http://localhost/phpcursus/7/reacties.php to view all the comments you have previously made.
  4. View the HTML code generated by the reactions.php script via the browser.

7.3. Delete data

We are going to phpcourse/7/reactions.php modify it so that it contains a button for deleting reactions. The file already indicates where this button should be, namely on the line where it says: // Adapt this line!. We are going to put a link on that line to remove.php place and send the id of the reaction. The deletion itself is handled by the delete.php script:



	Verwijderd


prepare($query);
	$stmt->execute(array($reactie_id));
	echo 'De reactie is verwijderd!
'; echo 'Ga terug naar de reacties.
'; } else { echo "Ongeldige aanvraag"; } ?>

Assignment 7.2.

  1. Save delete.php in phpcourse/7/. Note that the file must be saved with the extension .php
  2. Open phpcourse/7/reactions.php in Notepad
  3. Change the line with // Adapt this line! in:
        echo "
            remove\n";
    
  4. Go to the comments section http://localhost/phpcursus/7/reacties.php Please make sure that comments are deleted after clicking on the link.