Your simple guestbook

[Jacki]

Hi guys, I'm going to show you how you can make a simple guestbook using PHP/MYSQL.
(This is my first tutorial , I hope it will be useful)

First: we have to create the table into the database:

CREATE TABLE `guestbook` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 255 ) NOT NULL ,
`email` VARCHAR( 255 ) NOT NULL ,
`text` TEXT NOT NULL
) ENGINE = MYISAM ;

Second: the code need to be safe from sql injection etc, so we have to do some step:
1) Create the form to send the message (fields: username, email, text)
2) Add slashes at all the strings to prevent sql injection, check that email is true
3) Store the messages into the database
4) Show all the messages

This is the code:

<?php
// Database connection
$db            = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
              mysql_select_db("tutorials", $db);

// This check that the form has been submited
if(isset($_POST['username']))
{
    // Variables, addslashes is for secure the strings from sql injections
    $email         = addslashes($_POST['email']);
    $username     = addslashes($_POST['username']);
    $text         = addslashes($_POST['text']);

    // Check if the email is true and username and text are not empty
    if (!empty($username) && !empty($text) && preg_match('/^[^0-9][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[@][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[.][a-zA-Z]{2,4}$/', $email))
    {
        if(mysql_query("INSERT INTO guestbook (username, email, text) VALUES ('".$username."', '".$email."', '".$text."')"))
            echo "Your greeting has been succesful sent! Thanks!";
    } 
    else
    {
        echo "Error, please check your data.";
    }
}

// This show the messages stored into the database
$sql_query = mysql_query("SELECT username, email, text FROM guestbook ORDER BY id DESC");
while ($array = mysql_fetch_array($sql_query)) {
    echo "<table>
            <tr><td>".stripslashes($array['username'])."</td><td>".stripslashes($array['email'])."</td></tr>
            <tr><td>".stripslashes($array['text'])."</td></tr>
          </table>";
}

// This is the the form
echo "<form action=\"guestbook.php\" method=\"post\">
        Username:         <input type=\"text\" name=\"username\"><br />
        Email:            <input type=\"text\" name=\"email\"><br />
        Your greeting:    <textarea name=\"text\"></textarea>
                        <input type=\"submit\" value=\"Send\">
      </form>";
      
// Database closed
mysql_close($db);
?>

Remember that this is only the basic code of a guestbook, you can do a lot of other things, for example you can put in the date, or add some emoticons..

Enjoy! Bye!

[so1i]

That's a nice first tut! Simple, and interesting! Thanks mate +rep

[debtboy]

Awesome first tutorial +rep

[Jacki]

Thanks, I'm thinking that I'll write another tutorial soon...

[chili5]

Not bad.

A few ideas:

1. Create a way to display x messages per page.
2. How about you make it so it displays the date of the messages?
3. Make the program ensure that the user is submitting the form.

[Jacki]

It's very easy to add more things to this guestbook:
1. Messages per page, we have to define how may messages we want do display per page, and do a simple calc for the pages (number of the stored messages / messages per page)
2. For add the date we have to add a field to the database (date) it will be an int(11) because we insert the date with time().

Now, the code look quite easy:

<?php
define("STEP", 5); // Messages per page

// Database connection
$db            = mysql_connect("localhost", "root", "") or die("Could not connect: " . mysql_error());
              mysql_select_db("tutorials", $db);

// This check that the form has been submited
if(isset($_POST['username']))
{
    // Variables, addslashes is for secure the text
    $email         = addslashes($_POST['email']);
    $username     = addslashes($_POST['username']);
    $text         = addslashes($_POST['text']);
    $date         = time(); // Database fiels for date: int(11)

    // Check if the email is true and username and text are not empty
    if (!empty($username) && !empty($text) && preg_match('/^[^0-9][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[@][a-zA-Z0-9_]+([.][a-zA-Z0-9_]+)*[.][a-zA-Z]{2,4}$/', $email))
    {
        if(mysql_query("INSERT INTO guestbook (username, email, text, date) VALUES ('".$username."', '".$email."', '".$text."', '".$date."')"))
            echo "Your greeting has been succesful sent! Thanks!";
    } 
    else
    {
        echo "Error, please check your data.";
    }
}

// Get the page
if(!isset($_GET['p']))
    $start = 0;
else
    $start = $_GET['p'] * STEP;

// This show the messages stored into the database
$sql_query = mysql_query("SELECT username, email, text, date FROM guestbook ORDER BY id DESC LIMIT $start,".STEP."");
while ($array = mysql_fetch_array($sql_query)) {
    echo "<table>
            <tr><td>".stripslashes($array['username'])."</td><td>".date("d F y", $array['date'])."</td></tr>
            <tr><td colspan=\"2\">".stripslashes($array['text'])."</td></tr>
            <tr><td colspan=\"2\" style=\"text-align: right\">".stripslashes($array['email'])."</td></tr>
          </table>";
}

// This issues the pages
$sql_query = mysql_query("SELECT COUNT(id) FROM guestbook");
$count = mysql_result($sql_query, 0); // count how many messages are in the database
$pages = ceil($count/STEP); // Calculate the pages
// Print the pages
for($i = 0; $i < $pages; $i++) {
    echo "<a href=\"guestbook.php?p=".$i."\">".($i+1)."</a> ";
}

// This is the the form
echo "<form action=\"guestbook.php\" method=\"post\">
        Username:         <input type=\"text\" name=\"username\"><br />
        Email:            <input type=\"text\" name=\"email\"><br />
        Your greeting:    <textarea name=\"text\"></textarea>
                        <input type=\"submit\" value=\"Send\">
      </form>";
      
// Database closed
mysql_close($db);
?>

If you have any question please ask me... bye!

[Jordan]

Nice work, Jacki. +rep
I suggest you use mysql_real_escape_string rather than addslashes to cleanse your data.