Quakenet/#php Tutorial

Note: If you opened this page from an external URL pay attention that all chapters are linked together. Be sure you also read all prior chapters of this tutorial, otherwise you will miss relevant content explained before.

Guestbook

  1. Writing an own guestbook
  2. Layout of the mysql tables
  3. Layout of the php scripts
  4. Code for showing the guestbook
  5. Code for adding new entries
  6. Disadvantage of our guestbook script

1. Writing an own guestbook

A guestbook is similar to a newsscript but the entries are from the users, not from the administrator. But this also result in problems as the user can enter any text they want. And then you have content in your guestbook you don't want to have. For this reason every user input must be checked, also for spam.

2. Layout of the mysql tables

We use a similar table as the news table. It got 4 columns.

  1. id - The normal identification column with the type INT.

  2. added - The time when the entry was added. Type is DATETIME.

  3. author - The name of the user who added the entry. A VARCHAR(50) should be enought.

  4. content - The text of the entry. Type is TEXT.

The sql query looks like this.

CREATE TABLE guestbook (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author VARCHAR(50) NOT NULL,
    added DATETIME NOT NULL,
    content TEXT NOT NULL
);

You can add this table with phpMyAdmin of course.

3. Layout of the php scripts

The php script as two objectives. First, it must fetch the rows from the database and show them. Second, it must process the values from a form to add them in mysql. We can differ it by checking the value of $_SERVER['REQUEST_METHOD']. If the file is opened by a POST request (like a form) we save the form values to the database, for all other request types (which include the GET request) we show the guestbook and a form for adding new guestbook entries.

<?php
error_reporting
(E_ALL);
ini_set('display_errors'1);

if (
'POST' == $_SERVER['REQUEST_METHOD']) {
    
// code for adding new entries in the database
} else {
    
// code for showing the guestbook and the form
}
?>

As we use the mysql we create a connection to the database. And we load some html stuff with include or even better with readfile.

<?php
error_reporting
(E_ALL);
ini_set('display_errors'1);

