Guestbook Script using MySQL as a backend.

CSC · Jan 15, 2001

Greetings:

I am experiencing a coding block, you know, like a writer's block.

I am expressing my query to this group in the simplest terms that I can gather together at this point after spending the last five weeks (yes, that's 5 weeks!) writing incorrect code to solve an objective, and searching the I-net for a clue. The simple fact is, if I had the script for this Tek-Tips posting (message) board application that we are using now, it would solve my objective... that is, if it is using a database backend.

A clarification for the record: I am just finishing up my UIUC web engineering certification course and this is a one of the final projects (4 easier objectives to go... I peeked) that I must pass to get my certification. I found this site yesterday and read pages and pages of postings to try to glean a snippet of code. I only found one posting that was trying to solve a similar challenge in the SQL forum. This research is part and parcel of the course, so the ethics involved in asking a group like this for assistance is ethical and expected from the University. In the last 20 months of the course, this is this first time that I have really hit the wall.

The hardware, software involved: Unix/Linux servers, running Apache (latest non beta release), SQL servers running the latest full MySQL backend and anything that I can code in HTML, DHTML, JavaScript, PERL, UNIX, CGI, DBD and DBI to access the database and extract the data in a described manner. PHP and SSI are NOT allowed in this objective.

Here is the objective. Write a guest book / address book (not a chat app.) using an HTML form to gather user info, pre-parse the info in Perl and enter the fields using CGI, DBD and DBI into a MySQL pipe delimited database that I designed, retrieve the info from the database table (only one table is involved by design, so there are no joins with which to contend) using Perl and embedded HTML to output 10 rows (records) at a time in at least two browsers... I chose MSIE55 and NN47. An href to the individual pages is allowed. The primary key to the table is an auto_increment field table_id beginning at 1, not 0. All the code is
delivered to the user's browser server side and only the HTML form client side. I used the post method in the HTML form. I have completed all of the coding for this objective weeks ago except for the algorithm to show 10 records at a time, then the next 10, the next 10 and so on to the end of the record set AND to be able to go back to the previous 10 records, the next 10, the next 10 and so on to the beginning of the record set.

I am not worried about the aesthetics of the output right now, that is a matter of tweaking the HTML to show alternate rows of color or create a template to show each record in an HTML box... the choices are endless. I can use a header and/or footer script, you get the idea.

Here is one of many scripts in which I tried to use JavaScript and then I decided to try an href in the number 2 position. None work.

This script is one that lists all the records in three separate tables. The first flaw in the script, among many others, is that I decided to delete record #3 in the table and the objective was immediatley lost because the output table only lists 9 rows. So, you can see that I am performing a select based on the auto_increment function (table_id) of the MySQL table. This, I realized, is not a good thing.

I am including the run_statement sub here for reference since many of the select statements are using this subroutine, and it variations, and I think this is where the code starts to break down because I do not have a name reference to call the table as an href or a JavaScript path. I have many variations of this sub to make separate calls for each select, but they don't work either.

##############################
#sub run_statement {
# $stmt = "$_[0]";
# $sth = $dbh->prepare($stmt);
# $sth->execute;
# }
###############################

Here then, is one of many of my scripts, and it is not necessarily the best one. Notice that I have not included the embedded HTML/JavaScript code for the output tables.

Any help will be much appreciated.

#!/usr/bin/perl

require "dbi-lib.pl";

#--------------------#
# User Variables #
#--------------------#

$table_name = "sqlguestbook";

#--------------------#
# Main body #
#--------------------#

&parse_input;
&print_header;
&initialize_dbi;
&nice_table_1;
&nice_table_2;
&nice_table_3;
exit;

# The Functions #

sub nice_table_3 {

print "<center>";
print "<body bgcolor=#EDF4FE><br>";
print "<font size=+2 face=arial,helvetica,sans-serif color=#00008B> The MySQL Guestbook

</font><br><br>";
print "<table border=1 bgcolor=#F0FFF0 bordercolor=#00008B>\n";
print "<tr><td>Number</td>";
print "<td>First Name</td>";
print "<td>Second Name</td>";
print "<td>The Date</td>";
print "<td>E-Mail</td>";
print "<td>Message</td></tr>";

&run_statement("select table_id, first_name, second_name, date,
email, message from $table_name where table_id >20 and table_id <= 30;&quot

;

while (($table_id, $first_name, $second_name, $date, email, $message) = $sth->fetchrow)
{

print "<tr><td>$table_id</td>";
print "<td>$first_name</td>";
print "<td>$second_name</td>";
print "<td>$date</td>";
print "<td>$email</td>";
print "<td>$message</td></tr>\n";
}

print "</table>";
print "</body>";
print "</center>";
}

sub nice_table_2 {

print "<center>";
print "<body bgcolor=#EDF4FE><br>";
print "<font size=+2 face=arial,helvetica,sans-serif color=#00008B> The MySQL Guestbook

</font><br><br>";
print "<table border=1 bgcolor=#F0FFF0 bordercolor=#00008B>\n";
print "<tr><td>Number</td>";
print "<td>First Name</td>";
print "<td>Second Name</td>";
print "<td>The Date</td>";
print "<td>E-Mail</td>";
print "<td>Message</td></tr>";

&run_statement("select table_id, first_name, second_name, date,
email, message from $table_name where table_id >10 and table_id <= 20;&quot

;

while (($table_id, $first_name, $second_name, $date,
$email, $message) = $sth->fetchrow)
{

print "<tr><td>$table_id</td>";
print "<td>$first_name</td>";
print "<td>$second_name</td>";
print "<td>$date</td>";
print "<td>$email</td>";
print "<td>$message</td></tr>\n";
}

print "</table>";
print "</body>";
print "</center>";
}

sub nice_table_1 {

print "<center>";
print "<body bgcolor=#EDF4FE><br>";
print "<font size=+2 face=arial,helvetica,sans-serif color=#00008B> The MySQL Guestbook

</font><br><br>";
print "<table border=1 bgcolor=#F0FFF0 bordercolor=#00008B>\n";
print "<tr><td>Number</td>";
print "<td>First Name</td>";
print "<td>Second Name</td>";
print "<td>The Date</td>";
print "<td>E-Mail</td>";
print "<td>Message</td></tr>";

&run_statement("select table_id, first_name, second_name, date,
email, message from $table_name where table_id <=10;&quot

;

while (($table_id, $first_name, $second_name, $date,
$email, $message) = $sth->fetchrow)
{

print "<tr><td>$table_id</td>";
print "<td>$first_name</td>";
print "<td>$second_name</td>";
print "<td>$date</td>";
print "<td>$email</td>";
print "<td>$message</td></tr>\n";
}

print "</table>";
print "</body>";
print "</center>";
}

goBoating · Jan 16, 2001

Good Morning CSC,
I think you are making this way to difficult. If I understand, you want to send records at a time to a web browser with the ability from each page of 10 recs to go to the next 10 records. If so, then I would simply build into the output a button or link that pointed to the next 10 recs and make the CGI smart enough to catch that request and parse the record 'group'. Then, make the database call for the appropriate record group and send it to the browser with the associated links to the NEXT record group.

General Flow:

1 - call database for first record set.
2 - present first record set with a link to a call for the second record set.
3 - present second set with a links to the previous set and the next set
4 - and so on....

You just have to keep track, in each page of output, which set is current. Then, the next set is current + 1 and the previous set is current - 1. The code you posted is close to doing this. No jscript needed.

'hope this helps...

keep the rudder amid ship and beware the odd typo

CSC · Jan 25, 2001

Greetings goBoating:

Thanks for your encouragement. I cleaned my orginal code above to about half using nested subs. I haven't solved the problem yet, but there was an excellent posting back about about three or four pages in which you are participating. Perhaps I will use portions of that code "passing references" from hashes to help solve this vexing problem.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Guestbook Script using MySQL as a backend.

CSC

Programmer

goBoating

Programmer

CSC

Programmer

Similar threads

Part and Inventory Search

Sponsor