Guest Book with Oracle database paging display function

Most websites take into account the interaction with users. At this time, with the message version of the function, allows users to stay here, or some of the interaction with the site information.
In the design, it can be very simple to leave only the user's short message, can also be designed to classify the nature of a very complex Web BBS system. Of course, how to create a message version of their own website, on the nature of the site and Web site developers ingenuity.

The example presented here is a simple listing of all the messages. Users can see multiple messages at a time. The back-end message of the system is the Oracle 7.x version of the database system. The database name in the example is WWW, the user account for the connection is user38 and the password is iam3849. To use this example directly, you must first execute the following SQL instructions and create a guestbook table.

CREATE TABLE Guestbook (
Serial VARCHAR2 (255) NOT NULL,
Ref VARCHAR2 (255) NULL,
ID char (8) NOT NULL,
Alias VARCHAR2 () not NULL,
IP varchar2 (1024) NULL,
Msgdate date NOT NULL,
Email VARCHAR2 (1024) NULL,
MSG varchar2 NOT NULL,
Flag char (1) Default 1,
Primary key (serial)
);

The above SQL field descriptions and detailed information are shown in the following table

Ordinal field name data type length field description limit Key
0 serial number serial VARCHAR2 255 NN PK
1 Reference serial number ref VARCHAR2 255 temporary Reservation

Message-Answering function with

2 Account ID char 8 user account NN

3 Anonymous alias varchar2 32 display name NN

4 URL IP varchar2 1024 internet IP

5 Time Msgdate Date NN

6 Email Email varchar2 1024

7 message content msg VARCHAR2 NN

8 display flag Flag Char 1 0: Do not display

1: Show (default)

In this section of the message version of the program, if you add user authentication function, you can leave the user's authentication account in the Guestbook data sheet to facilitate webmaster to find the wrong sender in the future. Leave the field here first and let the readers in need practice.

To use the program in this section, first install Oracle 7.x, and make sure that the sql*net on the Web Server side can successfully connect to the Oracle repository. And then, when compiling PHP, add

The--with-oracle=/home/oracle/product/7.3.2 option, of course, can be changed to other paths, as long as the path is really Oracle's path. Please refer to the relevant books for details on Oracle installation and usage.

The following procedure is to add the user's message to the guestbook message datasheet. To set the user authentication function, you can check at the beginning of the program, send a message can confirm the identity, and read the message does not have to identity check. This setting can prevent improper speech, but will not let the message function only a few people use.

file://--------------------------- Add Message program addmsg.php Author:wilson Peng Copyright (C) 2000 file://--------------------------- // You can add the identity check function here // if (($alias!= "") and ($msg!= "")) { Putenv ("oracle_sid=www"); Putenv ("Nls_lang=american_taiwan.zht16big5"); Putenv ("oracle_home=/home/oracle/product/7.3.2"); Putenv ("Ld_library_path=/home/oracle/product/7.3.2/lib"); Putenv ("Ora_nls=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); Putenv ("Ora_nls32=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); $handle =ora_logon ("User38@www", "iam3849") or die; $cursor =ora_open ($handle); Ora_commitoff ($handle); $serial =md5 (rand ()) (uniqid); $ref = ""; $id = $PHP _auth_user; $ip = $REMOTE _addr; $msg =base64_encode ($msg); $flag = "1"; $query = "INSERT into guestbook (serial, ref, ID, alias, IP, Msgdate, email, MSG, flag) VALUES (' $serial ', ' $ref ', ' $id ', ' $alias ', ' $ip ', Sysdate, ' $email ', ' $msg ', ' $flag '); Ora_parse ($cursor, $query) or die; Ora_exec ($cursor); Ora_close ($cursor); Ora_logoff ($handle); Header ("Location:./index.php"); Exit } else { ? > fill in the message code nickname e-mail Content cols=33> } ? >

The above program in the implementation of the first check variables alias and msg whether there is information, if there is no information sent to fill out the message form to the user side, for users to fill out the message.

If the user fills in the message, presses the "Send out Message" button, then executes the first half of the program.

The program is probably divided into five parts.

1. Set the environment variables that Oracle needs
2. Connect to Oracle Database
3. Organize data into Oracle
4. End connection to Oracle
5. Close the program, display the latest message information

In setting the Oracle environment, use PHP's function putenv () to set the environment variables for the operating system layer. To use Chinese, remember to join the following line

Putenv ("Nls_lang=american_taiwan.zht16big5");

The functions of the Oracle function library are then used: Ora_logon () and so on. See Oracle database function library for details. With this function library, Oracle repositories can be easily manipulated.

Again, it's about organizing the data to be placed in the Oracle repository.

$serial =md5 (rand ()) (uniqid); $ref = ""; $id = $PHP _auth_user; $ip = $REMOTE _addr; $msg =base64_encode ($msg); $flag = "1"; $query = "INSERT into guestbook (serial, ref, ID, alias, IP, Msgdate, Email, MSG, flag) VALUES (' $serial ', ' $ref ', ' $id ', ' $alias ', ' $ip ', sysdate, ' $email ', ' $msg ', ' $flag ');

