Nairaland Database design

Hey Welcome back to continuation of Nairaland Forum Design, Here is the Nairaland.com like Forum Database Structure, this is a step-by-step tutorial to design a complete Nairaland forum system.
I have 10 tables here for now but as we grow in our development sure we will have more tables. The below ERD has explained the relation of this tables. Demo

Source Code

Users Tables

--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `email` varchar(70) NOT NULL,
  `password` varchar(100) NOT NULL,
  `status` int(5) NOT NULL,
  `avater` varchar(100) NOT NULL,
  `validcode` varchar(100) NOT NULL,
  `passreset` varchar(100) NOT NULL,
  `activeSince` int(11) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

PHP Code for email confirmation and Complete Registration

Change where you see http://www.thewallclone.com to your own domain address

HTML Form
You call this whatever you like

<form class="form-inline" action="do_confirm_email.php?" method="get"> 
      <div class="form-group"> <label for="exampleInputEmail2">Email</label> 
          <input type="email" name="email" SIZE="50" required class="form-control" id="exampleInputEmail2" placeholder="jane.doe@example.com"> </div>
           <button type="submit" name="invite" class="btn btn-primary">Send invitation</button> 
            <br/><small id="emailHelp" class="form-text text-muted">We'll never share your email with anyone else if you don't want to.</small>
      </form>

Let call this file do_comfirm_email.php

<?php
/* --------------------------------------------------------------------------------------- */
  if(isset($_GET['invite'])){
  $email = $_GET['email']; //Storing email in $email variable.
  $code=md5(uniqid(true)); // random alphernumeric character store in $code variable for verification

/*   checking if user already exist     */
$match = $db->query("SELECT * FROM users WHERE email ='$email' AND status='1' AND username!='0' "); 
@$mysql_num_rows = mysqli_num_rows($match);

/*   checking if requested to join     */
$requested = $db->query("SELECT * FROM users WHERE email ='$email' AND status='0' AND username='' "); 
@$num_rows = mysqli_num_rows($requested);
$data=mysqli_fetch_array($requested);

/* --------------------------------------------------------------------------------------- */

              if($mysql_num_rows)// if already requested, send new link!'
              { 
               echo ' <div class="section-title">
                  <h1 style="color:green"><strong>Already a member!</strong></h1>
                  <div class="divider"></div>
                </div>';

              } else
/* --------------------------------------------------------------------------------------- */
// if not registered run the below script,
if (!$num_rows) 
{

//echo "$email $code";

  $db->query("INSERT INTO users (email, validcode) VALUES ('$email', '$code')");  // insert into our table users with unique random code


  /* Send a registration link to entered email address */
  $to = $email;
  $subject = "Confirm registration";
  $header = "Nairaland Demo";
  $body = "Thank you for registering with us. You are now part of a great community 
  For active your account, visit the link below to complete your registration: 
  http://www.thewallclone.com/register?email=$email&validcode=$code
    (You must complete your registration within a few days)";

  $sent=mail($to,$subject,$body,$header);

/* if email is sent display success message */
if ($sent) {

  echo ' <div class="section-title">
    <h1 style="color:green"><strong>Please Check Your E-Mail!</strong></h1>
    <p>We have just sent you an e-mail.<p>Please <b>check your inbox</b> 
    to find the e-mail and <b>complete your registration</b>.
    <p>Please <b>check your spam folder</b> if you cannot find it in your inbox. <p>Welcome to Nairaland!<p>
    <div class="divider"></div>
  </div>';
      } /* end if statement for sent */

}
  /* --------------------------UPDATE REGISTRATION LINK--------------------------------------------- */
  if ($num_rows) {
   $dbCode=$data['validcode'];
   $dbEmail=$data['email'];
  // echo "$email $dbCode";
   /* Send a registration link to entered email address */
  $to = $email;
  $subject = "Confirm registration";
  $header = "Nairaland Demo";
  $body = "Thank you for registering with us. You are now part of a great community 
  For active your account, visit the link below to complete your registration: 
  http://www.thewallclone.com/register?email=$dbEmail&validcode=$dbCode
    (You must complete your registration within a few days)";

  $sent=mail($to,$subject,$body,$header);

/* if email is sent display success message */
if ($sent) {

  echo '<div class="section-title">
    <h1 style="color:green"><strong>Link resent!</strong></h1>
    
  </div>';
      } /* end if statement for sent */
/* --------------------------------------------------------------------------------------- */
}

} /* end if get statement */
?>

And call this register.php

after email as been confirmed the user will be redirected to this page to complete registration

