Wednesday, June 5, 2013

Membuat Web Forum dengan PHP/MySQL

Langkah 1 : Membuat Tabel Database

Sebagai langkah pertama, kita akan membuat database dengan nama database yang berisikan tabel user, categoriestopics, dan posts.

Tabel user :

CREATE TABLE users (
user_id     INT(8) NOT NULL AUTO_INCREMENT,
user_name    VARCHAR(30) NOT NULL,
user_pass      VARCHAR(255) NOT NULL,
user_email    VARCHAR(255) NOT NULL,
user_date    DATETIME NOT NULL,
user_level    INT(8) NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) TYPE=INNODB;


Tabel categories :

CREATE TABLE categories (
cat_id              INT(8) NOT NULL AUTO_INCREMENT,
cat_name         VARCHAR(255) NOT NULL,
cat_description     VARCHAR(255) NOT NULL,
UNIQUE INDEX cat_name_unique (cat_name),
PRIMARY KEY (cat_id)
) TYPE=INNODB;


Tabel topics :

CREATE TABLE topics (
topic_id        INT(8) NOT NULL AUTO_INCREMENT,
topic_subject          VARCHAR(255) NOT NULL,
topic_date        DATETIME NOT NULL,
topic_cat        INT(8) NOT NULL,
topic_by        INT(8) NOT NULL,
PRIMARY KEY (topic_id)
) TYPE=INNODB; 


Tabel posts : 

CREATE TABLE posts (
post_id         INT(8) NOT NULL AUTO_INCREMENT,
post_content        TEXT NOT NULL,
post_date         DATETIME NOT NULL,
post_topic        INT(8) NOT NULL,
post_by        INT(8) NOT NULL,
PRIMARY KEY (post_id)
) TYPE=INNODB;



Selanjutnya kita akan menghubungkan tabel-tabel tersebut. Hubungkan topics ke categories :
ALTER TABLE topics ADD FOREIGN KEY(topic_cat) REFERENCES categories(cat_id) ON DELETE CASCADE ON UPDATE CASCADE

hubungkan topics ke user :

ALTER TABLE topics ADD FOREIGN KEY(topic_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE; 

selanjutnya hubungkan post ke topics :

ALTER TABLE posts ADD FOREIGN KEY(post_topic) REFERENCES topics(topic_id) ON DELETE CASCADE ON UPDATE CASCADE; 

dan yang terakhir adalah menghubungkan posts ke user : 

ALTER TABLE posts ADD FOREIGN KEY(post_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;


Langkah 2 : Pengenalan Header/Footer System

Pada langkah kedua ini, kita akan membuat header dan body untuk halaman forum kita nanti

header.php :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="nl" lang="nl">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <meta name="description" content="A short description." />
    <meta name="keywords" content="put, keywords, here" />
    <title>PHP-MySQL forum</title>
    <link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>
<h1>My forum</h1>
    <div id="wrapper">
    <div id="menu">
        <a class="item" href="/forum/index.php">Home</a> -
        <a class="item" href="/forum/create_topic.php">Create a topic</a> -
        <a class="item" href="/forum/create_cat.php">Create a category</a>
        <div id="userbar">
     if($_SESSION['signed_in'])
     {
          echo 'Hello' . $_SESSION['user_name'] . '. Not you? <a href="signout.php">Sign out</a>';
     }
     else
     {
         echo '<a href="signin.php">Sign in</a> or <a href="sign up">create an account</a>.';
     }
</div>
        <div id="content">


<?php
$error = false;
if($error = false)
{
     //the beautifully styled content, everything looks good
     echo '<div id="content">some text</div>';
}
else
{
     //bad looking, unstyled error :-(
}

?>


body {
    background-color: #4E4E4E;
    text-align: center;            /* make sure IE centers the page too */
}
#wrapper {
    width: 900px;
    margin: 0 auto;             /* center the page */
}
#content {
    background-color: #fff;
    border: 1px solid #000;
    float: left;
    font-family: Arial;
    padding: 20px 30px;
    text-align: left;
    width: 100%;                /* fill up the entire div */
}
#menu {
    float: left;
    border: 1px solid #000;
    border-bottom: none;        /* avoid a double border */
    clear: both;                /* clear:both makes sure the content div doesn't float next to this one but stays under it */
    width:100%;
    height:20px;
    padding: 0 30px;
    background-color: #FFF;
    text-align: left;
    font-size: 85%;
}
#menu a:hover {
    background-color: #009FC1;
}
#userbar {
    background-color: #fff;
    float: right;
    width: 250px;
}
#footer {
    clear: both;
}
/* begin table styles */
table {
    border-collapse: collapse;
    width: 100%;
}
table a {
    color: #000;
}
table a:hover {
    color:#373737;
    text-decoration: none;
}
th {
    background-color: #B40E1F;
    color: #F0F0F0;
}
td {
    padding: 5px;
}
/* Begin font styles */
h1, #footer {
    font-family: Arial;
    color: #F1F3F1;
}
h3 {margin: 0; padding: 0;}
/* Menu styles */
.item {
    background-color: #00728B;
    border: 1px solid #032472;
    color: #FFF;
    font-family: Arial;
    padding: 3px;
    text-decoration: none;
}
.leftpart {
    width: 70%;
}
.rightpart {
    width: 30%;
}
.small {
    font-size: 75%;
    color: #373737;
}
#footer {
    font-size: 65%;
    padding: 3px 0 0 0;
}
.topic-post {
    height: 100px;
    overflow: auto;
}
.post-content {
    padding: 30px;
}
textarea {
    width: 500px;
    height: 200px;
}
   