$serial variable is a unique string, the program first chaos number to produce a unique string, and then use MD5 encoding, the string chaos, form a hash-like processing after the meaningless string. Because the string length, and become very messy, can prevent users, especially hackers or visitors using serial number to stamp the system.

The $REF variable is currently invalid. $id variables for the user authentication, if the program at the beginning of the user certification to join the program, then $PHP _auth_user will become the user's account, passed into $id variables.

As for the user-written string, to prevent the complexity of the database or processing, it is BASE64 encoded. Can let the text of the strange characters disappear, of course, this is the saw arrows, but for the WEB program, the implementation of fast, easy to modify is the most important, there is no need to waste energy to deal with these Chinese punching code problem. It is worth noting that the use of BASE64 encoding, will allow the string expansion of about 1/3, if the database storage space is limited, may not be suitable for this method, and then said back, now the hard disk is cheap, casually is more than 10 GB to
, you should not consider the problem of limited database space.

Finally, the variables are sorted into $query strings for use by the database to execute the SQL instructions.

Ora_parse ($cursor, $query) or die;
Ora_exec ($cursor);
Ora_close ($cursor);
Ora_logoff ($handle);

Before you can execute an Oracle SQL directive, go through the parse action. If you precede with an @ (for example, @ora_prase ();), you can keep the user from seeing the error message. After you execute the query command, you can close the connection to Oracle.

Header ("Location:./index.php");
Exit

These two lines allow the browser to redirect to index.php. Let the user see his new message, the completion of the message action.

Then take a look at the message content display program.

http-equiv=content-type> Message Board file://--------------------------- Message Display program index.php Author:wilson Peng Copyright (C) 2000 file://--------------------------- $WebmasterIPArray = Ay "10.0.1.30",//management personnel a machine IP "10.0.2.28"//management personnel B's machine IP ); $WebmasterIP =false; for ($i =0; $i if ($REMOTE _addr = = $WebmasterIPArray [$i]) $WebmasterIP =true; } Putenv ("oracle_sid=www"); Putenv ("Nls_lang=american_taiwan.zht16big5"); Putenv ("oracle_home=/home/oracle/product/7.3.2"); Putenv ("Ld_library_path=/home/oracle/product/7.3.2/lib"); Putenv ("Ora_nls=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); Putenv ("Ora_nls32=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); $handle =ora_logon ("User38@www", "iam3849") or die; $cursor =ora_open ($handle); Ora_commitoff ($handle); $query = "Select serial, ref, ID, alias, IP, To_char (msgdate, ' yyyy/mm/dd hh:mi:ss '), email, msg from guestbook where flag= ' 1 ' ORDER by msgdate Desc '; Ora_parse ($cursor, $query) or die; Ora_exec ($cursor); $i = 0; while (Ora_fetch ($cursor)) { $guestbook [$i][0] = ora_getcolumn ($cursor, 0); $guestbook [$i][1] = Ora_getcolumn ($cursor, 1); $guestbook [$i][2] = Ora_getcolumn ($cursor, 2); $guestbook [$i][3] = Ora_getcolumn ($cursor, 3); $gu estbook[$i][4] = Ora_getcolumn ($cursor, 4); $guestbook [$i][5] = Ora_getcolumn ($cursor, 5); $guestbook [$i][6] = Ora_getcolumn ($cursor, 6); $guestbook [$i][7] = Ora_getcolumn ($cursor, 7); $i + +; } Ora_close ($cursor); Ora_logoff ($handle); echo " new Message .... \ n "; if ($QUERY _string!= "") $page = $QUERY _string; } else $page = 0; } $i =count ($guestbook); $msgnum = 20; 20 pens per page $start = $page * $MSGNUM; $end = $start + $msgnum; if ($end > $i) $end = $i; $totalpage = $i/$msgnum; $pagestr = ""; if ($page >0) $pagestr = $pagestr. " Href=index.php? ". ($page-1). " >< on page -"; $pagestr = $pagestr. " [subsection]; for ($i =0; $i $totalpage; $i + +) if ($i!= $page) $pagestr = $pagestr. " "; } else $pagestr = $pagestr. ($i + 1). ""; } } $pagestr = $pagestr. "Page]"; if ($page ($totalpage-1)) $pagestr = $pagestr. " - Href=index.php? ". ($page + 1). " > next page, ; $pagestr = " $pagestr "; echo " ". $pagestr. " \ n "; for ($i = $start; $i $end; $i + +) echo " \ n"; echo " \n". $guestbook [$i][5]. " "; if ($guestbook [$i][6]!= "") echo Href=mailto: ". $guestbook [$i][6]." > "; echo " ". $guestbook [$i][3]. " "; if ($guestbook [$i][6]!= "") echo ""; echo " \ n"; if ($WebmasterIP) echo " Href=erase.php? ". $guestbook [$i][0]. " > > delete this article!! (". $guestbook [$i][2].") "; echo " from: ". $guestbook [$i][4]." \ n "; $msg =base64_decode ($guestbook [$i][7]); $msg =nl2br ($msg); Echo $msg; echo " \ n"; } echo " \ n"; Echo $pagestr; ? >