$db = @new mysqli('localhost''username''password''database');
if (
mysqli_connect_errno()) {
    die(
'Could not create a connection to the database: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}

readfile('header.html'); // contains the <body> tag

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    
// code for adding new entries in the database
} else {
    
// code for showing the guestbook and the form
}

readfile('footer.html');
?>

Now we can begin with the two parts.

4. Code for showing the guestbook

The code for showing the guestbook entries is almost the same as for the news script, so we don't need any explanations here.

<?php
// [...]

} else {
    
$sql 'SELECT
                added,
                author,
                content
            FROM
                guestbook
            ORDER BY
                added DESC'
;
    
$result $db->query($sql);
    if (!
$result) {
        die(
'the query couldn't be executed'.$db->error);
    }
    if ($result->num_rows) {
        while ($row = $result->fetch_assoc()) {
            echo '
<div class="entry">'."\n";
            echo '    
<span class="author">'.htmlspecialchars($row['author'])."</span>\n";
            echo '    
<span class="date">'.$row['added']."</span>\n";
            echo "    <p>\n";
            echo nl2br(htmlspecialchars(preg_replace('
~S{30}~', '', $row['content'])));
            echo "    </p>\n";
            echo "</div>\n";
        }
    } else {
        echo '
<class="info">There are no guestbook entries</p>';
    } 
    readfile('
form.html);
}

// [...]
?>

The function htmlspecialchars is used to escape html characters like < so they are shown as < instead of starting a html tag. This is needed as people always try to add javascript code to your guestbook. The function nl2br appends after every new line the html newline tag <br />. This is needed as in the database there are just normal linebreaks with \n. The preg_replace statement isn't easy to explain as it got a regular expression. Its dealed in a later chapter but for now this preg_replace statement adds in words which are longer as 30 characters spaces inside the words so the browser can wrap the line. This is to prevent content lines like AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA.... breaking your whole html code.

The form.html file contains the code for the form so the users can add a new entry to your guestbook.

<form action="guestbook.php" method="post">
    <fieldset>
        <legend>Write into the guestbook</legend>
        <label>Name: <input type="text" name="author" /></label>
        <label>Text: <textarea name="content" rows="6" cols="40"></textarea></label>
        <label>{QUESTION}: <input type="text" name="answer"/></label>
        <input type="submit" name="formaction" value="Add" />
    </fieldset>
</form>

The form is send to the php file guestbook.php. The field about {QUESTION} is to prevent spam, it's explained later.

Of course the guestbook is empty so we see the line There are no guestbook entries. Now we add the code for adding new entries.

5. Code for adding new entries

We get the values author, content, answer and formaction. First we check if we really get this values from the form or if the user use a self written form.

<?php
// [...]

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (!isset(
$_POST['author'], $_POST['content'], $_POST['answer'], $_POST['formaction'])) {
        die (
'only use forms from the homepage.');
    }
} else {

// [...]
?>

Nun machen wir einfache Textabfragen indem wir gucken ob der Benutzer überhaupt was eingegeben hat. Dazu verwenden wir einmal die Funktion trim um Leerzeichen vor und nach der Eingabe zu löschen und vergleichen den Rückgabewert mit '' und wissen dann ob was eingegeben wurde oder nicht.

<?php
// [...]

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (!isset(
$_POST['author'], $_POST['content'], $_POST['answer'], $_POST['formaction'])) {
        die(
'only use forms from the homepage.');
    }
    if (
'' == $author trim($_POST['author']) or
            
'' == $content trim($_POST['content']) or
            
'' == $answer trim($_POST['answer'])) {
        die(
'Fill out the form completly');
    }
} else {

// [...]
?>

Now we check the answer field. We expect a special answer from the user. The {QUESTION} part in the form must be changed so the user adds the right answer. This is similar to a CAPTCHA but more simplier. With this input we exclude spam bots which whould spam the guestbook. It's enought to check for a single word.

<?php
// [...]

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (!isset(
$_POST['author'], $_POST['content'], $_POST['answer'], $_POST['formaction'])) {
        die(
'only use forms from the homepage.');
    }
    if (
'' == $author trim($_POST['author']) or
            
'' == $content trim($_POST['content']) or
            
'' == $answer trim($_POST['answer'])) {
        die(
'Fill out the form completly');
    }
    if (
'' != $answer) { // change for your {QUESTION} part
        
die ('You must answer the question correctly.');
    }
} else {

// [...]
?>

Note that you shouldn't ask for complex stuff, especially case sensitive answers.

Now we can save the data in the database. The easiest way is to build a INSERT query with concatenation of strings and variables. This will be tricky as the content can contain ' characters which whill break your sql query. This is called sql injection as the users try to add subqueries like DROP TABLE guestbook;. For this reason we must protect/change the values. In the mysql extension there is a function called mysql_real_escape_string. It is used to return an escaped version of a variable or value so it can be safely used in a sql query.

<?php
$sql 
'INSERT INTO
            Guestbook(author,added,content)
        VALUES
            ("'
.mysql_real_escape_string($author).'",
             NOW(),
             "'
.mysql_real_escape_string($content).'");';
?>

In the MySQLi extension we use prepared statements. These are special sql queries with placeholders (single ? characters). These placeholders are replaced with special methods with values. These methods care take automatically of the content of the values and escape them if needed so they don't break your sql query.

To create such a prepared statement you must use the prepare() method (mysqli_prepare). The ? characters are the placeholders which we fill later with values.

<?php
// [...]

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (!isset(
$_POST['author'], $_POST['content'], $_POST['answer'], $_POST['formaction'])) {
        die(
'only use forms from the homepage.');
    }
    if (
'' == $author trim($_POST['author']) or
            
'' == $content trim($_POST['content']) or
            
'' == $answer trim($_POST['answer'])) {
        die(
'Fill out the form completly');
    }
    if (
'' != $answer) { // change for your {QUESTION} part
        
die ('You must answer the question correctly.');
    }
    
$sql 'INSERT INTO
                guestbook(author, added, content)
            VALUES
                (?, NOW(), ?)'
;
    
$stmt $db->prepare($sql);
    if (!
$stmt) {
        die (
"A query couldn't be prepare: ".$db->error);
    }
} else {

// [...]
?>

As prepare() can fails we check the return value. If all goes fine the method returns a MySQLi_STMT object which we can use. For us they are the methods bind_param() and execute().

<?php
// [...]

if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (!isset(
$_POST['author'], $_POST['content'], $_POST['answer'], $_POST['formaction'])) {
        die(
'only use forms from the homepage.');
    }
    if (
'' == $author trim($_POST['author']) or
            
'' == $content trim($_POST['content']) or
            
'' == $answer trim($_POST['answer'])) {
        die(
'Fill out the form completly');
    }
    if (
'' != $answer) { // change for your {QUESTION} part
        
die ('You must answer the question correctly.');
    }
    
$sql 'INSERT INTO
                guestbook(author, added, content)
            VALUES
                (?, NOW(), ?)'
;
    
$stmt $db->prepare($sql);
    if (!
$stmt) {
        die (
"A query couldn't be prepare: ".$db->error);
    }
    
$stmt->bind_param('ss'$author$content);
    if (!
$stmt->execute()) {
        die(
"Query couldn't be executed: ".$stmt->error);
    }
    echo 
'<p class="info">Guestbook entry added. <a href="guestbook.php">Back to the guestbook</a>.</p>';
} else {

// [...]
?>

The two s characters declare that there are two string following ( for the columns author and content). For bind_param() there are the characters i for integer numbers, d for float numbers (double) and b for binary data. But we use mainly i and s. After that we call the execute() method to execute the query and output some info message. As the execute() method can fail we check the return value with the if clause.

If you test your script it's possible that your webserver use magic quotes. As we take care of the escaping by ourself (or be more specific bind_param() do) we revert magic quotes if activated. We do this at topmost of our script.

<?php
error_reporting
(E_ALL);
ini_set('display_errors'1);

include 
'magic_quotes_remove_slashes.php'// or however the file is called
// or copy the code here instead of using an include

// [...]
?>

Now our guestbook is ready.

6. Disadvantage of our guestbook script

This script isn't totally save. As an example a user can add several entries at once. It can be try to prevent if you save the ip address and check the ip list before adding a new entry. And there is no paging and all entries are shown in one page.

Questions about the chapter

No questions

Back to Next to
Copyright © to the OPs of #php/QuakeNet Valid XHTML 1.0 Strict Valid CSS!