topic

The forum working with database MySQL

Apache web - server is necessary for job of a forum with support PHP and database MySQL. The script forum.php3 is located in the catalogue/usr/local/apache/htdocs (without blanks). The working forum can be seen here <http://phpmysql.flashmaster.ru/forum.php>, and full listing here <http: // www.zk.ru/alextr/forumcode.html>.

The beginning

Before to begin consideration of practical application PHP, it is necessary enter in MySQL as the manager and to create a database in which the information will be stored{kept}.

We create a database forum.

mysqladmin-u root-p create forum;

use forum;

We create 3 tables in a database forum. In them the information will enter the name.

create table info (

id int (4) not null auto_increment primary key,

name varchar (15) not null,

password varchar (15) not null,

email varchar (40) not null,

posts int (4) default ' 0 ',

key (name), key (password));

create table topics (

top_id int (4) not null auto_increment primary key,

top_name varchar (255) not null,

name varchar (15) not null,

message text not null,

replies int (4) default ' 0 ',

post_date datetime default ' 0000-00-00 00:00:00 ',

last_reply datetime default ' 0000-00-00 00:00:00 ',

key (top_name), key (name));

create table replies (

name varchar (15) not null,

top_name varchar (255) not null,

reply text not null,

reply_date datetime default ' 0000-00-00 00:00:00 ',

key (name), key (top_name));

Registration of the user in a DB

Further will go pure{clean} PHP and HTML.

Let's generate the form for registration:

<h2 align=center> <font color = "ff0000"> Registration. </font> </h2>

<p> <font face = "serif" size=2> Please, fill in the form below.

<p> You may use only english chars

in your name, and name's length should be not less than 3 and

not more than 15 characters.

<br> Remember:all form fields are case sensitive. It means the names

<font face = "arial" size=2

color = "0000ff"> name </font> and

<font face = "arial" size=2 color = "0000ff"> Name </font> are different.

<p> Password's length should be not less than 6 characters. <br>

Don't forget to enter your e-mail address, you may need it if you'll forget your password.

<tr> <TD> <b> Login: </b> <TD>

<tr> <TD> <b> Password: </b> <TD>

<tr> <TD> <b> Verify password: </b> <TD>

</table>

</form>