In the part that shows the message, considering the content of the message if a lot, plus the network slow, it may allow users to slow down the entire database when the line, so, as soon as possible to connect the database, to obtain the necessary information, immediately close the database, and then slowly to the user, should be the best countermeasures

The procedure is divided into four parts

1. Initialize

2. Take the data from the database

3. Calculate the number of pages to display

4. Send out information

This program is implemented on the Biglobe, because this is an ISP company, so in the set up to limit the link or dedicated members to see, enter the account and password before entering. In order to protect the privacy of the message, the message to the mosaic processing.

$WebmasterIPArray = Array ( "10.0.1.30",//management personnel a machine IP "10.0.2.28"//management personnel B's machine IP ); $WebmasterIP =false; for ($i =0; $i if ($REMOTE _addr = = $WebmasterIPArray [$i]) $WebmasterIP =true; } Then initialize the Oracle program slightly

The display program and the message program initialization part of the same, but the display program added a function, set Webmaster computer. Webmaster Use of IP address added to the $WebmasterIPArray array variables, you can display messages, display delete message string, easy to handle improper message.

$handle =ora_logon ("User38@www", "iam3849") or die; $cursor =ora_open ($handle); Ora_commitoff ($handle); $query = "Select serial, ref, ID, alias, IP, To_char (msgdate, ' Yyyy/mm/dd hh:mi:ss '), email, msg from guestbook where flag= ' 1 ' Msgdate desc "; Ora_parse ($cursor, $query) or die; Ora_exec ($cursor); $i = 0; while (Ora_fetch ($cursor)) { $guestbook [$i][0] = ora_getcolumn ($cursor, 0); $guestbook [$i][1] = Ora_getcolumn ($cursor, 1); $guestbook [$i][2] = Ora_getcolumn ($cursor, 2); $guestbook [$i][3] = Ora_getcolumn ($cursor, 3); $guestbook [$i][4] = Ora_getcolumn ($cursor, 4); $guestbook [$i][5] = Ora_getcolumn ($cursor, 5); $guestbook [$i][6] = Ora_getcolumn ($cursor, 6); $guestbook [$i][7] = Ora_getcolumn ($cursor, 7); $i + +; } Ora_close ($cursor); Ora_logoff ($handle);

After initialization, you can connect to the Oracle database and put the message out in $guestbook array. Once you get the information, close the database and process the data $guestbook the array.

if ($QUERY _string!= "") { $page = $QUERY _string; } else { $page = 0; }

This section of the program to determine the number of pages to display, the default is to display the first page. To display pages on the third page, you need to use the http://xxxxxx/index.php?2 format, which is the incoming $QUERY _string, and so on. The following line of procedures are used to process the number of pages and the amount of data displayed.

$msgnum = 20; 20 pens per page

To change the number of displayed pens per page, you can change $msgnum variables. The default value of the program is 20 pens.

for ($i = $start; $i $end; $i + +) { echo " \ n"; echo " \n". $guestbook [$i][5]. " "; if ($guestbook [$i][6]!= "") echo Href=mailto: ". $guestbook [$i][6]." > "; echo " ". $guestbook [$i][3]. " "; if ($guestbook [$i][6]!= "") echo ""; echo " \ n"; if ($WebmasterIP) echo " delete This article!!   (". $guestbook [$i][2].") "; echo " from: ". $guestbook [$i][4]." \ n "; $msg =base64_decode ($guestbook [$i][7]); $msg =nl2br ($msg); Echo $msg; echo " \ n"; }

This section of the program is really show the message information to the user to see the program. Using a For loop, the data of the $guestbook array is taken out according to the set number of pages and displayed to the user. It is worth mentioning that, if you look at the message machine IP as a $WebmasterIPArray variable array of elements, it will be in the message after the nickname "Delete this article!!" string, for managers to delete improper messages.

The following is the procedure for deleting a message.

file://--------------------------- Message Removal program erase.php Author:wilson Peng Copyright (C) 2000 file://--------------------------- Putenv ("oracle_sid=www"); Putenv ("Nls_lang=american_taiwan.zht16big5"); Putenv ("oracle_home=/home/oracle/product/7.3.2"); Putenv ("Ld_library_path=/home/oracle/product/7.3.2/lib"); Putenv ("Ora_nls=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); Putenv ("Ora_nls32=/home/oracle/product/7.3.2/ocommon/nls/admin/data"); $handle =ora_logon ("User38@www", "iam3849") or die; $cursor =ora_open ($handle); Ora_commitoff ($handle); $query = "UPDATE Guestbook set flag= ' 0" where Serial= ' ". $QUERY _string." ' "; Ora_parse ($cursor, $query) or die; Ora_exec ($cursor); Ora_close ($cursor); Ora_logoff ($handle); Header ("Location:./index.php"); ? >

In fact, this program is very simple, as long as open Oracle database, will want to delete the serial number of the Flag field set to 0 can be, do not have the data really from the database to remove.