If using an SQL database, things get much more complicated as you need to learn SQL, a server side language like PHP or Perl. How to connect to the database and design of a database schema
<?php
//
// select guestbook entries and return
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
/*
// old format
include( './pw/db0.php' );
$chan = mysql_connect ($db_host, $username, $password);
mysql_select_db ($DB_name, $chan);
//$resultid = mysql_query ("select * from GB order by postedOn desc ", $chan);
$resultid = mysql_query ("select * from GB order by postedOn ", $chan);
*/
// include passwords and connection details from a file elsewhere.
include( './pw/db0.php' );
$mysqli = new mysqli($db_host, $username, $password, $DB_name);
if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); }
/* fetch data */
$uresult = $mysqli->query( " select * from GB order by postedOn desc " , MYSQLI_USE_RESULT);
?>
<?php
function escp ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to // $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'", "'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&", "<", ">" );
// Uses regular expressions to remove
return preg_replace ($search, $replace, $ss );
}
/*
while ( $row = mysql_fetch_assoc( $resultid ) ) {
//echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".escp($row["name"])."','".escp($row["email"])."',".
"'".escp($row["postedOn"])."','".escp($row["IPaddress"])."',".
"'".escp($row["userfield1"])."','".escp($row["userfield2"])."',".
"'".escp($row["userfield3"])."','".escp($row["userfield4"])."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
*/
if ($uresult) {
while ($row = $uresult->fetch_array()) {
//echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".escp($row["name"])."','".escp($row["email"])."',".
"'".escp($row["postedOn"])."','".escp($row["IPaddress"])."',".
"'".escp($row["userfield1"])."','".escp($row["userfield2"])."',".
"'".escp($row["userfield3"])."','".escp($row["userfield4"])."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
}
?>
The guest book script accepts a post from a form and appends the data onto the end of a database table.
It is possible to query the database and the client gets a list of javascript function calls populated with the data from the database. It is up to you to write the guts of the function to format as you wish.
Download PHP scripts: dbPHP.zip
Form example:
http://ccgi.dougrice.plus.com/db0601/db8.php
Action: <form name="F1" action="http://ccgi.dougrice.plus.com/db0601/db6.php" method="POST" >
http://ccgi.dougrice.plus.com/db0601/db6.php
Returns data in JavaScript gbF() format:
http://ccgi.dougrice.plus.com/db0601/db9.php
Webpage that includes db9.php:
gbook_db9.htm
Returns data in Table format:
http://ccgi.dougrice.plus.com/db0601/db7.php
Include the javascript into your web page:
<script language = "JavaScript" type ="text/javascript"
src ="http://ccgi.dougrice.plus.com/db0601/db9.php?par" >
</script>
Here is an example web page: gbook_db9.htm
<html><body>
<script>
opStr ="";
// The function below is called once per guestbook entry.
// You write this function to do what you want!!
function gbF(
name,email,postedOn,IPaddress,userfield1,userfield2,userfield3,userfield4,comments){
// You do what you want here
opStr += postedOn +"<BR>\n"
}
// test call
gbF( "Test Call","email","15 Nov 2003","IPaddress","userfield1","userfield2","userfield3","userfield4","Testcomments")
</script>
<!-- Include remote guestbook data-->
<!--
<script language = "JavaScript" type =
"text/javascript"
src =
"http://ccgi.dougrice.plus.com/gb/gbookFXXX.js" >
</script>
-->
<!-- Include remote guestbook that runs a PHP script to
query the database -->
<script language = "JavaScript" type =
"text/javascript"
src =
"http://ccgi.dougrice.plus.com/db0601/db9.php?par" >
</script>
<script> self.document.write( opStr )
</script>
</body></html>
You can also read the javascript downloaded
http://ccgi.dougrice.plus.com/db0601/db9.php?par
And you get JavaScript:
//par//<BR>
// XXX //<BR>
gbF( 'Doug ','email','2007-10-29 07:06:24','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Mon Oct 29 07:06:03 UTC 2007|A test post' ); //<BR>
// XXX //<BR>
gbF( 'name','email','2007-10-28 17:57:53','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 17:57:39 UTC 2007|' ); //<BR>
// Account //<BR>
gbF( 'Doug','email','2007-10-28 08:33:33','212.56.108.219 | dougrice.plus.com','userfield1 - test','userfield2 - uf2','userfield3 - uf3','userfield4 - uf4','uf5 Sun Oct 28 08:32:00 UTC 2007|Here is a test.' ); //<BR>
// XXX //<BR>
gbF( 'Doug','email','2007-10-28 08:32:02','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 08:32:00 UTC 2007|' ); //<BR>
This has not been made secure yet but is a attempt to fetch the guestbook from a MySQL database.
Insert guest book entries with SQL like:
-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
--
--------------------------------------------------------
--
-- Table structure for table `GB`
--
CREATE TABLE `GB` (
`guestbook` text,
`name` text,
`email` text,
`postedOn` text,
`IPaddress` text,
`userfield1` text,
`userfield2` text,
`userfield3` text,
`userfield4` text,
`comments` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `GB`
--
INSERT INTO `GB` (`guestbook`, `name`, `email`,
`postedOn`, `IPaddress`, `userfield1`, `userfield2`, `userfield3`,
`userfield4`, `comments`) VALUES
('guestbookname', 'CRtest', 'email', now() ,
'212.56.108.219 | dougrice.plus.com', 'userfield1', '4', 'userfield3',
'userfield4', 'uf5 Tue Oct 23 08:24:05 UTC+0100 2007|Another test 1 " ')
INSERT INTO `GB` (`guestbook`, `name`, `email`,
`postedOn`, `IPaddress`, `userfield1`, `userfield2`, `userfield3`,
`userfield4`, `comments`) VALUES
('guestbookname', 'CRtest', 'email', '2007-10-27
16:58:28', '212.56.108.219 | dougrice.plus.com', 'userfield1', '4',
'userfield3', 'userfield4', 'uf5 Tue Oct 23 08:24:05 UTC+0100 2007|Another
test 1 " '),
('XXX', 'name', 'email', '2007-10-28 17:57:53',
'212.56.108.219 | dougrice.plus.com', 'userfield1', 'userfield2',
'userfield3', 'userfield4', 'uf5 Sun Oct 28 17:57:39 UTC 2007|'),
('XXX', 'Doug ', 'email', '2007-10-29 07:06:24',
'212.56.108.219 | dougrice.plus.com', 'userfield1', 'userfield2',
'userfield3', 'userfield4', 'uf5 Mon Oct 29 07:06:03 UTC 2007|A test post');
<?php
//
// select guestbook entries and return
// <script src=db9.php></script>
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
// Pick up database login details
include( './pw/db0.php' );
echo "//".$QUERY_STRING."//<BR>\n";
$chan = mysql_connect ($db_host, $username, $password);
mysql_select_db ($DB_name, $chan);
$resultid = mysql_query ("select * from GB order by postedOn desc ", $chan);
$infoStr = mysql_info();
function escp ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to
// $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'",
"'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&",
"<", ">" );
// Uses regular expressions to remove
return preg_replace ($search, $replace, $ss );
}
while ( $row = mysql_fetch_assoc( $resultid ) ) {
echo " // ".$row["guestbook"] ." //<BR>\n";
echo " gbF( '".$row["name"]."','".$row["email"].
"','".$row["postedOn"]."','".$row["IPaddress"]."',".
"'".$row["userfield1"]."','".$row["userfield2"]."',".
"'".$row["userfield3"]."','".$row["userfield4"]."',".
"'".escp($row["comments"])."' ); //<BR>\n";
}
?>
<script language = "JavaScript" type =
"text/javascript"
src = "http://ccgi.dougrice.plus.com/db0601/db9.php?par" >
</script>
Each row is supplied:
// XXX //<BR>
gbF( 'Doug','email','2007-10-28 08:32:02','212.56.108.219 | dougrice.plus.com','userfield1','userfield2','userfield3','userfield4','uf5 Sun Oct 28 08:32:00 UTC 2007|' ); //<BR>
<script language="php">
// db6.php - Guestbook script the saves the data wrapped in JavaScript function calls and inserts into db
// copyright 2007 douglas rice.
// This script takes the data from the form fields.
// and wraps it in a JavaScript Function Call:
// gbF( "name","email",postedOn,IPaddress,"userfield1","userfield2","userfield3","userfield4","comments", ).
// After appending the data the URL in form field: nextpage replaces the page.
//
// This uses the same form fields as the BTOpenWorld Guestbook.
//
ini_set ( "always_populate_raw_post_data","true") ;
function esc ( $s ) {
// escape characters that are code to Java Script, and HTML
// PHP escapes " and ' by default, so no need for call to
// $ss = addslashes ( $s );
$ss = $s;
// //CRLF and LF //HTML specials
$search = array ( "'\r'", "'\n'", "'&'",
"'<'", "'>'" );
$replace = array ( "\\r\\\r", "", "&",
"<", ">" );
// Uses regular expressions to remove
$result = preg_replace ($search, $replace, $ss );
// The "i" after the pattern delimiter indicates a case-insensitive search
if (preg_match("/alprazolam|url=/i", $result))
{
$result = "url=spam ";
} else {
$result = preg_replace ($search, $replace, $ss );
}
return $result;
}
$userfield5 = $_REQUEST["userfield5"];
$guestbook = $_REQUEST["guestbook" ];
$name = $_REQUEST["name"];
$email = $_REQUEST["email"];
$userfield1 = $_REQUEST["userfield1"];
$userfield2 = $_REQUEST["userfield2"];
$userfield3 = $_REQUEST["userfield3"];
$userfield4 = $_REQUEST["userfield4"];
$comments = $_REQUEST["comments"];
$nextpage = $_REQUEST["nextpage"];
$date=gmstrftime ("%d %b %Y %H:%M:%S");
//$HTTP_RAW_POST_DATA);
//file_put_contents("php://filter/write=string.rot13/resource=gbOP.txt","--------------");
$filename = 'gbOP.txt';
// Let's make sure the file exists and is writable first.
if (is_writable($filename)) {
// In our example we're opening $filename in append mode.
// The file pointer is at the bottom of the file hence
// that's where $somecontent will go when we fwrite() it.
if (!$handle = fopen($filename, 'a')) {
echo "Cannot open file ($filename)";
exit;
}
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
if (fwrite($handle, "\n---------------- "
.$date. "--" .$_SERVER['REMOTE_ADDR']. "--" .$hostname. "--\n " ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
foreach ($_REQUEST as $key => $value ) {
// Write $somecontent to our opened file.
if (fwrite($handle, $key."=".$value."\n" ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
}
if (fwrite($handle, "\n------------------------ ENV ------------------------\n " ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
$spam = false;
foreach ($_ENV as $key => $value ) {
// Write $somecontent to our opened file.
if (fwrite($handle, $key."=".$value."\n" ) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
if (preg_match("/compatible; MSIE 6\.0; Windows NT 5\.1/i", $result)) {
$spam = true;
}
}
// Write $somecontent to our opened file.
// if (fwrite($handle, $HTTP_RAW_POST_DATA."\n".$HTTP_POST_VARS ) === FALSE) {
// echo "Cannot write to file ($filename)";
// exit;
// }
//echo "Success, wrote ($somecontent) to file ($filename)";
fclose($handle);
} else {
echo "The file $filename is not writable";
}
$date=gmstrftime ("%d %b %Y %H:%M:%S");
if ( $spam == false ) {
// append to the file gbookF + the name specified by $guestbook + .js
$fp = fopen ("./gbookF$guestbook.js", "a+b");
fputs($fp,"// HTTP_USER_AGENT:".$_ENV["HTTP_USER_AGENT"]."\n");
fputs($fp,"// QUERY_STRING:".$_ENV["QUERY_STRING"]."\n");
//fputs($fp,"// HTTP_REFERER:".$_ENV["HTTP_REFERER"]."\n");
fputs($fp,"gbF( //guestbook.php ".esc( $userfield5 )."\n");
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
fputs($fp,"'".esc( $name )."',\n'".esc( $email )."',\n '$date',\n '".
getenv ("REMOTE_ADDR" )." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n");
// output the 4 user fields and comments
fputs($fp,"'".esc( $userfield1 )."',\n'".esc( $userfield2 )."',\n");
fputs($fp,"'".esc( $userfield3 )."',\n'".esc( $userfield4 )."',\n");
fputs($fp,"'".esc( $userfield5 )."|".esc( $comments )."'\n");
fputs($fp,");\n");
fclose($fp);
$db_host = "host_name_here";
$username = "userid_here";
$password = "your_password_here";
$DB_name = "your_dbname_here";
// Pick up database login details
include( './pw/db0.php' );
$chan = mysql_connect ($db_host, $username,
$password);
mysql_select_db ($DB_name, $chan);
$sqlStr =
"INSERT INTO GB VALUES " .
// fputs($fp,"// HTTP_USER_AGENT:".$_ENV["HTTP_USER_AGENT"]."\n");
// fputs($fp,"// QUERY_STRING:".$_ENV["QUERY_STRING"]."\n");
// //fputs($fp,"// HTTP_REFERER:".$_ENV["HTTP_REFERER"]."\n");
// fputs($fp,"gbF( //guestbook.php ".esc( $userfield5 )."\n");
// $hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
"( ".
"'".esc( $guestbook )."',".
// "'".esc( $name )."',\n '".esc( $email )."',\n '$date',\n '".getenv ( "REMOTE_ADDR")." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n".
"'".esc( $name )."',\n '".esc( $email )."',\nnow(),\n '".
getenv ( "REMOTE_ADDR" )." ".getenv ( "HTTP_X_FORWARDED_FOR" )." | ".esc($hostname)."',\n".
// output the 4 user fields and comments
"'".esc( $userfield1 )."',\n'".esc( $userfield2 )."',\n".
"'".esc( $userfield3 )."',\n'".esc( $userfield4 )."',\n".
"'".esc( $userfield5 )."|".esc( $comments )."'\n ); " ;
// echo( $sqlStr ."<BR>\n" );
$resultid = mysql_query ( $sqlStr , $chan);
$infoStr = mysql_info() . " - affected rows: " . mysql_affected_rows();
// echo( "insert result:".$resultid." " . $infoStr . "<P>");
// echo "<br>";
}
// reload the page with the URL provided in next page.
header("Location: $nextpage\n\n\n" );
exit;
</script>