The functions used in the form, serve for check of the information entered by the user. Further, it is necessary to process the data. For connection with a DB function mysql_connect is used () .Poskol`ku to incorporate from a DB it is necessary not time, I have born{have taken out} all data in function connect ()

function connect () {

$hostname = "localhost:3306";

$user = "root";

$password = " your password ";

$db = "forum";

mysql_connect ($hostname, $user, $password);

$request=mysql_select_db ($db);

if (! mysql_connect) {

echo " Error: " .mysql_error ();

}

This function connects a database and checks presence of mistakes of connection. Now it is possible to receive and process the data of the user input.

if ($action == "register") {

connect ();

if ($verify) {

$query = " select id from info where name = ' $ login ' "; // Search to a DB.

$result=mysql_query ($query);

if (mysql_num_rows ($result)) {// It is checked, whether there is already such user.

print_header ();

echo " <p align=center> <b> Error! </b> ";

echo " <p> Name <b> ". $login. " </b> already exists. Please, go back and choose another name. ";

<? php

} else {

// If all OK it is inserted the data in the table info.

$query = " insert into info (name, password, email) values (' $login ',' $pass', ' $ email ') ";

$result=mysql_query ($query);

// We speak the user of thanks and we unpack{we print out} the information.

print_header ();

echo " <h3 align=center> Thank you, <font color ='ff0000 '> ". $login. "! </font> </h3> ";?>

<p> You are successfuly registered and your information added to our database.

<p> Please, remember your information. Only registered users may post messages and replies.

<p> <font face ='arial ' size=2 color = '0000ff'> Your login: </font>

<font face ='arial ' size=2 color = '0000ff'> Password: </font>

<p> Good luck!

<center> <a href = ""> | Go to topics </a> |

<a href = "? action=add_new_top "> Post new topic </a> | </center>

<? php

}

Function print_header () is heading of html-page. She too is used for reduction of quantity{amount} of a code.

function print_header () {

<html> <head> <title> Forum version 1.1 </title>

</head> <style> A:link {font-family:arial; font-size:10pt; text-decoration:none; color: * 0000ff;}

A:hover {font-family:arial; font-size:10pt; text-decoration:none; color:red;} ";

A:visited {font-family:arial; font-size:10pt; text-decoration:underline; color: * 0000ff;}

BODY {background-color: * e6e8fa; font-family:arial; font-size:10pt; color: * 333300;}

TD {font-family:arial; font-size:10pt; color: * 333300;}

H2 {text-align:center; color:blue;}

</style> <body>

<? php

}

Well that's all. The user is registered and can place messages in a forum.

Creation new topika

For creation new topika it is used the following form:

<h3 align=center> <font color = "0000ff"> New topic </font> </h3>

<p align=right> | <a href = " <? php echo $script?> "> Go to topics </a>

| <a href = " <? php echo $script?>? action=register "> Register </a> |

Only registered users may post messages in this forum. </font>

<p> <form action = " <? php echo $script?> " name = "newtop" method = "POST">

<tr> <td> *nbsp;

<tr> <td> <b> Your password: </b> <td> <input type = "password" name = "passwd" size=20>

<tr> <td> <b> Topic name: </b> <td> <input type = "text" name = "theme" size=20>

<tr> <td colspan=2> *nbsp; <b> Message: </b> <p> *nbsp; *nbsp; <textarea cols=40 rows=15

WRAP = "VIRTUAL" name = "message"> </textarea>

<? php

All fields are obligatory for filling. The user should enter the name under which he was registered in a DB and the password. The script checks their presence in a DB and if it's OK be located{place} the message in the table topics. If the name and the password are entered the mistake incorrect - is given out. Place messages the registered users can only. Here is how it looks:

elseif ($action == "add_new_top") {

connect (); // we Are connected to a DB.

if ($test) {

// We read the data from the form.

$date=date (" Y-m-d H:i:s ", mktime ());

$message=htmlspecialchars ($message);

$message=nl2br ($message);

$message=str_replace ("," ", $message);

$theme=str_replace ("," ", $theme);

$theme=str_replace (' "'", ", $theme);

$theme=htmlspecialchars ($theme);

// Here it is necessary to give explanatories about use

// Functions htmlspecialchars (), str_replace () and nl2br ().

// htmlspecialchars () it is used that

// To transform html-tegi. It is done{made} with a view of safety

And for protection against a different sort of jokers:)

// str_replace () replaces a symbol unary inverted commas with a blank,

// Since entering unary inverted commas in a DB

// It invalid - it a special service symbol. It will protect a DB from breaking.

// nl2br () will transform a symbol of the end of a line in html-teg <br>.

// It is used that the text entered into the form was saved constant.

$query = " select id from info where name = ' $ nick ' AND password = ' $ passwd ' ";

// Search to BD.Proverka a name and the password.

$result=mysql_query ($query);

// If all OK, we check, that the field of the form were not empty.

if (mysql_num_rows ($result)) {

if (strlen ($theme)! =0 ** strlen ($message)! =0) {

// If all OK, we insert the data in the table topics.

$query = " insert into topics (top_name, name, message, post_date, last_reply)

values (' $theme ',' $nick ',' $message ',' $date ',' $date ') ";

$result=mysql_query ($query);

// We update the table we info-increased quantity{amount} of posts for the given user on 1.

$query = " update info set posts=posts+1 where name = ' $ nick ' ";

$result=mysql_query ($query);

print_header ();

// We speak the user of thanks.

<h3 align=center> Thank you, <font color = "ff0000"> <? php echo $nick?>! </font> </h3>

<p> Your topic has been submitted.

<p> <center> | <a href = " <? php echo $script?> "> Go to topics </a> |

<a href = " <? php echo $script?>? action=add_new_top "> Post new topic </a> | </center>

<? php

} else {

print_header ();

// If one of fields of the form appeared the mistake emptier - is given out.

<h2 align=center> Error! </h2>

<p> You did not enter topic name or message. Please, go back and try again.

| <a href = " <? php echo $script?>? action=add_new_top "> Go back </a>

| <a href = "? action=remind_pass "> Forgot password? </a> | </center>

<? php

}

} else {

print_header ();

// If an incorrect name or the password are entered, the mistake also is given out.

<h2 align=center> Error! </h2>

<p> You entered incorrect name or password. Please, go back and try again.

<p> <center> | <a href = " <? php echo $script?>? action=add_new_top "> Go back </a>

| <a href = " <? php echo $script?>? action=remind_pass "> Forgot password? </a> | </center>

<? php

}

All these precautions not superfluous. First with a view of safety of the database. Second for protection of a forum against casual visitors and against breaking. New topik it is created also the information it is added in the table topics. Further we shall consider how to place the answer to the message.

Parcel{Sending} of the answer to the message

She differs nothing from creation new topika, only the answer is located in the table replies. We shall generate the form.

<h3 align=center> <font color = "0000ff"> Post reply. </font> </h3>

<p align=right> | <a href = " <? php echo $script?> "> Go to topics </a>

| <a href = " <? php echo $script?>? action=register "> Register </a> |

Only registered users may post messages in this forum. </font>

<p> <form action = " <? php echo $script?> " name = "reply" method = "POST">

<tr> <td> *nbsp;

<tr> <td> <b> Your password: </b> <td>

<tr> <td colspan=2> *nbsp; <b> Message: </b> <p> *nbsp; *nbsp;

And data processing:

elseif ($action == "post_reply") {

if ($validate) {

connect ();

$date=date (" Y-m-d H:i:s ", mktime ());

$message=htmlspecialchars ($message);

$message=nl2br ($message);

$message=str_replace ("," ", $message);

$query = " select id from info where name = ' $ nick ' AND password = ' $ passwd ' ";

$result=mysql_query ($query);

if (mysql_num_rows ($result)) {

if (strlen ($message)! =0) {

print_header ();

$query = " insert into replies (name, top_name, reply, reply_date)

values (' $nick ',' $top_name ',' $message ',' $date ') ";

$result=mysql_query ($query);

$query = " update topics set replies=replies+1, last_reply = ' $ date '

where top_name = ' $ top_name ' ";

$result=mysql_query ($query);

$query = " update info set posts=posts+1 where name = ' $ nick ' ";

$result=mysql_query ($query);

echo " <h3 align=center> Thank you,

<font color ='ff0000 '> ". $nick. "! </font> </h3> ";

echo " <p> Your reply has been submitted. ";

<p> <center> | <a href = " <? php echo $script?> "> Go to topics </a>

| <a href = " <? php echo $script?>? action=add_new_top ">

Post new topic </a> | </center> <? php

} else {

print_header ();

<h2 align=center> Error! </h2>

<p> You did not enter message. Please, go back and try again.

? action=post_reply*top_name =

<? php echo $top_name?> "> Go back </a>

| <a href = " <? php echo $script?>? action=remind_pass ">

Forgot password? </a> | </center>

<? php

}

With it, I think, all is understandable, as well as in the previous example.

Reading topika

It is rather volumetric part of a script, therein I shall allow explanatories.

elseif ($action == "read_topic") {

if ($top_name) {

connect ();

// In this case the data get out of 2 tables - topics and info.

$query = " select message, post_date, info.posts,

info.email from topics, info where topics.name =

info.name and topics.top_name = ' $ top_name ' and topics.name = ' $ name ' ";

$result=mysql_query ($query);

$row=mysql_fetch_array ($result);

$message = $ row ["message"];

$post_date = $ row ["post_date"];

$posts = $ row ["posts"];

$email = $ row ["email"];

// We cut out tegi <br> that they were not displayed on page.

$message=str_replace (" *lt; br*gt; ", " ", $message);

$char = $ message;

// This heap of transformations allows to place

// In messages smileys and to display them as figures.

$char=str_replace (":) ", " <img src = './images/smile.gif '> ", $char);

$char=str_replace (": (", " <img src = './images/frown.gif '> ", $char);

$char=str_replace (":o", " <img src = './images/redface.gif '> ", $char);

$char=str_replace (":D", " <img src = './images/biggrin.gif '> ", $char);

$char=str_replace (";) ", " <img src = './images/wink.gif '> ", $char);

$char=str_replace (":p", " <img src = './images/tongue.gif '> ", $char);

$char=str_replace (":cool: ", " <img src = './images/cool.gif '> ", $char);

$char=str_replace (":rolleyes: ", " <img src = './images/rolleyes.gif '> ", $char);

$char=str_replace (":mad: ", " <img src = './images/mad.gif '> ", $char);

$char=str_replace (":eek: ", " <img src = './images/eek.gif '> ", $char);

$char=str_replace (":confused: ", " <img src = './images/confused.gif '> ", $char);

print_header ();

// Now all chosen data it is placed in the html-table and we display.

<p align=right> | <a href = " <? php echo $script?> "> Go to topics </a> |

<a href = " <? php echo $script?>? action=add_new_top "> Post new topic </a> |

<a href = " <? php echo $script?>? action=post_reply*top_name =

<? php echo $top_name?> "> Post reply </a> |

<font face = "Arial" size=2 color = "808080"> Author </font>

Topic: </font> <font face = "serif" size=2 color = "ff0000">

<? php echo $top_name?> </font> </td> </tr>

<? php echo $name?> </font>

Posts: <? php echo $posts?> </font> </td>

<font face = "serif" size=1 color = "000080"> Posted:

<? php echo $post_date?> </font>

*nbsp; *nbsp; *nbsp; *nbsp; *nbsp; *nbsp; <a href = " mailto:

<? php echo $email?> "> <img src = "email.gif" border=0> </a>

*nbsp; *nbsp; <font face = "serif" size=1 color = "000080"> E-mail </font>

*nbsp; *nbsp; *nbsp; *nbsp; *nbsp; *nbsp;

<a href = " <? php echo $script?>? action=edit*top_name =

<? php echo $top_name?> *nick =

<? php echo $name?> *type=message ">

<img src = "edit.gif" border=0> </a> *nbsp; *nbsp;

Edit message </font> </td> </tr>

<? php echo $char?> </font> </td> </tr> </table>

<? php

// We limit quantity{amount} of recordings on page 20 to messages.

$lines=20;

$begin = $ page * $ lines;

if (empty ($page)) {

$page=0;

}

// We do{make} new search for a conclusion of answers on topik.

$query = " select replies.name, reply, reply_date, info.posts,

info.email from replies, info where replies.name =

info.name and replies.top_name =

' $top_name ' order by reply_date limit $begin, $lines ";

$result=mysql_query ($query);

// Further all the same as and in previous time.

while ($row=mysql_fetch_array ($result)) {

$nick = $ row ["name"];

$reply = $ row ["reply"];

$reply_date = $ row ["reply_date"];

$posts = $ row ["posts"];

$mail = $ row ["email"];

$reply=str_replace (" *lt; br*gt; ", " ", $reply);

$char = $ reply;