Learnit Training

7. Extended Example

In This chapter, we'll merge everything we've learned to do this: we use forms, programming techniques, and SQL to communicate with the database.

We continue with the form we already made in Chapter 4 (on Forms). When processing this comment form, which is intended for website visitors, we will store data in the database. In addition, we create a system for the administrator of a website to view, modify and discard comments.

7.1. Storing in the database

In Processing. php We checked the entry, and we showed it in the browser again. We didn't save the data yet. We are going to do that now.

Contract 7.1

  1. In phpcursus/ Create a folder 7/ on.
  2. Save form. html in phpcursus/7/ (right-click, save Target As).
  3. Save This code as processing. PHP on in phpcursus/7/.
  4. View the source of phpcursus/7/processing. PHP by opening it in Notepad
  5. Study what is happening, some things have changed:
    • There are pieces of text that Comment Are added, they always start with //. This code is not executed and serves badly to make the code for a programmer more readable.
    • The variable $correct has been added. This variable helps to ensure that errors are created. Once we encounter an error we put the Boleaanse variable $correct Op False (It's 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"; }
      On the site of Enter the code here to save the comment! We will'll insert the code that will store the data.
  6. Check what the form does by browsing to http://localhost/phpcursus/7/form.html in your browser.
  7. Change the rule Enter the code here to save the comment! 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')));	
    

    In the above there are a lot of new things that we discuss here. First, a connection to the MySQL server is created. We make use of the PHP Data Object extension. This PHP extension allows you to communicate from our PHP script with the database.

    Our MySQL server runs on the Localhost And we log in with the Root Account (actually this is not neat, but for this demonstration the simplest), here is no password necessary. The code to log in will therefore look like Volt:

    $db = new PDO (' mysql: host = localhost; dbname = Comments ', ' 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.

    Hierna maken we SQL queries, die u waarschijnlijk uit het vorige hoofdstuk herkent. Er is echter een groot verschil. Op de plek waar de naam en het e-mailadres moeten komen staat een ?, de zogenaamde placeholder. Door gebruik te maken van deze placeholder beschermen we ons tegen SQL injecties. Dit is een veel voorkomende aanval, waarbij hackers hun eigen SQL code proberen toe te voegen aan de query. Helaas kunnen we hier niet in detail op in gaan. Gelukkig is er veel online informatie beschikbaar over SQL injecties.

    We ask the database to prepare the query with $stmt = $db-> prepare ($query);. After this we execute the queries with $stmt-> Execute (Array ($naam, $email));. Only now when executing are the variables $naam and $email Meegegegeven. The SQL server places it in the query at the place of the question marks.

    Before we add the person we look first, using a SELECT Whether a person already exists with the name and e-mail address that is filled in.

    The function $db-> lastInsertId (); Causes the Id Of the last query executed, this will link a response to the correct person.

  8. Test the new script by going back to the form and adding some comments. Make sure there are people who have multiple messages in the database. You can view the added record using PHPMyAdmin http://localhost/phpmyadmin
  9. Download the new version of the code for processing. PHP (overwrite the old processing. PHP).
If it all went well, you filled the database with new data, we're going to create a simple script to display this data.

7.2. Displaying Data

From the previous chapter, we still know how to combine data from different tables. We are adjusting the query a little bit by ORDER BY command to add:

SELECT people. Name, people. Email, comments. Comment, comments. date
FROM people, comments
WHERE comments. persoon_id = personen.id
ORDER BY comments. Date

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

Below PHP script displays all comments:



	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 connect again to the MySQL server, we select a server and then we execute SELECT command. Then we make a loop through the results of the query using the While loop. We print the result in an HTML table.

Contract 7.2.

  1. Save comments. php in phpcursus/7/ (right click, save Target As).
  2. Phpcursus/7/comments. PHP matches the above script.
  3. Surf to http://localhost/phpcursus/7/reacties.php to view all the comments you have previously made.
  4. View the HTML code generated with the comments. php script via the browser.

7.3. Delete data

Let's go Phpcursus/7/comments. php Adjust the button to remove comments. The file already shows where this button should appear, namely on the line where it says: Customize this line!. On that line We will link to Remove. php Place and send it Id Of the reaction with it. The removal itself is handled by the Remove. 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"; } ?>

Contract 7.2.

  1. Save Delete. php in phpcursus/7/. Please note that the file must be saved with the extension. php
  2. Open Phpcursus/7/comments. PHP in Notepad
  3. Change the line with Customize this line! In:
        EchoRemove\n ";
    
  4. Go to the comment overview http://localhost/phpcursus/7/reacties.php Check if comment Daadwerlijk removals are after clicking the link.