</div><!-- content -->
</div><!-- wrapper -->
<div id="footer">Created for Nettuts+</div>
</body>
</html>


Langkah 3: Memulai

Sebelum kita bisa membaca apa pun dari database kitakita perlu sambunganItulah connect.phpKita akan memasukkannya dalam setiap file yang akan kita buat.

<?php
//connect.php
$server    = 'localhost';
$username    = 'usernamehere';
$password    = 'passwordhere';
$database    = 'databasenamehere';
if(!mysql_connect($server, $username,  $password))
{
     exit('Error: could not establish database connection');
}
if(!mysql_select_db($database)
{
     exit('Error: could not select the database');
}
?>


Langkah 4: Displaying the Forum Overview

create_cat.php :
<?php
//create_cat.php
include 'connect.php';
include 'header.php';
echo '<tr>';    
echo '<td class="leftpart">';
        echo '<h3><a href="category.php?id=">Category name</a></h3> Category description goes here';
    echo '</td>';
    echo '<td class="rightpart">';
            echo '<a href="topic.php?id=">Topic subject</a> at 10-10';
    echo '</td>';echo '</tr>';include 'footer.php';
?>

Langkah 5: Mendaftarkan Pengguna

Mari kita mulai dengan membuat bentuk HTML sederhana sehingga pengguna baru dapat mendaftarLihat halaman signup.php di bawah.

<?php
//signup.php
include 'connect.php';
include 'header.php';
echo '<h3>Sign up</h3>';
if($_SERVER['REQUEST_METHOD'] != 'POST'){
    /*the form hasn't been posted yet, display it      note that the action="" will cause the form to post to the same page it is on */
    echo '<form method="post" action="">          Username: <input type="text" name="user_name" />         Password: <input type="password" name="user_pass">        Password again: <input type="password" name="user_pass_check">        
E-mail: <input type="email" name="user_email">         <input type="submit" value="Add category" />      </form>';
}
else{
    /* so, the form has been posted, we'll process the data in three steps:        1.    Check the data        2.    Let the user refill the wrong fields (if necessary)        3.    Save the data    */
    $errors = array();
 /* declare the array for later use */
    if(isset($_POST['user_name']))    {
        //the user name exists
        if(!ctype_alnum($_POST['user_name']))
        {
            $errors[] = 'The username can only contain letters and digits.';
        }
        if(strlen($_POST['user_name']) > 30)
        {            
$errors[] = 'The username cannot be longer than 30 characters.';
        }
    }
    else
    {
        $errors[] = 'The username field must not be empty.';
    }
    if(isset($_POST['user_pass']))
    {        
if($_POST['user_pass'] != $_POST['user_pass_check'])
        {            
$errors[] = 'The two passwords did not match.';
        }
    }
    else
    {
        $errors[] = 'The password field cannot be empty.';
    }
    if(!empty($errors))
 /*check for an empty array, if there are errors, they're in this array (note the ! operator)*/
    {
        echo 'Uh-oh.. a couple of fields are not filled in correctly..';
        echo '<ul>';
        foreach($errors as $key => $value) /* walk through the array so all the errors get displayed */
        {
            echo '<li>' . $value . '</li>';
 /* this generates a nice error list */
        }
        echo '</ul>';
    }
    else
    {
        //the form has been posted without, so save it
        //notice the use of mysql_real_escape_string, keep everything safe!
        //also notice the sha1 function which hashes the password
        $sql = "INSERT INTO users(user_name, user_pass, user_email ,user_date, user_level)
                VALUES('" . mysql_real_escape_string($_POST['user_name']) . "',                      
'" . sha1($_POST['user_pass']) . "',                       '" . mysql_real_escape_string($_POST['user_email']) . "',NOW(),0)";
        $result = mysql_query($sql);
        if(!$result)
        {
            //something went wrong, display the error
            echo 'Something went wrong while registering. Please try again later.';
            //echo mysql_error();
 //debugging purposes, uncomment when needed
        }
        else
        {
            echo 'Successfully registered. You can now <a href="signin.php">sign in</a> and start posting! :-)';
        }
    }
}
include 'footer.php';
?>

Langkah 6: Menambahkan Otentikasi dan Tingkat Pengguna

File signin.php ada di bawah ini :
<?php
//signin.php
include 'connect.php';
include 'header.php';
echo '<h3>Sign in</h3>';
//first, check if the user is already signed in. If that is the case, there is no need to display this page
if(isset($_SESSION['signed_in']) && $_SESSION['signed_in'] == true)
{
    echo 'You are already signed in, you can <a href="signout.php">sign out</a> if you want.';
}
else
{
    if($_SERVER['REQUEST_METHOD'] != 'POST')
    {
        /*the form hasn't been posted yet, display it
          note that the action="" will cause the form to post to the same page it is on */
        echo '<form method="post" action="">
            Username: <input type="text" name="user_name" />
            Password: <input type="password" name="user_pass">
            <input type="submit" value="Sign in" />
         </form>';
    }
    else
    {
        /* so, the form has been posted, we'll process the data in three steps:
            1.    Check the data
            2.    Let the user refill the wrong fields (if necessary)
            3.    Varify if the data is correct and return the correct response
        */
        $errors = array(); /* declare the array for later use */
        if(!isset($_POST['user_name']))
        {
            $errors[] = 'The username field must not be empty.';
        }
        if(!isset($_POST['user_pass']))
        {
            $errors[] = 'The password field must not be empty.';
        }
        if(!empty($errors)) /*check for an empty array, if there are errors, they're in this array (note the ! operator)*/
        {
            echo 'Uh-oh.. a couple of fields are not filled in correctly..';
            echo '<ul>';
            foreach($errors as $key => $value) /* walk through the array so all the errors get displayed */
            {
                echo '<li>' . $value . '</li>'; /* this generates a nice error list */
            }
            echo '</ul>';
        }
        else
        {
            //the form has been posted without errors, so save it
            //notice the use of mysql_real_escape_string, keep everything safe!
            //also notice the sha1 function which hashes the password
            $sql = "SELECT
                        user_id,
                        user_name,
                        user_level
                    FROM
                        users
                    WHERE
                        user_name = '" . mysql_real_escape_string($_POST['user_name']) . "'
                    AND
                        user_pass = '" . sha1($_POST['user_pass']) . "'";
            $result = mysql_query($sql);
            if(!$result)
            {
                //something went wrong, display the error
                echo 'Something went wrong while signing in. Please try again later.';
                //echo mysql_error(); //debugging purposes, uncomment when needed
            }
            else
            {
                //the query was successfully executed, there are 2 possibilities
                //1. the query returned data, the user can be signed in
                //2. the query returned an empty result set, the credentials were wrong
                if(mysql_num_rows($result) == 0)
                {
                    echo 'You have supplied a wrong user/password combination. Please try again.';
                }
                else
                {
                    //set the $_SESSION['signed_in'] variable to TRUE
                    $_SESSION['signed_in'] = true;
                    //we also put the user_id and user_name values in the $_SESSION, so we can use it at various pages
                    while($row = mysql_fetch_assoc($result))
                    {
                        $_SESSION['user_id']     = $row['user_id'];
                        $_SESSION['user_name']     = $row['user_name'];
                        $_SESSION['user_level'] = $row['user_level'];
                    }
                    echo 'Welcome, ' . $_SESSION['user_name'] . '. <a href="index.php">Proceed to the forum overview</a>.';
                }
            }
        }
    }
}
include 'footer.php';
?>
   

<?php
//set the $_SESSION['signed_in'] variable to TRUE
$_SESSION['signed_in'] = true;
//we also put the user_id and user_name values in the $_SESSION, so we can use it at various pages
while($row = mysql_fetch_assoc($result))
{
     $_SESSION['user_id'] = $row['user_id'];
     $_SESSION['user_name'] = $row['user_name'];
}
?>


Langkah 7 : Membuat Kategori

Untuk membuat halaman kategori, buatlah form terlebih dahulu.

<form method="post" action="">
     Category name: <input type="text" name="cat_name" />
     Category description: <textarea name="cat_description" /></textarea>
    <input type="submit" value="Add category" />
 </form>


<?php
//create_cat.php
include 'connect.php';
if($_SERVER['REQUEST_METHOD'] != 'POST')
{
    //the form hasn't been posted yet, display it
    echo '<form method='post' action=''>
          Category name: <input type='text' name='cat_name' />
         Category description: <textarea name='cat_description' /></textarea>
         <input type='submit' value='Add category' />
      </form>';
}
else
{
    //the form has been posted, so save it
    $sql = ìINSERT INTO categories(cat_name, cat_description)
        VALUES('' . mysql_real_escape_string($_POST['cat_name']) . ì',
              '' . mysql_real_escape_string($_POST['cat_description']) . ì')';
    $result = mysql_query($sql);
    if(!$result)
    {
        //something went wrong, display the error
        echo 'Error' . mysql_error();
    }
    else
    {
        echo 'New category successfully added.';
    }
}
?>

Langkah 8Menambahkan Kategori ke index.php

Kami telah membuat beberapa kategorijadi sekarang kami dapat menampilkannya di halaman depan 

index.php :
<?php
//create_cat.php
include 'connect.php';
include 'header.php';
$sql = "SELECT
            cat_id,
            cat_name,
            cat_description,
        FROM
            categories";
$result = mysql_query($sql);
if(!$result)
{
    echo 'The categories could not be displayed, please try again later.';
}
else
{
    if(mysql_num_rows($result) == 0)
    {
        echo 'No categories defined yet.';
    }
    else
    {
        //prepare the table
        echo '<table border="1">
              <tr>
                <th>Category</th>
                <th>Last topic</th>
              </tr>';
        while($row = mysql_fetch_assoc($result))
        {
            echo '<tr>';
                echo '<td class="leftpart">';
                    echo '<h3><a href="category.php?id">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'];
                echo '</td>';
                echo '<td class="rightpart">';
                            echo '<a href="topic.php?id=">Topic subject</a> at 10-10';
                echo '</td>';
            echo '</tr>';
        }
    }
}
include 'footer.php';
?>


Langkah 9: Membuat Topik

<?php
//create_cat.php
include 'connect.php';
include 'header.php';
echo '<h2>Create a topic</h2>';
if($_SESSION['signed_in'] == false)
{
    //the user is not signed in
    echo 'Sorry, you have to be <a href="/forum/signin.php">signed in</a> to create a topic.';
}
else
{
    //the user is signed in
    if($_SERVER['REQUEST_METHOD'] != 'POST')
    {
        //the form hasn't been posted yet, display it
        //retrieve the categories from the database for use in the dropdown
        $sql = "SELECT
                    cat_id,
                    cat_name,
                    cat_description
                FROM
                    categories";
        $result = mysql_query($sql);
        if(!$result)
        {
            //the query failed, uh-oh :-(
            echo 'Error while selecting from database. Please try again later.';
        }
        else
        {
            if(mysql_num_rows($result) == 0)
            {
                //there are no categories, so a topic can't be posted
                if($_SESSION['user_level'] == 1)
                {
                    echo 'You have not created categories yet.';
                }
                else
                {
                    echo 'Before you can post a topic, you must wait for an admin to create some categories.';
                }
            }
            else
            {
                echo '<form method="post" action="">
                    Subject: <input type="text" name="topic_subject" />
                    Category:';
                echo '<select name="topic_cat">';
                    while($row = mysql_fetch_assoc($result))
                    {
                        echo '<option value="' . $row['cat_id'] . '">' . $row['cat_name'] . '</option>';
                    }
                echo '</select>';
                echo 'Message: <textarea name="post_content" /></textarea>
                    <input type="submit" value="Create topic" />
                 </form>';
            }
        }
    }
    else
    {
        //start the transaction
        $query  = "BEGIN WORK;";
        $result = mysql_query($query);
        if(!$result)
        {
            //Damn! the query failed, quit
            echo 'An error occured while creating your topic. Please try again later.';
        }
        else
        {
            //the form has been posted, so save it
            //insert the topic into the topics table first, then we'll save the post into the posts table
            $sql = "INSERT INTO
                        topics(topic_subject,
                               topic_date,
                               topic_cat,
                               topic_by)
                   VALUES('" . mysql_real_escape_string($_POST['topic_subject']) . "',
                               NOW(),
                               " . mysql_real_escape_string($_POST['topic_cat']) . ",
                               " . $_SESSION['user_id'] . "
                               )";
            $result = mysql_query($sql);
            if(!$result)
            {
                //something went wrong, display the error
                echo 'An error occured while inserting your data. Please try again later.' . mysql_error();
                $sql = "ROLLBACK;";
                $result = mysql_query($sql);
            }
            else
            {
                //the first query worked, now start the second, posts query
                //retrieve the id of the freshly created topic for usage in the posts query
                $topicid = mysql_insert_id();
                $sql = "INSERT INTO
                            posts(post_content,
                                  post_date,
                                  post_topic,
                                  post_by)
                        VALUES
                            ('" . mysql_real_escape_string($_POST['post_content']) . "',
                                  NOW(),
                                  " . $topicid . ",
                                  " . $_SESSION['user_id'] . "
                            )";
                $result = mysql_query($sql);
                if(!$result)
                {
                    //something went wrong, display the error
                    echo 'An error occured while inserting your post. Please try again later.' . mysql_error();
                    $sql = "ROLLBACK;";
                    $result = mysql_query($sql);
                }
                else
                {
                    $sql = "COMMIT;";
                    $result = mysql_query($sql);
                    //after a lot of work, the query succeeded!
                    echo 'You have successfully created <a href="topic.php?id='. $topicid . '">your new topic</a>.';
                }
            }
        }
    }
}
include 'footer.php';
?>
    

Langkah 10: Menampilkan Kategori

Kita akan membuat halaman ikhtisar untuk satu kategoriKami baru saja membuat kategoriitu akan berguna untuk dapat melihat semua topik di dalamnyaPertamamembuat halaman yang disebut category.php.

<?php
//create_cat.php
include 'connect.php';
include 'header.php';
//first select the category based on $_GET['cat_id']
$sql = "SELECT
            cat_id,
            cat_name,
            cat_description
        FROM
            categories
        WHERE
            cat_id = " . mysql_real_escape_string($_GET['id']);
$result = mysql_query($sql);
if(!$result)
{
    echo 'The category could not be displayed, please try again later.' . mysql_error();
}
else
{
    if(mysql_num_rows($result) == 0)
    {
        echo 'This category does not exist.';
    }
    else
    {
        //display category data
        while($row = mysql_fetch_assoc($result))
        {
            echo '<h2>Topics in ′' . $row['cat_name'] . '′ category</h2>';
        }
        //do a query for the topics
        $sql = "SELECT
                    topic_id,
                    topic_subject,
                    topic_date,
                    topic_cat
                FROM
                    topics
                WHERE
                    topic_cat = " . mysql_real_escape_string($_GET['id']);
        $result = mysql_query($sql);
        if(!$result)
        {
            echo 'The topics could not be displayed, please try again later.';
        }
        else
        {
            if(mysql_num_rows($result) == 0)
            {
                echo 'There are no topics in this category yet.';
            }
            else
            {
                //prepare the table
                echo '<table border="1">
                      <tr>
                        <th>Topic</th>
                        <th>Created at</th>
                      </tr>';
                while($row = mysql_fetch_assoc($result))
                {
                    echo '<tr>';
                        echo '<td class="leftpart">';
                            echo '<h3><a href="topic.php?id=' . $row['topic_id'] . '">' . $row['topic_subject'] . '</a><h3>';
                        echo '</td>';
                        echo '<td class="rightpart">';
                            echo date('d-m-Y', strtotime($row['topic_date']));
                        echo '</td>';
                    echo '</tr>';
                }
            }
        }
    }
}
include 'footer.php';
?>


Langkah 11: Tampilan Topik

Pada tahap ini, kita tinggal menggantikan query pada bagian PHP dalam halaman category.phpQuery yang pertama untuk mengambil informasi dasar tentang topik:

SELECT
    topic_id,
    topic_subject
FROM
    topics
WHERE
    topics.topic_id = " . mysql_real_escape_string($_GET['id'])


Selanjutnya, kita mengambil semua posting dalam topik ini dari databaseQuery berikut memberi kita apa yang kita butuhkan:

SELECT
    posts.post_topic,
    posts.post_content,
    posts.post_date,
    posts.post_by,
    users.user_id,
    users.user_name
FROM
    posts
LEFT JOIN
    users
ON
    posts.post_by = users.user_id
WHERE
    posts.post_topic = " . mysql_real_escape_string($_GET['id'])
    

Langkah 12: Menambahkan Reply

Mari kita membuat bagian terakhir yang hilang dari forum inikemungkinan untumenambahkan balasanKita akan mulai dengan membuat form.

Kode lengkap reply.php terlihat seperti ini :

<form method="post" action="reply.php?id=5">
    <textarea name="reply-content"></textarea>
    <input type="submit" value="Submit reply" />
</form>


<?php
//create_cat.php
include 'connect.php';
include 'header.php';
if($_SERVER['REQUEST_METHOD'] != 'POST')
{
    //someone is calling the file directly, which we don't want
    echo 'This file cannot be called directly.';
}
else
{
    //check for sign in status
    if(!$_SESSION['signed_in'])
    {
        echo 'You must be signed in to post a reply.';
    }
    else
    {
        //a real user posted a real reply
        $sql = "INSERT INTO
                    posts(post_content,
                          post_date,
                          post_topic,
                          post_by)
                VALUES ('" . $_POST['reply-content'] . "',
                        NOW(),
                        " . mysql_real_escape_string($_GET['id']) . ",
                        " . $_SESSION['user_id'] . ")";
        $result = mysql_query($sql);
        if(!$result)
        {
            echo 'Your reply has not been saved, please try again later.';
        }
        else
        {
            echo 'Your reply has been saved, check out <a href="topic.php?id=' . htmlentities($_GET['id']) . '">the topic</a>.';
        }
    }
}
include 'footer.php';
?>




REFERENSI

Padje, Evert, 2010, How to Create a PHP/MySQL Powered Forum from Scratch, viewed 26 Februari 2013, http://net.tutsplus.com/tutorials/php/how-to-create-a-phpmysql-powered-forum-from-scratch/