Page 1 of 1

Sample Guesbook code

Posted: Sat Oct 01, 2011 5:59 pm
by twinedev
Inspired by another thread I read earlier, I decided to throw together a simple little "Guestbook" script that has the following features:

- Any submission requires E-mail address, and it will not be public until a confirmation link in an e-mail is clicked on
- Default view is in chronological order
- Comments can be replied to (and if they are a reply, they show parent Title as well
- Any comment that is part of a thread gives a link so that you can view it "Thread" view from the original comment down.
- When replying to a comment, it auto fills in Title of message with RE: (old title)

Some things to improve it...
- Write a admin editor
- Add another level of "status" (ie. Validated) so that even when validated, admin still needs to make it public
- Add paging to the system
- Add a "rating" system

Again, something simple, but just for fun while the idea was in my head on a lazy Saturday. Some things could probably be optimized better, but should be pretty solid for what it does. This for me is a good starting point for a comment system I'll be building into my custom CMS I'm slowly building.

Enjoy.

CREATE TABLE:[text]CREATE TABLE `GuestBook` (
`ID` int(10) unsigned NOT NULL auto_increment,
`ParentID` int(10) unsigned NOT NULL default '0',
`Status` enum('Active','Pending','Inactive') NOT NULL default 'Pending',
`Name` varchar(50) NOT NULL,
`Email` varchar(75) NOT NULL,
`Title` varchar(100) NOT NULL,
`Message` text NOT NULL,
`TimeStamp` datetime NOT NULL,
`IP` varchar(16) NOT NULL,
PRIMARY KEY (`ID`)
);[/text]

Code: Select all

<?php

    define('GB_FROM_NAME',  'Guestbook Handler');
    define('GB_FROM_EMAIL', 'info@domain.com');
    define('GB_BCC_EMAIL',  'admin@domain.com'); // To notify us something was posted

    $aryDB = array('host' => 'HOSTNAME',
                   'user' => 'USERNAME',
                   'pass' => 'PASSWORD',
                   'db'   => 'DATABASE');

    define ('GB_TABLE_NAME', 'GuestBook'); // Change if needed
    
    // =============== END OF NON-PRGRAMMED CONFIGURATION ==================

    mysql_connect($aryDB['host'],$aryDB['user'],$aryDB['pass'])
        or die ('[ERR:'.__LINE__.'] Could not connect to DB Server');

    mysql_select_db($aryDB['db'])
        or die ('[ERR:'.__LINE__.'] Could not access database');

    unset($aryDB); // Clear as there is no need for it.

    $strH2 = '';

    if (isset($_GET['new'])) { // ==== SUBMIT NEW POST =====

        $aryErr = array();
        $strH2 = "Post a New Comment";
        $intParentID = (int)$_GET['new'];

        if (count($_POST)>0) {

            // Clean up POST data, trim any whitespace and if need, get rid of auto slashing
            $bCleanSlash = (boolean)get_magic_quotes_gpc(); // Set this here so not called multiple times below
            foreach($_POST as $key=>$val) {
                if (is_string($val)) {
                    $_POST[$key] = ($bCleanSlash) ? trim(stripslashes($val)) : trim($val);
                }
            }

            // BEGIN: Validate submitted post

                if (!isset($_POST['txtName']) || strlen($_POST['txtName'])<4) {
                    $aryErr['Name'] = 'Name must be at least 4 characters';
                }
                if (!isset($_POST['txtEmail']) || !preg_match('/^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,6}$/i',$_POST['txtEmail'])) {
                    $aryErr['Email'] = 'Invalid E-mail address';
                }
                if (!isset($_POST['txtTitle']) || strlen($_POST['txtTitle'])<4) {
                    $aryErr['Title'] = 'Title must be at least 4 characters';
                }
                if (!isset($_POST['txtMessage']) || strlen($_POST['txtMessage'])<2) {
                    $aryErr['Message'] = 'Message must be at least 2 characters';
                }

            // END: Validate submitted post

            if (count($aryErr)==0) { // No errors, go ahead and write it

                $SQL  = 'INSERT INTO `'.GB_TABLE_NAME.'` SET ';
                $SQL .= '`Name` = "'.mysql_real_escape_string($_POST['txtName']).'",';
                $SQL .= '`Email` = "'.mysql_real_escape_string($_POST['txtEmail']).'",';
                $SQL .= '`Title` = "'.mysql_real_escape_string($_POST['txtTitle']).'",';
                $SQL .= '`Message` = "'.mysql_real_escape_string($_POST['txtMessage']).'",';
                $SQL .= '`ParentID`='.$intParentID.',`Status` = "Pending",`Timestamp` = NOW(),`IP` = "'.$_SERVER['REMOTE_ADDR'].'"';

                $rsWrite = mysql_query($SQL)
                    or die ('[ERR:'.__LINE__.'] Unable to write new record');
                $intID = mysql_insert_id();
                unset($rsWrite);

                // Send e-mail so they can verify email
                $strKey = md5($_POST['txtName'].$_POST['txtEmail']).'x'.dechex($intID);
                $strSite = (($_SERVER['SERVER_PORT']==80)?'http://':'https://').$_SERVER['SERVER_NAME'].$_SERVER['SCRIPT_NAME'];

                $strMsg  = "Your e-mail was used to submite a comment to our guestbook at\n$strSite\n\n";
                $strMsg .= "If this was you, please validate your e-mail by clicking on this link:\n";
                $strMsg .= $strSite.'?validate='.$strKey."\n\n";
                $strMsg .= "If you were not the one who submitted this, do not worry, your\n";
                $strMsg .= "E-mail is NOT published unless you click the above link.\n";


                $strHeader  = 'From: '.GB_FROM_NAME.' <'.GB_FROM_EMAIL.">\r\n";
                $strHeader .= 'Bcc: '.GB_BCC_EMAIL."\r\n";
                $strHeader .= 'X-Mailer: Our Awesome Guestbook v1.0';

                mail($_POST['txtEmail'],'Guestbook Confirmation Required',$strMsg,$strHeader)
                    or die ('[ERR:'.__LINE__.'] Could not send confirmation E-Mail');

                $strNotice  = 'Thank you for submitting your comment. Watch your e-mail for a confirmation link.';
                unset($aryErr);

            } // END: if (No Errors)

        } // END: if (Form was posted)
        else {

            // Set defaults so there are no "undefined index" issues

            $_POST['txtName'] = '';
            $_POST['txtEmail'] = '';
            $_POST['txtTitle'] = '';
            $_POST['txtMessage'] = '';

        } // END: ELSE: if (Form was posted)

        if ($intParentID>0) {
            // Was set to be a reply, so load up orginal Title... (which checks for valid ParentID)

            $SQL  = 'SELECT `Name`,`Title`,`Message`,DATE_FORMAT(`TimeStamp`, "%b %e, %Y @ %h:%i%p") as tsFormat ';
            $SQL .= 'FROM `'.GB_TABLE_NAME.'` WHERE `Status`="Active" AND `ID`='.$intParentID;

            $rsParent = mysql_query($SQL);
            if ($rsParent && mysql_num_rows($rsParent)>0) {
                $aryParent = mysql_fetch_assoc($rsParent);
                $_POST['txtTitle'] = 'RE: '.$aryParent['Title'];
            }
            else {
                $intParentID = 0;
            }
            unset($rsParent);
        }

    } // END: ==== SUBMIT NEW POST =====

    elseif (isset($_GET['validate']) && preg_match('/([0-9a-z]{32})x([0-9a-f]+)/',$_GET['validate'],$regs)) { // ==== VALIDATE A POST ====

        $strH2 = 'Validate Your Comment';
        $intID = hexdec($regs[2]);
        $rsCheck = mysql_query('SELECT MD5(CONCAT(`Name`,`Email`)) AS hash FROM `'.GB_TABLE_NAME.'` WHERE `Status`="Pending" AND `ID`='.$intID);
        if ($rsCheck && mysql_num_rows($rsCheck)>0) {
            if (mysql_result($rsCheck,0,'hash')==$regs[1]) {
                mysql_query('UPDATE `'.GB_TABLE_NAME.'` SET `Status`="Active" WHERE `ID`='.$intID);
            }
            else {
                // Hash passed didn't match database info - we do this so they can't just adjust validate= to hit other rows too
                die ('[ERR:'.__LINE__.'] Invalid link passed, check your e-mail and try again');
            }
        }
        else {
            // There wasn't an ID that was marked Pending
            die ('[ERR:'.__LINE__.'] Could not find this post, may have already been validated');
        }

        $strNotice = 'Thank you for validating your post.';

    } // END: ==== VALIDATE A POST ====

    elseif (isset($_GET['thread']) && (int)$_GET['thread']>0) { // ==== DISPLAY THREADED MESSAGE ====

        $strH2 = 'View Threaded Message';
        $SQL  = 'SELECT `ID`,`Name`,`Title`,`Message`,DATE_FORMAT(`TimeStamp`, "%b %e, %Y @ %h:%i%p") as tsFormat ';
        $SQL .= 'FROM `'.GB_TABLE_NAME.'` WHERE `Status`="Active" AND `ParentID`=0 AND `ID`='.(int)$_GET['thread'];
        $rsPost = mysql_query($SQL);
        if ($rsPost && mysql_num_rows($rsPost)>0) {
            $aryThread = mysql_fetch_assoc($rsPost);
            mysql_freeresult($rsPost);
        }
        else {
            $strNotice = 'Could not find that post.';
        }
        unset($rsPost);

        if (isset($aryThread)) { $aryThread['Children'] = fetchChildren($aryThread['ID']); }


    } // END: ==== DISPLAY THREADED MESSAGE ====

    else { // ==== LOAD UP MESSAGES TO DISPLAY ====

        $strH2 = 'Display All Comments';
        $aryPosts = array(0=>array('Title'=>FALSE));
        $SQL  = 'SELECT `ID`,`ParentID`,`Name`,`Title`,`Message`,DATE_FORMAT(`TimeStamp`, "%b %e, %Y @ %h:%i%p") as tsFormat ';
        $SQL .= 'FROM `'.GB_TABLE_NAME.'` WHERE `Status`="Active" ORDER BY `TimeStamp` DESC';
        $rsPost = mysql_query($SQL);
        if ($rsPost && mysql_num_rows($rsPost)>0) {
            while($aryTemp = mysql_fetch_assoc($rsPost)) {
                $aryTemp['Children'] = FALSE;
                $aryPosts[$aryTemp['ID']] = $aryTemp; // Set key to ID so we can call ParentID's title
            }
            mysql_free_result($rsPost);
        }
        else {
            $strNotice = 'There are currently no commnets.';
        }
        unset($rsPost);
        if (count($aryPosts)==1) {
            unset($aryPosts);
        }
        else {
            foreach($aryPosts as $key=>$aryPost) {
                if ($key>0) {
                    $aryPosts[$aryPost['ParentID']]['Children'] = TRUE;
                    if ($aryPost['ParentID']>0) {
                        $aryPosts[$key]['RootParent'] = getRootParent($aryPost['ParentID']);
                    }
                    else {
                        $aryPosts[$key]['RootParent'] = FALSE;
                    }
                }
            }
        }


    } // END: ==== LOAD UP MESSAGES TO DISPLAY ====

    function getRootParent($intParentID) {
        global $aryPosts;
        if ($aryPosts[$intParentID]['ParentID']==0) {
            return $intParentID;
        }
        else {
            return getRootParent($aryPosts[$intParentID]['ParentID']);
        }
    }

    function echoHSC($strText) {
        echo htmlspecialchars($strText);
    }

    function fetchChildren($intParentID) {
        $aryTree = array();
        $SQL  = 'SELECT `ID`,`ParentID`,`Name`,`Title`,`Message`,DATE_FORMAT(`TimeStamp`, "%b %e, %Y @ %h:%i%p") as tsFormat ';
        $SQL .= 'FROM `'.GB_TABLE_NAME.'` WHERE `Status`="Active" AND `ParentID`='.$intParentID.' ORDER BY `TimeStamp` ASC';

        $rsPost = mysql_query($SQL);
        if ($rsPost && mysql_num_rows($rsPost)>0) {
            while (($aryTemp = mysql_fetch_assoc($rsPost)) && $aryTemp!==FALSE) {
                $aryTree[] = $aryTemp;
            }
            mysql_free_result($rsPost);
        }

        unset($rsPost);
        if (count($aryTree)>0) {
            foreach($aryTree as $key=>$aryBranch) {
                $aryTree[$key]['Children'] = fetchChildren($aryBranch['ID']);
            }
            return $aryTree;
        }
        else {
            return FALSE;
        }
    }

    function displayInput($strField,$strLabel=FALSE) {
        if (!$strLabel) { $strLabel = $strField; }
        echo '<p>';
        echo '<label for="txt',$strField,'">',htmlspecialchars($strLabel),':</label>';
        echo '<input type="text" name="txt',$strField,'" id="txt',$strField,'" value="',htmlspecialchars($_POST['txt'.$strField]),'" />';
        echo '</p>';
    }

    function displayThread($aryBranch,$bLinks=TRUE,$strParentTitle=FALSE) {
        echo '<dl>';
        echo '<dt>',echoHSC($aryBranch['Title']),'</dt>';
        if ($strParentTitle) {
            echo '<dd><em>Reply to: ',htmlspecialchars($strParentTitle),'</em></dd>';
        }
        echo '<dd>',echoHSC($aryBranch['Message']),'</dd>';
        echo '<dd>Posted By: ',echoHSC($aryBranch['Name']),'</dd>';
        echo '<dd>Posted On: ',echoHSC($aryBranch['tsFormat']),'</dd>';
        if ($bLinks) {
            echo '<dd>';
            if (isset($aryBranch['RootParent']) && $aryBranch['RootParent'] >0) {
                echo '<a href="',$_SERVER['SCRIPT_NAME'],'?thread=',$aryBranch['RootParent'],'">View Thread</a> | ';
            }
            elseif (!$strParentTitle && $aryBranch['Children']===TRUE) {
                echo '<a href="',$_SERVER['SCRIPT_NAME'],'?thread=',$aryBranch['ID'],'">View Thread</a> | ';
            }
            echo '<a href="',$_SERVER['SCRIPT_NAME'],'?new=',$aryBranch['ID'],'">Reply</a>';
            echo '</dd>';
        }
        if (isset($aryBranch['Children']) && is_array($aryBranch['Children'])) {
            foreach($aryBranch['Children'] as $aryChild) {
                echo '<dd>',displayThread($aryChild),'</dd>';
            }
        }
        echo "</dl>\n";
    }

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us" dir="ltr">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Our Little Guestbook</title>
    <style type="text/css">
        #errors { color: #f00; }
        #errors p, dt { font-weight: 900; }
    </style>
</head>
<body>
    <h1>Out Little GuestBook</h1>
    <h2><?php echoHSC($strH2); ?></h2>
    <hr />
    <?php if (isset($aryErr)): // == POST A NEW COMMENT == ?>

        <?php if ($intParentID>0): ?>
            <p>You are replying to this message:</p>
            <?php displayThread($aryParent,FALSE); ?>
        <?php endif; // if($intParentID>0) ?>

        <?php if(count($aryErr)>0): ?>
            <div id="errors">
                <p>We found the following issue(s):</p>
                <ul>
                    <?php foreach($aryErr as $strMessage) { echo '<li>',$strMessage,"</li>\n"; } ?>
                </ul>
            </div>
        <?php endif; ?>

        <form method="post" action="<?php echo $_SERVER['SCRIPT_NAME'],'?new=',$intParentID; ?>" id="frmComment">
            <?php displayInput('Name'); ?>
            <?php displayInput('Email','E-Mail'); ?>
            <?php displayInput('Title'); ?>
            <p>
                <label for="txtMessage">Comment:</label>
                <textarea cols="40" rows="5" name="txtMessage" id="txtMessage"><?php echoHSC($_POST['txtMessage']); ?></textarea>
            </p>
            <p><input type="submit" name="submit" value="Post Comment" />  [<a href="<?php echo $_SERVER['SCRIPT_NAME']; ?>">Cancel</a>]</p>
        </form>

    <?php elseif (isset($aryThread)): // == DISPLAY THREADED MESSAGE == ?>

        <?php displayThread($aryThread); ?>
        <p>Return to the <a href="<?php echo $_SERVER['SCRIPT_NAME']; ?>">main listing</a>.</p>

    <?php elseif (isset($aryPosts)): // == DISPLAY ALL POSTS == ?>

        <a href="<?php echo $_SERVER['SCRIPT_NAME']; ?>?new=0">Post New Comment</a>

        <?php foreach($aryPosts as $key=>$aryPost): ?>
            <?php if ($key>0) { displayThread($aryPost,TRUE,$aryPosts[$aryPost['ParentID']]['Title']); } ?>
        <?php endforeach; ?>

    <?php else: // == DISPLAY MESSAGE ?>

        <p><?php echoHSC($strNotice); ?></p><p>Return to the <a href="<?php echo $_SERVER['SCRIPT_NAME']; ?>">main listing</a>.</p>

    <?php endif; ?>

</body>

</html>