<?php

 if(isset($_GET['validcode']) && ($_GET['email']) )
     {

     $code = $_GET['validcode'];
     $email = $_GET['email'];

     $match = $db->query("SELECT * FROM users WHERE email ='$email' AND validcode='$code' AND status='0' "); 
    $mysql_num_rows = mysqli_num_rows($match);
    $row=mysqli_fetch_array($match);
{
$uid=$row['uid'];
$validcode=$row['validcode'];
$email=$row['email'];


    if($mysql_num_rows)
{ 

 echo '<div class = "panel panel-default">   
   <div class = "panel-body">
      Complete Registration
   </div>
      <div class = "panel-heading">
      <form class="form-inline" action="success.php?email='.$email.'&token='.$validcode.'" method="post"> 
        <div class="form-group"> <label for="exampleInputName2">Name</label> 
        <input type="hidden" name="email" value="'.$email.'">
        <input type="text" name="username" class="form-control" id="exampleInputName2" placeholder="Jane Doe"> </div>

        <div class="form-group"> <label for="exampleInputEmail2">Password</label> 
          <input type="password" name="password" required class="form-control" id="exampleInputEmail2" placeholder=""> </div>
           <button type="submit" name="join" class="btn btn-primary">Join</button> 
      </form>
   </div>
</div>';
}

else
{
   echo ' <div class="section-title">
    <h1>An error occured!</h1>
    <div class="divider"></div>
  </div>
';
}
}
}

?>

Profile Table

--
-- Table structure for table `profile`
--
CREATE TABLE IF NOT EXISTS `profile` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `uid_fk` int(11) NOT NULL,
  `day` varchar(11) NOT NULL,
  `month` varchar(11) NOT NULL,
  `year` varchar(11) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `personal_text` varchar(100) NOT NULL,
  `signature` varchar(20) NOT NULL,
  `web_title` varchar(50) NOT NULL,
  `web_url` varchar(30) NOT NULL,
  `location` varchar(30) NOT NULL,
  `yim` varchar(30) NOT NULL,
  `twitter` varchar(30) NOT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Category Table

-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `url` varchar(50) NOT NULL,
  `des` varchar(20) NOT NULL,
  PRIMARY KEY (`cid`)
)

Sub_category Table

--
-- Table structure for table `sub_cat`
--

CREATE TABLE IF NOT EXISTS `sub_cat` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(50) NOT NULL,
  `surl` varchar(50) NOT NULL,
  `cid_fk` int(11) NOT NULL,
  PRIMARY KEY (`sid`)
)

Followed Board

--
-- Table structure for table `followed_boards`
--

CREATE TABLE IF NOT EXISTS `followed_boards` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `board_id_fk` int(11) NOT NULL,
  `user_id_fk` int(11) NOT NULL,
  `bdate` varchar(30) NOT NULL,
  PRIMARY KEY (`fid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

Topic Table

Topic Demo

--
-- Table structure for table `topics`
--
CREATE TABLE IF NOT EXISTS `topics` (
  `topic_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `link` varchar(100) NOT NULL,
  `content_text` text NOT NULL,
  `user_id_fk` int(11) NOT NULL,
  `board_id_fk` int(11) NOT NULL,
  `created` varchar(10) NOT NULL,
  PRIMARY KEY (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

Topic Comment

CREATE TABLE IF NOT EXISTS `topic_comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` text NOT NULL,
  `quote_id` int(11) NOT NULL,
  `topic_id` int(11) NOT NULL,
  `board_id_fk` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `creator` int(11) NOT NULL,
  `comment_id_fk` int(11) NOT NULL,
  `commentedOn` varchar(20) NOT NULL,
  PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=86 ;

Followed Topic Table

--
-- Table structure for table `followed_topics`
--

CREATE TABLE IF NOT EXISTS `followed_topics` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id_fk` int(11) NOT NULL,
  `user_id_fk` int(11) NOT NULL,
  `fdate` varchar(30) NOT NULL,
  PRIMARY KEY (`fid`)
)

Page View Table

--
-- Table structure for table `pageviews`
--

CREATE TABLE IF NOT EXISTS `pageviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `topic_id` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

PHP Code for Total View

$page=$pid; // for example

$pageviews = $db->query("SELECT * FROM pageviews WHERE topic_id='$page'");
$total_pageviews = mysqli_num_rows($pageviews);
$row=mysqli_fetch_array($pageviews);

$views=$row['views'];
$total=$views+1;
  //echo "$total";

if ($total_pageviews) {
  # code...
$db->query("UPDATE pageviews SET views='".$total."' WHERE topic_id='$page' ");
}
else
{
$db->query("INSERT INTO pageviews (topic_id,views) values('$page','1')");
}

echo number_format($total); //display number of page views

Total View

--
-- Table structure for table `total_visitors`
--

CREATE TABLE IF NOT EXISTS `total_visitors` (
  `id` int(11) NOT NULL,
  `session` varchar(100) NOT NULL,
  `ip` varchar(50) NOT NULL,
  `status` int(11) NOT NULL,
  `time` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

That is all for now stay back enjoy and subscribe as i bring you more tutorials at codexpress creative

Nairaland Database design

Source Code

Users Tables

PHP Code for email confirmation and Complete Registration

Profile Table

Category Table

Sub_category Table

Followed Board

Topic Table

Topic Comment

Followed Topic Table

Page View Table

Total View

Post a Comment

FeedBurner Alternative - Stop Using FeedBurner is Dead

Hot Posts

Labels

Most Recent

FeedBurner Alternative - Stop Using FeedBurner is Dead

Mailchimp api 3.0 check if email is subscribed Solution

Download Nairaland 9 Forum Script

How to create forgot password system in PHP