<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #forum-thread-functions
  require_once('app/forum-thread-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  //session variables
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);








?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <section class="main-container">

      <?php
      #show all the forums
      forum_overview($con, $session_tz);
      ?>

    </section>
  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(2);

  if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['change_password'])){

    #password to generate a new password and email link to activate it
    list($indicator, $data) = forgot_password($con, $_POST['username'], $_POST['email']);


  }


?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle()?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body class="body-center">
    <div class="login-container">
      <?php

        if(isset($indicator) && $indicator === TRUE){

          foreach($data as $msg)

          echo '

          <section class="message-container">
            <div class="message-area green">'.$msg.'</div>
          </section>

          ';
          die();

        }
        if(isset($indicator) && $indicator === FALSE){

          foreach($data as $msg){

            echo '

            <section class="message-container red-border">
              <div class="message-area red">'.$msg.'</div>
            </section>

            ';

          }
        }

      ?>
      <h2>Reset your password</h2>
      <form action="forgot_password.php" method="POST">
        <label for="username">Enter your username:</label>
        <input type="text" name="username" value="<?php if (isset($_POST['username'])) echo e($_POST['username']);?>" maxlength="" class="login-input">
        <label for="email">Enter your account email:</label>
        <input type="email" name="email" value="<?php if (isset($_POST['email'])) echo e($_POST['email']);?>" maxlength="" class="login-input">
        <div class="submit-con">
          <input type="submit" value="Reset Password" class="login-submit" name="change_password">
        </div><a href="login.php">Login</a>
      </form>
      <div class="login-links"></div>
    </div>
  </body>
</html>
<file_sep><?php
session_start();
/**
 * verify and activate account
 */
 #includes
   #sql connection
   require_once('_dependancies/sql.php');
   #functions
     #navigation
     require_once('app/navigation-functions.php');
     #login functions
     require_once('app/register-functions.php');
     #security
     require_once('app/security-functions.php');

if(isset($_GET['verify1']) && isset($_GET['verify2'])){

  $url_email = $_GET['verify1'];
  $url_key = $_GET['verify2'];

  activate_account($con, $url_email, $url_key) === TRUE ? header('Location: profile.php') : header('Location: login.php');
}else{
  #else redirect user
  header('Location: login.php');
}









?>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #functions
    #navigation
    require_once('app/navigation-functions.php');
    #login functions
    require_once('app/register-functions.php');
    #security
    require_once('app/security-functions.php');
    //start session
    #check the users login
    check_login(2);


    if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['register'])){

      //verify registration data
      list($flag, $errors) = verify_registration_data($con, $_POST['username'], $_POST['email'], $_POST['password'], $_POST['password2']);

      if($flag === TRUE ){

        activation_email($con, $_SESSION['username'], $_SESSION['email'], $_SESSION['key']) ? header('Location: register_activate.php') : header('Location: register_activate.php') ;

      }

    }

?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pagetitle()?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body class="body-center">
    <div class="login-container">
      <h2>Register:</h2>
      <form action="register.php" method="POST">
        <?php
        #any erros during registration
        if(!empty($errors)){
          foreach($errors as $msg){
            echo "- $msg<br>";
          }
        }

        ?>
        <label for="username">Choose your username:</label>
        <input type="text" name="username" value="<?php if(isset($_POST['username'])) echo e($_POST['username'])?>" maxlength="" class="login-input">
        <label for="email">Email:</label>
        <input type="email" name="email" value="<?php if(isset($_POST['email'])) echo e($_POST['email'])?>" maxlength="" class="login-input">
        <label for="password">Password:</label>
        <input type="<PASSWORD>" name="password" value="<?php if(isset($_POST['password'])) echo e($_POST['password'])?>" maxlength="" class="login-input">
        <label for="password2">Verify Password:</label>
        <input type="password" name="password2" value="<?php if(isset($_POST['password2'])) echo e($_POST['password2'])?>" maxlength="" class="login-input">
        <div class="submit-con">
          <input type="submit" value="Register" class="login-submit" name="register">
        </div><a href="">Login</a>
      </form>
      <div class="login-links"></div>
    </div>
  </body>
</html>
<file_sep><?php
/**
 * function to verify all the registration data
 * also calls the registration function
 */
function verify_registration_data($con, $username = '', $email='', $pass1 = '', $pass2 = ''){

  trim($username);
  trim($email);
  trim($pass1);
  trim($pass2);

  #initialize errors array
  $errors = array();
  #initialize key array
  $key = array();

  #username validation
    if(empty($username)){
      $errors[] = 'Please choose a username';
    }
    if(strlen($username > 20)){
      $errors[] = 'Max username size is 20 characters';
    }

  #email validation
    if(!empty($email)){
      if(filter_var($email, FILTER_VALIDATE_EMAIL)){
        $email;
      }else{
        $errors[] = 'Please ente your valid email address';
      }

    }else{
      $errors[] = 'Please enter your email address';
    }
    if(strlen($email > 100)){
      $errors[] = 'Max email size is 100 characters';
    }

  #passwords validation
    if(empty($pass1)){
      $errors[] = 'Please enter your chosen password';
    }
    if(empty($pass2)){
      $errors[] = 'Please confirm your password';
    }
    if(($pass1 === $pass2) === FALSE){
      $errors[] = 'Passwords do not match';
    }else{
      $pass1;
    }

  #if errors is empty check if the username of email is taken
    if(empty($errors)){

      #see if username is already taken(in users table and limbo table)
        $sql = "SELECT (SELECT COUNT(id_users) FROM users WHERE username=?) + (SELECT COUNT(id_limbo) FROM limbo WHERE username=?) AS total";
        $stmt = mysqli_prepare($con, $sql);
        mysqli_stmt_bind_param($stmt, 'ss', $username, $username);
        mysqli_execute($stmt);
        mysqli_stmt_bind_result($stmt, $sum);
        while(mysqli_stmt_fetch($stmt)){
          $sum;
        }
        mysqli_stmt_free_result($stmt);
        if($sum > 0){
          $errors[] = 'Username Taken';
          return array(FALSE, $errors);
        }
      #see if email is already taken
        $sql = "SELECT (SELECT COUNT(id_users) FROM users WHERE email=?) + (SELECT COUNT(id_limbo) FROM limbo WHERE email=?) AS total";
        $stmt = mysqli_prepare($con, $sql);
        mysqli_stmt_bind_param($stmt, 'ss', $email, $email);
        mysqli_execute($stmt);
        mysqli_stmt_bind_result($stmt, $sum);
        while(mysqli_stmt_fetch($stmt)){
          $sum;
        }
        mysqli_stmt_free_result($stmt);
        if($sum > 0){
          $errors[] = 'Email Taken';
          return array(FALSE, $errors);
        }
      #register the user
      list($flag, $errors, $key) = register($con, $username, $email, $pass1);
      #set super global variables for later use
      if(empty($errors) && isset($key) && !empty($key)){
        $_SESSION['username'] = $username;
        $_SESSION['email'] = $email;
        $_SESSION['key'] = $key[0];
        $_SESSION['resent'] = 0;
        return array(TRUE, $errors);
      }else{
        return array(FALSE, $errors);
      }


    }else{
      return array(FALSE, $errors);
    }

}

/**
 * function to register the user
 * this function is called in the verify_registration_data function
 * inserts the user registration data in the limbo table in the database
 * returns error if there was a problem and a activation key if everything went according to plan
 */
function register($con, $username, $email, $password){
  #initilizee errors array
  $errors = array();
  #initialize key array
  $key = array();
  #sha1 version of email
  $sha1_email = sha1($email);

  #generate a unique random activation key
  $activation_key = sha1(uniqid(rand()));

  $sql =  "INSERT INTO limbo(username, email, email_sha1, password, activation_code, date) VALUES(?,?,SHA1(?),?, UTC_TIMESTAMP())";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 'sssss', $username, $email, $sha1_email, $password, $activation_key);
  mysqli_execute($stmt);

  if(mysqli_stmt_affected_rows($stmt) === 1){
    mysqli_stmt_free_result($stmt);
    $key[] = $activation_key;
    return array(TRUE, $errors, $key);

  }else{
    $errors[] = 'There was a problem Please try again';
    return array(FALSE, $errors);
  }

}
/**
 * function sends the email for account activation
 * verify1 = email
 * verify2 = activation keycode
 */
function activation_email($con, $username, $email, $key){

  #intialize errors array
  $errors = array();

  $link = 'activate.php?verify1='. sha1(urlencode($email)) .'&verify2='. urlencode($key) .' ';

  $to = $email;
  $subject = "Activate your account";
  $body = '

  <a href="' . $link . '>Activate your acount</a>"

  ';
  $headers = 'no-reply';

  if(@mail($to, $subject, $body, $headers)){
    return TRUE;
  }else{
    return FALSE;
  }

}

/**
 * function to activate users account
 * validates the data from the url and moves users account from limbo table to users table
 * also logs the user in after activation of account
 */
function activate_account($con, $url_email = '', $url_key = ''){

  #if strigns are empty redirect user
  if(empty($url_email) && empty($url_key)){
    isset($_SESSION['user_id']) ? header('Location: forum_overview.php') : header('Location: login.php');
  }
  /**
   * section will use a mysql transaction to move users information from limbos table to the users table
   */
     #start transaction
      mysqli_autocommit($con, FALSE);
      //default image
      $image = addslashes(file_get_contents($image = '_dependancies/profiles/default.png'));

      $sql = "SELECT username, email, password FROM limbo WHERE email_sha1=? AND activation_code=?";
      $stmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($stmt, 'ss', $url_email, $url_key);
      mysqli_execute($stmt);
      $result = mysqli_stmt_get_result($stmt);

       $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
       $username = $row['username'];
       $email = $row['email'];
       $password = $row['<PASSWORD>'];

       $rand = sha1(uniqid(rand()));

       $sql = "INSERT INTO users(id_users_sha1, username, password, email, profile, activation_date) VALUES(?,?,?,?,?, UTC_TIMESTAMP())";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'sssss', $rand, $username, $password, $email, $image);
       mysqli_execute($stmt);

       $sql = "DELETE FROM limbo WHERE email_sha1=? AND activation_code=? LIMIT 1";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'ss', $url_email, $url_key);
       mysqli_execute($stmt);

    #commit transaction
      if(mysqli_commit($con)){
        #if commited logged the user in
          $sql =
          "SELECT users.id_users, users.id_users_sha1 AS id_sha1, time_zones.name_time_zones AS tz
          FROM users
          JOIN time_zones ON time_zones.id_time_zones = users.time_zone
          WHERE username=? AND email=? AND password=?";
          $stmt = mysqli_prepare($con, $sql);
          mysqli_stmt_bind_param($stmt, 'sss', $username, $email, $password);
          mysqli_execute($stmt);
          $result = mysqli_stmt_get_result($stmt);
          $user_id = mysqli_fetch_array($result, MYSQLI_ASSOC);
          #set the user id
          $_SESSION['user_id'] = $user_id['id_users'];
          $_SESSION['secure'] = $user_id['id_sha1'];
          $_SESSION['timezone'] = $user_id['tz'];
        #return the function as true if the user is logged in
        return TRUE;

      }else{
        return FALSE;
      }

}
<file_sep><?php
session_start();

$_SESSION = array();
session_destroy();
setcookie('PHPSESSID', '', time()-3600);


header('Location: login.php');

?>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(2);

  if(isset($_GET['reset1']) && isset($_GET['reset2'])){
    #call the reset password function to reset it in do transaction on database to update
    reset_password($con, $_GET['reset1'], $_GET['reset2']);

}else{
  header('Location: login.php');
}




?>
<file_sep><?php
#initialize flag
  $flag = '';
/**
 * Function handles the login of the user
 * returns error messages if criteria is not met
 * sets the user session with the user id
 * $con = the connection of the database
 * $email is the user email
 * $password is the user password
 */
function login($con, $email = '', $password = ''){

  trim($email);
  trim($password);

  #initialize errors array
  $errors = array();

  #email validation
    if(!empty($email)){
      if(filter_var($email, FILTER_VALIDATE_EMAIL)){
        $email;
      }else{
        $errors[] = 'Please enter your valid email address';
      }

    }else{
      $errors[] = 'Please enter your email address';
    }

  #password validation
    if(!empty($password)){
      $password;
    }else{
      $errors[] = 'Please enter your password';
    }

  #if errors is empty check the password and email
    if(empty($errors)){

      $sql =
      "SELECT users.id_users, users.id_users_sha1 AS secure, time_zones.name_time_zones AS tz
      FROM users
      JOIN time_zones ON time_zones.id_time_zones = users.time_zone
      WHERE email=? AND password=<PASSWORD>(?)";
      $stmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($stmt, 'ss', $email, $password);
      mysqli_execute($stmt);
      mysqli_stmt_bind_result($stmt, $user_id, $secure, $tz);
      mysqli_stmt_store_result($stmt);
      $num = mysqli_stmt_num_rows($stmt);

      if(mysqli_stmt_num_rows($stmt) === 1){
        #check first the the user hasent request that there password must be changed
        while(mysqli_stmt_fetch($stmt)){

          $sql_check = "SELECT id_user FROM forgot_password WHERE id_user=?";
          $stmt_check = mysqli_prepare($con, $sql_check);
          mysqli_stmt_bind_param($stmt_check, 'i', $user_id);
          mysqli_execute($stmt_check);
          mysqli_stmt_store_result($stmt_check);
          if(mysqli_stmt_num_rows($stmt_check) === 1){
            $errors[] = 'Please check your email you have requested that your password be changed';
            return array(FALSE, $errors);
          }
          #set the user session upon successful login
          $_SESSION['user_id'] = $user_id;
          $_SESSION['secure'] = $secure;
          $_SESSION['timezone'] = $tz;
          break;
        }
        require_once ('additional-functionality-functions.php');
        user_logs($con, $_SESSION['user_id']);
        return array(TRUE, $errors);
      }else{
        $errors[] = 'Wrong email password combination';
        return array(FALSE, $errors);
      }

    }else{
      return array(FALSE, $errors);
    }


}
<file_sep><?php
#start session
session_start();
#includes
  #sql connection
  require_once('sql.php');
  #navigation
  require_once('../_public/app/navigation-functions.php');
  #security
  require_once('../_public/app/security-functions.php');
  #check the users login
  check_login(1);

  $id = $_GET['id'];


  $sql = "SELECT profile from users WHERE id_users_sha1=?";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 's', $id);
  mysqli_execute($stmt);
  $result = mysqli_stmt_get_result($stmt);
  $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
  $image = stripslashes($row['profile']);

  /*
    can use this section if you want to do a more advance proxy
    you'll need save the content type in the database and run a query to run the
    required content-type
    header("Content-type: image/png");
    header("Content-type: image/jpg");
  */

  echo $image;







?>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #functions
    #navigation
    require_once('app/navigation-functions.php');
    #login functions
    require_once('app/register-functions.php');
    #security
    require_once('app/security-functions.php');
    #start session
    #check the users login
    check_login(2);


    if($_SERVER['REQUEST_METHOD'] === 'POST' AND isset($_POST['resend'])){

      activation_email($con, $_SESSION['username'], $_SESSION['email'], $_SESSION['key']);
      #spam delayer
      $_SESSION['resent'] = $_SESSION['resent'] + 1;
      ($_SESSION['resent'] > 0 && $_SESSION['resent'] <= 4) ? sleep($_SESSION['resent'] * 5) : NULL ;
      #if user has requested to many resends, destroy there session and redirect to login
      if($_SESSION['resent'] >= 5){
        $_SESSION = array();
        session_destroy();
        header('Location: login.php');
      }
    }else{
      #else redirect user to login
      header('Location: login.php');
    }


?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle() ?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body class="body-center">
    <div class="login-container">
      <h3 class="activate-h3">Activate your account with the link in your email</h3>
      <?php

      if($_SESSION['resent'] > 0){

        echo '<p class="sent">We have resent you the email, check your spam box</p>';

      }


      ?>
      <div class="register-activate-container">
        <form action="register_activate.php" method="POST">
          <p class="activate-p">Did not get it?</p>
          <input type="submit" value="Resend email" class="resend" name="resend">
        </form>
      </div>
    </div>
  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #functions
    #navigation
    require_once('app/navigation-functions.php');
    #login functions
    require_once('app/login-functions.php');
    #security
    require_once('app/security-functions.php');

    #check the users login
    check_login(2);

    if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['login'])){

      #use the login function if the form was activated
      list($flag, $errors) = login($con, $_POST['email'], $_POST['password']);
      #redirect login function logs user in
      $flag === TRUE ? header('Location: forum_overview.php') : NULL;

    }

?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle()?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body class="body-center">
    <div class="login-container">
      <?php
      #output errors if any
        if($flag === FALSE){
          foreach($errors as $msg){
            echo "- $msg<br>";
          }
        }

      ?>
      <h2>Login:</h2>
      <form action="login.php" method="POST">
        <label for="email">Email:</label>
        <input type="email" name="email" value=" <?php if(isset($_POST['email'])) echo e($_POST['email'])?>" maxlength="" class="login-input">
        <label for="password">Password:</label>
        <input type="password" name="password" value="<?php if(isset($_POST['password'])) echo e($_POST['password'])?>" maxlength="" class="login-input">
        <div class="submit-con">
          <input type="submit" value="Login" class="login-submit" name="login">
        </div>
      </form>
      <div class="login-links"><a href="forgot_password.php">Forgot-password</a><a href="register.php">Register</a></div>
    </div>
  </body>
</html>
<file_sep><?php
/**
 * function to validate file types and insert them into the database
 */
function verify_file(){

  #initialize errors array
  $errors = array();

  #array of allowed file types (MIME TYPES)
    $allowed_filetypes =
    array('image/png',
          'image/x-png',
          'image/jpg',
          'image/jpeg',
          'image/pjeg'
        );
  #allowed extensions
    $allowed_extensions =
    array('png',
          'x-png',
          'jpg',
          'jpeg',
          'pjeg'
        );


  $max_size = 5242880; #5MB

  #file superglobals
    #get pics original name
      $pic_orig_name = strtolower($_FILES['profile']['name']);
    #get the images name on the server
      $pic_name = strtolower($_FILES['profile']['tmp_name']);
    #get the images size
      $pic_size = $_FILES['profile']['size'];
    #get the images extension
      $pic_type = strtolower($_FILES['profile']['type']);


  #check the images size
    if($pic_size > $max_size){
      $errors[] = 'Max file upload is 5MB';
      return array(FALSE, $errors);
    }
  #check if file type is an image
    if(!getimagesize($pic_name)){
      $errors[] = 'Only image files allowed(png and jpg)';
      return array(FALSE, $errors);
    }

  #check magic bytes
    #handler( finfo extension )
      $fileinfo = finfo_open(FILEINFO_MIME_TYPE);
    #check the actual magic bytes
      if(!in_array(finfo_file($fileinfo, $pic_name), $allowed_filetypes)){
        $errors[] = 'Only png and jpg files allowed';
        return array(FALSE, $errors);
      }
  #check mime type ( mime_content_type )
    if(!in_array(mime_content_type($pic_name), $allowed_filetypes)){
      $errors[] = 'Only png and jpg files allowed';
      return array(FALSE, $errors);
    }
  #check mime with ( $_FILES )
    if(!in_array($pic_type, $allowed_filetypes)){
      $errors[] = 'Only png and jpg files allowed';
      return array(FALSE, $errors);
    }


  #check file extensions(that the files extenstions are what is allowed)
    if(!in_array(pathinfo($pic_orig_name, PATHINFO_EXTENSION), $allowed_extensions)){
      $errors[] = 'Only png and jpg files allowed';
      return array(FALSE, $errors);
    }

    if(empty($errors)){
      return array(TRUE, $errors);
    }

}

/**
 * function to sanitize output
 */
  function e($value){
    return htmlspecialchars($value, ENT_QUOTES, 'utf-8');
  }


/**
 * check if require session variables are set
 */
  function check_login($flag){

    $pagename = basename(e($_SERVER['PHP_SELF']));

    #flag 1
    if($flag === 1){

      #pagenames that need user_id, secure, timezone session variables
        $pages = array(
          'Change Password',
          'Create Thread',
          'Forums',
          'Threads',
          'Viewing Thread',
          'Your Profile',
          'Threads you created',
          'Your Posts',
          'Viewing image'
        );
      #if page is in array and certain variables are not set redirect the user to the login
        if(in_array(pagename($pagename), $pages) && (!isset($_SESSION['user_id']) || !isset($_SESSION['secure']) || !isset($_SESSION['timezone']))){
          #delete any redudant session data beforeredirecting user
          $_SESSION = array();
          session_destroy();
          setcookie('PHPSESSID', '', time()-3600);

          header('Location: login.php');

        }


    }
    #flag2
    if($flag === 2){

      #pagenames that need user_id, secure, timezone session variables
        $pages = array(
          'Forgot Password',
          'Login',
          'Activate your account',
          'Register',
          'Reset password'
        );
      #if page is in array and certain variables are not set redirect the user to the login
        if(in_array(pagename($pagename), $pages) && (isset($_SESSION['user_id']) || isset($_SESSION['secure']) || isset($_SESSION['timezone']))){

          #redirect the user to forum_overview

          header('Location: forum_overview.php');

        }


    }

}



/**
 * function to validate url data
 */
function validate_url($flag, $con, $id1 = '', $id2 = ''){

  #pages that require id 1
  if($flag === 1){
    #id 1 empty redirect user
    if(empty($id1)){
      header('Location: forum_overview.php');
    }
    #make sure that id1 is correct data tpye and correct length
    if(!ctype_alnum($id1) || strlen($id1) != 40){
      header('Location: forum_overview.php');
    }
    #validate id1
    $sql = "SELECT COUNT(id_forum_sha1) FROM forum_overview WHERE id_forum_sha1=?";
    $stmt = mysqli_prepare($con, $sql);
    mysqli_stmt_bind_param($stmt, 's', $id1);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_array($result, MYSQLI_NUM);

    if($row[0] != 1){
      mysqli_stmt_free_result($stmt);
      mysqli_stmt_close($stmt);
      header('Location: forum_overview.php');
    }


  }

  #pages the require id1 and id2
  if($flag === 2){
    #validate
      if(empty($id1) || empty($id2)){
        header('Location: forum_overview.php');
      }
      if(!ctype_alnum($id1) || !ctype_alnum($id2) || strlen($id1) != 40 || strlen($id2) != 40){
        header('Location: forum_overview.php');
      }

    #check database
    $sql =
    "SELECT forum_overview.id_forum_sha1, threads.id_thread_sha1
     FROM forum_overview
     JOIN threads ON forum_overview.id_forum = threads.id_forum
     WHERE forum_overview.id_forum_sha1=? AND threads.id_thread_sha1=?
     ";
    $stmt = mysqli_prepare($con, $sql);
    mysqli_stmt_bind_param($stmt, 'ss', $id1, $id2);
    mysqli_execute($stmt);
    mysqli_stmt_store_result($stmt);

    if(mysqli_stmt_num_rows($stmt) === 0){
      mysqli_stmt_free_result($stmt);
      mysqli_stmt_close($stmt);
      header('Location: forum_overview.php');
    }

  }


}
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #forum-thread
  require_once('app/forum-thread-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  #validate url
  validate_url(1, $con, $_GET['id1']);
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);
  #get forum hash from url
  $forum_id = $_GET['id1'];

  #check if the form to create the thread has been submitted
  if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['create_thread'])){

    $forum_to_create = $_POST['list_of_threads'];
    $subject = $_POST['subject'];
    $body = $_POST['body'];

    list($indicator, $data) = create_thread($con, $forum_to_create, $subject, $body, $session_id, $forum_id);


  }




?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <?php
    if(isset($indicator) && $indicator === FALSE){

      foreach($data as $msg){

        echo '

        <section class="message-container red-border">
          <div class="message-area red">'.$msg.'</div>
        </section>

        ';

      }
    }

    ?>

    <section class="main-container">
      <div class="create-thread-container">
        <div class="form-container">
          <h1>Create your Thread</h1>
          <form action="<?php echo create_thread_urlaction($_SERVER['PHP_SELF'], $forum_id); ?>" method="POST">
            <div class="thread-head">
              <label for="thread-dropdown" class="thread-label-select">Create thread in forum:</label>
              <?php
              #get forum id from url to remember in drop down seletion(sticky form)
              get_forum_id($con, $forum_id);

              ?>

            </div>
            <div class="create-thread-subject">
              <label for="subject" class="thread-label">Thread: subject</label>
              <input name="subject" class="thread-subject" value="<?php if(isset($_POST['subject'])) echo e($_POST['subject']);?>">
            </div>
            <div class="create-thread-body">
              <label for="body" class="thread-label">Thread: body(message)</label>
              <textarea name="body"><?php if (isset($_POST['body'])) echo e($_POST['body']);?></textarea>
            </div>
            <input type="submit" value="Create Thread" class="thread-submit" name="create_thread">
          </form>
        </div>
      </div>
    </section>




  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #forum-thread
  require_once('app/forum-thread-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  #validate url
  validate_url(2, $con, $_GET['id1'], $_GET['id2']);
  //session variables
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);
  #flag for which pagniation page
  $p_flag = 2;
  //log thread view
  thread_view_log($con, $_GET['id1'], $_GET['id2'], $session_id);

if(isset($_GET['id1']) && strlen($_GET['id1']) === 40 && isset($_GET['id2']) && strlen($_GET['id2']) === 40){

    #set url values to variables
    $forum_id = $_GET['id1'];
    $thread_id = $_GET['id2'];

    $y = 10;

    //see if pages has been set
    if(isset($_GET['pages']) && is_numeric($_GET['pages'])){
      $total_pages = $_GET['pages'];
    }else{
      $sql =
      "SELECT COUNT(posts.id_thread)
       FROM posts
       JOIN threads ON threads.id_thread = posts.id_thread
       WHERE threads.id_thread_sha1=?";
      $cstmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($cstmt, 's', $thread_id);
      mysqli_execute($cstmt);
      $result = mysqli_stmt_get_result($cstmt);
      $row = mysqli_fetch_array($result, MYSQLI_NUM);
      $total_records = $row[0];

      if($total_records > $y){
        $total_pages = ceil($total_records / $y);
      }else{
        $total_pages = 1;
      }
      mysqli_stmt_free_result($cstmt);
    }
    // databse start point
    if(isset($_GET['x']) && is_numeric($_GET['x'])){
      $x = $_GET['x'];
    }else{
      $x = 0;
    }

}

#section for posting a reply
if($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['post-in-thread'])){


  list($indicator, $data) = reply($con, $session_id, $_POST['message'], $forum_id, $thread_id);


}

?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <?php

      if($indicator === TRUE){

        foreach($data as $msg)

        echo '

        <section class="message-container">
          <div class="message-area green">'.$msg.'</div>
        </section>

        ';

      }
      if($indicator === FALSE){

        foreach($data as $msg){

          echo '

          <section class="message-container red-border">
            <div class="message-area red">'.$msg.'</div>
          </section>

          ';

        }
      }

    ?>


    <section class="main-container orange-border">

      <?php
      thread_post($con, $session_tz, $thread_id, $x, $y);
      ?>

    </section>


    <div class="pagination-container">
      <div class="pagination">
        <div class="pagination-links">
          <div class="tree">
            <?php navigation_modal($con, $forum_id);?>
          </div>
          <div class="links-contain">

            <?php
            pagination($p_flag, $total_pages, $forum_id, $thread_id , $x, $y);
            ?>

          </div>
        </div>
      </div>
    </div>


    <section class="main-container">

      <?php

      thread_replies($con, $session_tz, $thread_id, $x, $y);

      ?>
    </section>


    <section class="main-container blue-border">
      <div class="post-reply-container">
        <h2>Post a Reply<a name="reply"></a></h2>
        <div class="post-group">
          <div class="post-left">
            <div class="post-img"><?php echo "<img class=\"post-img1\" src=../_dependancies/image_proxy.php?id=$session_secure>"; ?></div>
          </div>
          <div class="post-right">
            <form action="<?php echo action_url($_SERVER['PHP_SELF'], $_GET['id1'], $_GET['id2']); ?>" method="POST">
              <textarea placeholder="Post a reply" name="message"><?php if(isset($_POST['message'])) echo e($_POST['message'])?></textarea>
              <input name="post-in-thread" type="submit" value="Post a Reply" class="post-in-thread">
            </form>
          </div>
        </div>
      </div>
    </section>


    <div class="pagination-container">
      <div class="pagination">
        <div class="pagination-links">
          <div class="tree">
            <?php navigation_modal($con, $forum_id);?>
          </div>
          <div class="links-contain">

            <?php
            pagination($p_flag, $total_pages, $forum_id, $thread_id , $x, $y);
            ?>

          </div>
        </div>
      </div>
    </div>







  </body>
</html>
<file_sep># Retro forum

### Installation instructions:

##### For viewing purposes:
_dependacies/_db contains the database files, you can use **dummy-data.sql** if you want to view the site with data, after importing the database you will have to set up a username and password manually within the database client-in the **users** table, to bypass email activation

<file_sep><?php

/**
 *  function to get users profile information
 */
function get_profile($con, $user_id, $session_timezone){

  #get the current users profile information
  $sql = "SELECT id_users_sha1, username, email, time_zone, signature, CONVERT_TZ(activation_date, 'UTC', ?) FROM users WHERE id_users=?";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 'si', $session_timezone, $user_id );
  mysqli_execute($stmt);
  mysqli_stmt_bind_result($stmt, $id_sha1, $username, $email, $user_timezone, $signature, $activation_date);
  mysqli_stmt_store_result($stmt);

  #get the timezon information
  $sql_tz = "SELECT id_time_zones, name_time_zones FROM time_zones";
  $stmt_tz = mysqli_prepare($con, $sql_tz);
  mysqli_execute($stmt_tz);
  mysqli_stmt_bind_result($stmt_tz, $id_tz, $name_tz);
  mysqli_stmt_store_result($stmt_tz);

  while(mysqli_stmt_fetch($stmt)){

    echo '
    <img class="profile-img" img src="_dependancies/image_proxy.php?id='. e($id_sha1) .'">
    </div>
  <div class="profile-details-contain">
    <div class="profile-header">
      <h1>Viewing Your Profile</h1><a href="change_password.php">Change password</a>
    </div>
    <h3>Account activate on: '.e($activation_date).'</h3>
    <h3>Username:</h3>
    <input type="text" placeholder="'.e($username).'" class="text-grey-input" readonly >
    <h3>Email:</h3>
    <input type="text" placeholder="'.e($email).'" class="text-grey-input" readonly >
    <h3>Signature:</h3>
    <textarea name="signature">'.e($signature).'</textarea>
    ';
    $user_timezone;
  }

    echo '
    <h3>Your Timezone</h3>
      <select  name="timezone" class="timezone">
    ';

    while(mysqli_stmt_fetch($stmt_tz)){

      echo'

        <option value="'.e($id_tz).'"';

        if($id_tz === $user_timezone){
          echo ' SELECTED>';
        }else{
          echo '>';
        }

      echo ''.e($name_tz).'</option>';

    }
    echo '

      </select>

    ';

  mysqli_stmt_free_result($stmt);
  mysqli_stmt_free_result($stmt_tz);
}



/**
 * function to update users profile
 * also uses the verify file function to validate profile picture information
 */

 function update_profile($con, $profile_pic = '', $timezone, $signature, $session_id, $session_secure, $session_tz){

   if(strlen($signature) > 250){
     $errors[] = 'Max signature length is 250 characters';
     return array(FALSE, $errors);
   }

   #initilize success array
   $success = array();

  if(!empty($profile_pic)){

    list($data, $errors) = verify_file();

    if($data === TRUE){

      $image = addslashes(file_get_contents($profile_pic));

      $sql_image = "UPDATE users SET time_zone=?, profile=?, signature=? WHERE id_users=?";
      $stmt_image = mysqli_prepare($con, $sql_image);
      mysqli_stmt_bind_param($stmt_image, 'issi', $timezone, $image, $signature, $session_id);
      mysqli_execute($stmt_image);

      if(mysqli_stmt_affected_rows($stmt_image) === 1){
        #remove the uplaoded image from the server after it has been succesfully inserted into the database
        unlink($profile_pic);
        $success[] = 'Your profile has been updated';

        #update session info
        $sql_up_tz =
        "SELECT users.id_users, users.id_users_sha1 AS secure, time_zones.name_time_zones AS tz
        FROM users
        JOIN time_zones ON time_zones.id_time_zones = users.time_zone
        WHERE users.id_users=?";
        $stmt_up_tz = mysqli_prepare($con, $sql_up_tz);
        mysqli_stmt_bind_param($stmt_up_tz, 'i', $session_id);
        mysqli_execute($stmt_up_tz);
        mysqli_stmt_bind_result($stmt_up_tz, $user_id, $secure, $tz);
        mysqli_stmt_store_result($stmt_up_tz);
        //echo $num = mysqli_stmt_num_rows($stmt);

        if(mysqli_stmt_num_rows($stmt_up_tz) === 1){
          #set the user session upon successful login
          while(mysqli_stmt_fetch($stmt_up_tz)){
            $_SESSION['timezone'] = $tz;
            break;
          }
          mysqli_stmt_free_result($stmt_up_tz);
          #echo to user profile has been updated
          return array(TRUE, $success);
        }
      }
      mysqli_stmt_free_result($stmt_image);
   }else{
     return array(FALSE, $errors);
   }
 }#end of not empty profile picture if



  if(empty($profile_pic)){

   #update information in database
   $sql_no_image = "UPDATE users SET time_zone=?, signature=? WHERE id_users=? LIMIT 1";
   $stmt_no_image = mysqli_prepare($con, $sql_no_image);
   mysqli_stmt_bind_param($stmt_no_image, 'isi', $timezone, $signature, $session_id);
   mysqli_execute($stmt_no_image);

   if(mysqli_stmt_affected_rows($stmt_no_image) === 1){

     $success[] = 'Your profile has been updated';

     #update session info
     $sql_up_tz =
     "SELECT users.id_users, users.id_users_sha1 AS secure, time_zones.name_time_zones AS tz
     FROM users
     JOIN time_zones ON time_zones.id_time_zones = users.time_zone
     WHERE users.id_users=?";
     $stmt_up_tz = mysqli_prepare($con, $sql_up_tz);
     mysqli_stmt_bind_param($stmt_up_tz, 'i', $session_id);
     mysqli_execute($stmt_up_tz);
     mysqli_stmt_bind_result($stmt_up_tz, $user_id, $secure, $tz);
     mysqli_stmt_store_result($stmt_up_tz);
     //echo $num = mysqli_stmt_num_rows($stmt);

     if(mysqli_stmt_num_rows($stmt_up_tz) === 1){
       #set the user session upon successful login
       while(mysqli_stmt_fetch($stmt_up_tz)){
         $_SESSION['timezone'] = $tz;
         break;
       }
       mysqli_stmt_free_result($stmt_up_tz);
       return array(TRUE, $success);
     }
  }
  mysqli_stmt_free_result($stmt_no_image);

}#end of empty profile picture if

}#end of function




/**
 * function to ge your posts or you threads pages data
 */
 function your_posts_threads($flag, $con, $x, $y, $session_id, $session_tz){
   #your_posts
   if($flag === 1){
     #view only where thread is distinct
     $sql =
      "SELECT posts.id_posts, MIN(posts.date_posted) as firstReplied, posts.message_post, threads.title_thread, threads.id_user, threads.id_thread_sha1, users.username, forum_overview.id_forum_sha1
       FROM posts
       JOIN threads ON threads.id_thread = posts.id_thread
       JOIN forum_overview ON forum_overview.id_forum = threads.id_forum
       JOIN users ON users.id_users = threads.id_user WHERE posts.id_user = ?
       GROUP BY threads.id_thread
       LIMIT ?,?";
      $stmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($stmt, 'iii', $session_id, $x, $y);
      mysqli_execute($stmt);
      mysqli_stmt_bind_result($stmt, $id_post, $firstreplied, $message, $title_thread, $threads_id_user, $id_thread_sha1, $username, $id_forum_sha1);
      mysqli_stmt_store_result($stmt);

      while(mysqli_stmt_fetch($stmt)){
        #modify data for better display pusposes
        $title_thread = strlen($title_thread) > 50 ? substr($title_thread, 0, 50).'...' : $title_thread;
        $message = strlen($message) > 250 ? substr($message, 0, 250).'...' : $message;
        #check if thread was created by the user
        $username = $threads_id_user === $session_id ? 'You created this thread' : $username;

        echo '

        <div class="your-threads-container">
          <div class="go-to-thread">
            <div class="modal-forum">
              <div class="modal-text1">Thread:</div>
              <div class="modal-text2">'.e($title_thread).'</div>
            </div><a class="go-to-thread-btn" href="view_thread.php?id1='.e($id_forum_sha1).'&id2='.e($id_thread_sha1).'">Go to this thread</a>
          </div>
          <div class="your-threads-main">
            <div class="your-threads-left">
              <div class="combo-your-threads">
                <div class="combo-your-threads-heading">Thread Created by:</div>
                <div class="combo-your-threads-data">'.e($username).'</div>
              </div>
              <div class="combo-your-threads">
                <div class="combo-your-threads-heading">Date posted a reply: </div>
                <div class="combo-your-threads-data">'.e($firstreplied).'</div>
              </div>
            </div>
            <div class="your-threads-right">
              <div class="h2-right">Your post:</div>
              <p>'.e($message).'</p>
            </div>
          </div>
        </div>

        ';

      }
      mysqli_stmt_free_result($stmt);

   }

   #your_threads
   if($flag === 2){

     #the query data
      $sql =
      "SELECT threads.id_thread, threads.id_thread_sha1, threads.title_thread, threads.body_thread, DATE_FORMAT(threads.created_on, '%d-%b-%y %h:%i %p') AS created, count(posts.id_thread) AS totalposts, IFNULL(DATE_FORMAT(CONVERT_TZ(max(posts.date_posted), 'UTC', ?), '%d-%b-%y %h:%i %p'), 'No replies') AS lastposted, forum_overview.name_forum, forum_overview.id_forum_sha1, users.username
       FROM threads
       LEFT JOIN posts ON threads.id_thread = posts.id_thread
       JOIN forum_overview ON forum_overview.id_forum = threads.id_forum
       JOIN users ON users.id_users = threads.id_user
       WHERE threads.id_user=?
       GROUP BY threads.id_thread
       ORDER BY totalposts DESC, created DESC
       LIMIT ?,? ";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'siii', $session_tz, $session_id, $x, $y);
       mysqli_execute($stmt);
       mysqli_stmt_bind_result($stmt, $id_thread, $id_thread_sha1, $thread_title, $thread_body, $created, $totalposts, $lastposted, $name_forum, $id_forum_sha1, $username);
       mysqli_stmt_store_result($stmt);


    while(mysqli_stmt_fetch($stmt)){
      #modify data for better display pusposes
      $thread_title = strlen($thread_title) > 75 ? substr($thread_title, 0, 75).'...' : $thread_title;
      $thread_body = strlen($thread_body) > 250 ? substr($thread_body, 0, 250).'...' : $thread_body ;

      echo '

      <div class="your-threads-container">
        <div class="go-to-thread">
          <div class="modal-forum">
            <div class="modal-text1">Created in forum:</div>
            <div class="modal-text2">'.e($name_forum).'</div>
          </div><a class="go-to-thread-btn" href="view_thread.php?id1='.e($id_forum_sha1).'&id2='.e($id_thread_sha1).'">Go to this thread</a>
        </div>
        <div class="your-threads-main">
          <div class="your-threads-left">
            <div class="combo-your-threads">
              <div class="combo-your-threads-heading">You created this thread on:</div>
              <div class="combo-your-threads-data">'.e($created).'</div>
            </div>
            <div class="combo-your-threads">
              <div class="combo-your-threads-heading">Last reply to thread: </div>
              <div class="combo-your-threads-data">'.e($lastposted).'</div>
            </div>
            <div class="combo-your-threads">
              <div class="combo-your-threads-heading">Number of posts in thread:</div>
              <div class="combo-your-threads-data">'.e($totalposts).'</div>
            </div>
          </div>
          <div class="your-threads-right">
            <div class="h2-right">'.e($thread_title).'</div>
            <p>'.e($thread_body).'</p>
          </div>
        </div>
      </div>



      ';


    }
    mysqli_stmt_free_result($stmt);



   }


 }

 /**
  * function to change users password
  */
  function change_password($con, $password = '', $new_<PASSWORD>1 = '', $new_<PASSWORD>2, $session_id){

    #errors array
    $errors = array();
    #succss array
    $succes = array();

    #validate input
    if(empty($password)){
      $errors[] = 'Please enter your password';
    }
    if(empty($new_password1)){
      $errors[] = 'Please enter your new password';
    }
    if(empty($new_password2)){
      $errors[] = 'Please confirm your new password';
    }
    if(!empty($errors)){
      return array(FALSE, $errors);
    }
    $password = <PASSWORD>($password);
    $new_password1 = <PASSWORD>($<PASSWORD>);
    $new_password2 = <PASSWORD>($<PASSWORD>);

    if($new_password1 != $new_password2){
      $errors[] = 'New passwords do not match';
      return array(FALSE, $errors);
    }


    if(empty($errors)){

      #check pass1 in database
      $sql = "SELECT id_users FROM users WHERE password=? AND id_users=?";
      $stmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($stmt, 'si', $password, $session_id);
      mysqli_execute($stmt);
      mysqli_stmt_store_result($stmt);

      if(mysqli_stmt_num_rows($stmt) != 1){
        mysqli_stmt_free_result($stmt);
        mysqli_stmt_close($stmt);
        $errors[] = 'Your password is incorrect';
        return array(FALSE, $errors);
      }
      if(mysqli_stmt_num_rows($stmt) === 1){

        $sql = "UPDATE users SET password=? WHERE id_users=? Limit 1";
        $stmt = mysqli_prepare($con, $sql);
        mysqli_stmt_bind_param($stmt, 'si', $new_password1, $session_id);
        mysqli_execute($stmt);

        if(mysqli_stmt_affected_rows($stmt) === 1){
          $success[] = 'Your password has been updated';
          return array(TRUE, $success);
        }else{
          $errors[] = 'An error occured please try again';
          return array(FALSE, $errors);
        }

      }

    }else{
      return array(FALSE, $errors);
    }


  }

/**
 * function for forgot_password
 */
     function forgot_password($con, $username, $email){

       #initialize arrays
       $errors = array();
       $success = array();

       if(empty($username)){
         $errors[] = 'Please enter your username';
       }
       if(empty($email)){
         $errors[] = 'Please enter you email';
       }

       if(!empty($errors)){
         return array(FALSE, $errors);
       }

       #check if user name and email is real user
       $sql = "SELECT id_users, id_users_sha1, username, email FROM users WHERE username=? AND email=?";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'ss', $username, $email);
       mysqli_execute($stmt);
       mysqli_stmt_bind_result($stmt, $id_user, $id_secure, $username_db, $email_db);
       mysqli_stmt_store_result($stmt);
       $num = mysqli_stmt_num_rows($stmt);

       if($num === 0){
         $errors[] = 'No such user';
         return array(FALSE, $errors);
       }else{
         while(mysqli_stmt_fetch($stmt)){
           $id_user;
           $id_secure;
           $username_db;
           $email_db;
           break;
         }
       }
       #destroy $num to free memory reset the memory associated with $stmt
       unset($num);
       mysqli_stmt_free_result($stmt);


       #check if there is already a request pending
       $sql = "SELECT request_date FROM forgot_password WHERE id_user=?";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'i', $id_user);
       mysqli_execute($stmt);
       mysqli_stmt_bind_result($stmt, $request_date);
       mysqli_stmt_store_result($stmt);
       $num = mysqli_stmt_num_rows($stmt);

       if($num === 1){
         while(mysqli_stmt_fetch($stmt)){
           $errors[] = 'A request was made on: '.$request_date.' please check your email';
           return array(FALSE, $errors);
         }
       }
       unset($num);
       mysqli_stmt_free_result($stmt);

       #generate a new password, add details to forgot_password table
       $new_password = <PASSWORD>(<PASSWORD>(<PASSWORD>()));

       $sql = "INSERT INTO forgot_password(id_user, id_secure, new_password, request_date) VALUES(?,?,?, UTC_TIMESTAMP())";
       $stmt = mysqli_prepare($con, $sql);
       mysqli_stmt_bind_param($stmt, 'iss', $id_user, $id_secure, $new_password);
       mysqli_stmt_execute($stmt);

       if(mysqli_stmt_affected_rows($stmt) === 1){

         if(newPasword_email($con, $id_secure, $email_db, $new_password) === TRUE){
           $success[] = 'Check your email your password was changed';
           return array(TRUE, $success);
         }else{
           $success[] = 'Check your email your password was changed';
           return array(TRUE, $success);
         }


       }else{
         $errors[] = 'An error occured please try again';
         return array(FALSE. $errors);
       }

     }




/**
 * function to send link to change password
 */
 function newPasword_email($con, $id_secure, $email_db, $new_password){

  $link = 'reset_password.php?reset1='. urlencode($id_secure) .'&reset2='. urlencode($new_password) .' ';

   $to = $email_db;
   $subject = "Reset your password";
   $body = '

   <a href="' . $link . '>Reset your password</a>"

   ';
   $headers = 'no-reply';

   if(@mail($to, $subject, $body, $headers)){
     return TRUE;
   }else{
     return FALSE;
   }

 }


/**
 * function activate the changed password
 */
 function reset_password($con, $id_secure, $new_password_hashed){

   if(!ctype_alnum($id_secure) || !ctype_alnum($new_password_hashed) || strlen($id_secure) != 40 || strlen($new_password_hashed) != 40){

     header("Location: login.php");


   }else{
   #start transaction
    mysqli_autocommit($con, FALSE);

    $sql = "SELECT id_user, new_password FROM forgot_password WHERE id_secure=? AND new_password=?";
    $stmt = mysqli_prepare($con, $sql);
    mysqli_stmt_bind_param($stmt, 'ss', $id_secure, $new_password_hashed);
    mysqli_stmt_execute($stmt);

    $result = mysqli_stmt_get_result($stmt);

     $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
     $id_user = $row['id_user'];
     $new_password = $row['<PASSWORD>'];

     $sql = "UPDATE users SET password=? WHERE id_users=? LIMIT 1";
     $stmt = mysqli_prepare($con, $sql);
     mysqli_stmt_bind_param($stmt, 'si', $new_password, $id_user);
     mysqli_execute($stmt);

     $sql= "DELETE FROM forgot_password WHERE id_user=? AND new_password=? LIMIT 1";
     $stmt = mysqli_prepare($con, $sql);
     mysqli_stmt_bind_param($stmt, 'is', $id_user, $new_password_hashed);
     mysqli_execute($stmt);

     if(mysqli_commit($con)){
       #if commited logged the user in
       if(!isset($_SESSION['user_id']) || !isset($_SESSION['secure']) || !isset($_SESSION['timezone']))  {
         $sql =
         "SELECT users.id_users, users.id_users_sha1 AS id_sha1, time_zones.name_time_zones AS tz
         FROM users
         JOIN time_zones ON time_zones.id_time_zones = users.time_zone
         WHERE id_users=?";
         $stmt = mysqli_prepare($con, $sql);
         mysqli_stmt_bind_param($stmt, 'i', $id_user);
         mysqli_execute($stmt);
         $result = mysqli_stmt_get_result($stmt);
         $user_id = mysqli_fetch_array($result, MYSQLI_ASSOC);
         #set the user id
         $_SESSION['user_id'] = $user_id['id_users'];
         $_SESSION['secure'] = $user_id['id_sha1'];
         $_SESSION['timezone'] = $user_id['tz'];

         #redirect the mto change password page so that they must change there password
         header('Location: change_password.php');


       }
     }else{
       header('Location: login.php');
     }

   }

 }


/**
 *  collect user data
 */
 function user_logs($con, $session_id){

   $agent = $_SERVER['HTTP_USER_AGENT'];

     #get ip
     if(!empty($_SERVER['HTTP_CLIENT_IP'])){
       $ip = addslahes($_SERVER['HTTP_CLIENT_IP']);
       $method = 'client_ip';
     }
     elseif(!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
       $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
       $method = 'x_forward';
     }
     elseif(!empty($_SERVER['REMOTE_ADDR'])){
       $ip = $_SERVER['REMOTE_ADDR'];
       $method = 'remote';

     }else{
       $ip = 0;
       $method = 0;
     }

     #host name
     $hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
     #session cookie id
     $phpsess = session_id();
     #pagename
     $pagename = htmlspecialchars(basename($_SERVER['PHP_SELF']), ENT_QUOTES, 'utf-8');

     $sql = "INSERT INTO user_logs(user_id, ip, ip_method, hostaddr, phpsess, agent, page_name, datetime) VALUES(?,?,?,?,?,?,?, UTC_TIMESTAMP())";
     $stmt = mysqli_prepare($con, $sql);
     mysqli_stmt_bind_param($stmt, 'issssss', $session_id, $ip, $method, $hostname, $phpsess, $agent, $pagename);
     mysqli_execute($stmt);

     mysqli_stmt_free_result($stmt);

 }
<file_sep><?php
/**
 *  function for the forum overview page
 * get all necaseery information from database and builds the page
 */
function forum_overview($con, $timezone){
  $sql =
  "SELECT forum_overview.id_forum_sha1, forum_overview.name_forum, forum_overview.details_forum, COUNT(threads.id_forum), IFNULL(DATE_FORMAT(CONVERT_TZ(MAX(threads.created_on), 'UTC', ?), '%d %b '), '0')
   FROM forum_overview
   LEFT JOIN threads ON forum_overview.id_forum = threads.id_forum
   GROUP BY forum_overview.id_forum";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 's', $timezone);
  mysqli_execute($stmt);
  mysqli_stmt_bind_result($stmt, $id_forum, $name_forum, $details_forum, $number_threads, $last_thread);
  mysqli_stmt_store_result($stmt);


  $sql_posts =
  "SELECT forum_overview.id_forum, COUNT(posts.id_forum) as sum
  FROM forum_overview
  LEFT JOIN posts ON forum_overview.id_forum = posts.id_forum
  GROUP BY forum_overview.id_forum";
  $stmt_posts = mysqli_prepare($con, $sql_posts);
  mysqli_execute($stmt_posts);
  $result = mysqli_stmt_get_result($stmt_posts);


  $i = 1;
        while (mysqli_stmt_fetch($stmt)){
          echo '

          <div class="thread">
            <div class="counter">
              <div class="counter-inner">
                <div class="counter-inner-inner">
                  '.$i.'</1>
                </div>
              </div>
            </div>
            <div class="thread-combo"><a href="threads.php?id1='.$id_forum.'">
                <h3>' .$name_forum. '</h3></a>
              <div class="description">
                '. $details_forum .'
              </div>
            </div>
            <div class="thread-stats">
              <ul>
                <div class="stats-heading">Forum Stats
                  <li>
                    <div class="details">Threads:</div>
                    <div class="value">'.$number_threads.'</div>
                  </li>
                  <li>
          ';
              while($number = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                echo '
                <div class="details">Posts:</div>
                <div class="value">'.$number['sum'].'</div>
                ';
                break;
              }

          echo'
          </li>
                  <li>
                    <div class="details">Latest:</div>
                    <div class="value">'.$last_thread.'</div>
                  </li>
                </div>
              </ul>
            </div>
          </div>

          ';

          $i++;
        }
        mysqli_stmt_free_result($stmt);
        mysqli_stmt_free_result($stmt_posts);
}


#pagination
function pagination($p_flag, $total_pages, $forum_id, $thread_id='', $x,$y){

  #create this page variable to add appropriate styles
  $this_page = ($x / $y) + 1;

  if($p_flag === 1){

    #pagination links
    if ($total_pages > 1){


      if($this_page !=1){

        echo '<a href="threads.php?id1='.$forum_id.'&x='.($x - $y).'&pages='.$total_pages.'">Previous</a>';

      }
      for($i =1; $i <=$total_pages; $i++){
        if($i != $this_page){
          echo '<a href="threads.php?id1='.$forum_id.'&x='.(($y *($i-1))).'&pages='.$total_pages.'">'.$i.'</a>';
        }else{
          echo '<a class="active">'.$i .'</a>';
        }
      }
      if($this_page != $total_pages){
        echo '<a href="threads.php?id1='.$forum_id.'&x='.($x + $y).'&pages='.$total_pages.'">Next</a>';
      }

    }

  }
  if($p_flag === 2){

    #pagination links
    if ($total_pages > 1){


      if($this_page !=1){

        echo '<a href="view_thread.php?id1='.$forum_id.'&id2='.$thread_id.'&x='.($x - $y).'&pages='.$total_pages.'">Previous</a>';

      }
      for($i =1; $i <=$total_pages; $i++){
        if($i != $this_page){
          echo '<a href="view_thread.php?id1='.$forum_id.'&id2='.$thread_id.'&x='.(($y *($i-1))).'&pages='.$total_pages.'">'.$i.'</a>';
        }else{
          echo '<a class="active">'.$i .'</a>';
        }
      }
      if($this_page != $total_pages){
        echo '<a href="view_thread.php?id1='.$forum_id.'&id2='.$thread_id.'&x='.($x + $y).'&pages='.$total_pages.'">Next</a>';
      }


    }

  }
}


/**
 * modal function to transverse back and fourth through forums and  and threads
 */
function navigation_modal($con, $forum_id){

  $pageName = htmlspecialchars(basename($_SERVER['PHP_SELF']), ENT_QUOTES, 'utf-8');

  if(pagename($pageName) === 'Viewing Thread'){

    $modal_sql = "SELECT name_forum FROM forum_overview WHERE id_forum_sha1=?";
    $modal_stmt = mysqli_prepare($con, $modal_sql);
    mysqli_stmt_bind_param($modal_stmt, 's', $forum_id);
    mysqli_execute($modal_stmt);
    $result = mysqli_stmt_get_result($modal_stmt);
    $row = mysqli_fetch_array($result, MYSQLI_NUM);

    echo '<h3>Viewing a thread in: <a href="threads.php?id1='.$forum_id.'">'.$row[0].'</a> forum</h3>';

    mysqli_stmt_free_result($modal_stmt);
    mysqli_stmt_close($modal_stmt);

  }

  if(pagename($pageName) === 'Threads'){

    $modal_sql = "SELECT name_forum FROM forum_overview WHERE id_forum_sha1=?";
    $modal_stmt = mysqli_prepare($con, $modal_sql);
    mysqli_stmt_bind_param($modal_stmt, 's', $forum_id);
    mysqli_execute($modal_stmt);
    $result = mysqli_stmt_get_result($modal_stmt);
    $row = mysqli_fetch_array($result, MYSQLI_NUM);

    echo '<h3>Viewing all threads in: <a href="forum_overview.php">'.$row[0].'</a></h3>';

    mysqli_stmt_free_result($modal_stmt);
    mysqli_stmt_close($modal_stmt);

  }

}




#build submit butron url
function action_url($url, $id1, $id2){

  $page = strip_tags(htmlspecialchars(basename($url), ENT_QUOTES, 'utf-8'));

  #check if its the right page
  if(pageName($page) != 'Viewing Thread'){
    return $action = '#';
  }


  #id values must consist of only alphanumeric characters
  return $action = (ctype_alnum($id1) AND ctype_alnum($id2)) ? ''.$page.'?id1='.urlencode($id1).'&id2='.urlencode($id2).'' : '#' ;

}


/**
 * function for posting a message to a thread
 */
 $indicator = '';
function reply($con, $user_id, $message, $id1, $id2){

  #initialize errros array
  $errors = array();
  #initialize success array
  $success = array();

  if(empty($message)){
    $errors[] = 'Please enter your message';
  }


  if(empty($errors)){
    $get_sql=
    "SELECT forum_overview.id_forum, threads.id_thread
     FROM forum_overview
     JOIN threads ON forum_overview.id_forum = threads.id_forum
     WHERE forum_overview.id_forum_sha1=? AND threads.id_thread_sha1=?";
    $get_stmt = mysqli_prepare($con ,$get_sql);
    mysqli_stmt_bind_param($get_stmt, 'ss', $id1, $id2);
    mysqli_execute($get_stmt);
    mysqli_stmt_bind_result($get_stmt, $id_forum, $id_thread);
    mysqli_stmt_store_result($get_stmt);

    while(mysqli_stmt_fetch($get_stmt)){
      $id_forum;
      $id_thread;
      break;
    }

    $num = mysqli_stmt_num_rows($get_stmt);

    #if forum id and thread id are valid
    if($num === 1){

      $in_sql = "INSERT INTO posts(id_thread, id_forum, message_post, id_user, date_posted) VALUES (?,?,?,?, UTC_TIMESTAMP())";
      $in_stmt = mysqli_prepare($con, $in_sql);
      mysqli_stmt_bind_param($in_stmt, 'ssss', $id_thread, $id_forum, $message, $user_id);
      mysqli_execute($in_stmt);

      if(mysqli_stmt_affected_rows($in_stmt) === 1){
        $success[] = "Your message was posted!";
        return array(TRUE, $success);
      }else{
        $errors[] = "There was an error trying to post your message";
        return array(FALSE, $errors);
      }
    }
  }else{
    return array(FALSE, $errors);
  }

}




/**
 * function to remember chose thread for create thread
 */
function get_forum_id($con, $forum_id){

  #get the current forum to post in from url
  $sql =
  "SELECT id_forum FROM forum_overview WHERE id_forum_sha1=?";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 's', $forum_id);
  mysqli_execute($stmt);
  mysqli_stmt_bind_result($stmt, $current_forum);
  mysqli_stmt_store_result($stmt);
  while(mysqli_stmt_fetch($stmt)){
    $current_forum;
    break;
  }
  mysqli_stmt_free_result($stmt);

  #get list of all current forums
  $sql_forums = "SELECT id_forum, name_forum FROM forum_overview";
  $stmt_forums = mysqli_prepare($con, $sql_forums);
  mysqli_execute($stmt_forums);
  mysqli_stmt_bind_result($stmt_forums, $id_forum, $name_forum);
  mysqli_stmt_store_result($stmt_forums);

  #start drop down selection
  echo '<select name="list_of_threads">';

  while(mysqli_stmt_fetch($stmt_forums)){

    echo '<option value="'.$id_forum.'"';

    if($current_forum === $id_forum){
      echo ' SELECTED >';
    }else{
      echo '>';
    }

    echo ''.$name_forum.'</option>';

  }

  echo '</select>';
  mysqli_stmt_free_result($stmt_forums);

}


/**
 *  function to create a thread
 */
 function create_thread($con, $forum_to_create, $subject = '', $body = '', $session_id, $forum_id){

   #initialize errors array
   $errors = array();

   #validation checking
   if(strlen($subject) > 100){
     $errors[] = 'Forum subject can not be longer that 100 charaters';
   }
   if(empty($subject)){
     $errors[] = 'You forgot the subject of your post';
   }
   if(empty($body)){
     $errors[] = 'You forgot the body of your post';
   }

   if(empty($errors)){
     #create random sha1 key
     $id_thread_sha1 = sha1(uniqid(rand()));

     #lookup forum sha1
     $sql_lookup =
     "SELECT id_forum_sha1 FROM forum_overview WHERE id_forum=?";
     $stmt_lookup = mysqli_prepare($con, $sql_lookup);
     mysqli_stmt_bind_param($stmt_lookup, 'i', $forum_to_create);
     mysqli_stmt_execute($stmt_lookup);
     mysqli_stmt_bind_result($stmt_lookup, $id_out);
     mysqli_stmt_store_result($stmt_lookup);
     while(mysqli_stmt_fetch($stmt_lookup)){
       $id_out;
       break;
     }
     #create the thread
     $sql_create =
     "INSERT INTO threads(id_thread_sha1, id_forum, title_thread, body_thread, id_user, created_on) VALUES(?,?,?,?,?,UTC_TIMESTAMP()) ";
     $stmt_create = mysqli_prepare($con, $sql_create);
     mysqli_stmt_bind_param($stmt_create, 'sissi', $id_thread_sha1, $forum_to_create, $subject, $body, $session_id);
     mysqli_execute($stmt_create);

     if(mysqli_stmt_affected_rows($stmt_create) === 1){


      #if there where no problems and thread was created redirect user to their created thread
      header('Location: view_thread.php?id1='.$id_out.'&id2='.$id_thread_sha1.'');

       mysqli_stmt_free_result($stmt_lookup);
       mysqli_stmt_free_result($stmt_create);
       return array(TRUE, $errors);
     }else{
       $errors[] = 'An error occured please try again';
       return array(FALSE, $errors);
     }


   }else{
     return array(FALSE, $errors);
   }


 }



 #build submit butron url
 function create_thread_urlaction($url, $id1){

   $page = strip_tags(htmlspecialchars(basename($url), ENT_QUOTES, 'utf-8'));

   #check if its the right page
   if(pageName($page) != 'Create Thread'){
     return $action = '#';
   }


   #id values must consist of only alphanumeric characters
   return $action = (ctype_alnum($id1)) ? ''.$page.'?id1='.urlencode($id1).'' : '#' ;

 }


/**
 * get ops thread
 */
 function thread_post($con, $session_tz, $thread_id, $x='', $y=''){

 #thread post by OP
     $sql_poster =
     "SELECT users.username, users.id_users_sha1, DATE_FORMAT(CONVERT_TZ(users.activation_date, 'UTC', ?), '%d %b %y'), users.signature, threads.title_thread, threads.body_thread, DATE_FORMAT(CONVERT_TZ(threads.created_on, 'UTC', ?), '%d-%b-%Y %h:%i %p'), user_groups.name_user_group
      FROM users
      JOIN threads ON users.id_users = threads.id_user
      JOIN user_groups ON user_groups.id_user_group = users.user_group
      WHERE threads.id_thread_sha1=?";
     $stmt_poster = mysqli_prepare($con, $sql_poster);
     mysqli_stmt_bind_param($stmt_poster, 'sss', $session_tz, $session_tz, $thread_id);
     mysqli_execute($stmt_poster);
     mysqli_stmt_bind_result($stmt_poster,$p_username, $p_id_sha1, $p_account_date, $p_signature, $p_title, $p_body, $p_created_on, $p_user_group);
     mysqli_stmt_store_result($stmt_poster);

     while(mysqli_stmt_fetch($stmt_poster)){


       echo '

       <div class="op-main-container">
         <div class="time-container">
           <div class="post-number orange-text">#0 - OP</div>
           <div class="post-time orange-text">'.e($p_created_on).'</div>
         </div>
         <div class="post">
           <div class="left">
             <div class="img"><img class="img1" src="_dependancies/image_proxy.php?id='.e($p_id_sha1).'"></div>
             <ul>
               <li class="orange-text">Username: '.e($p_username).'</li>
               <li class="orange-text">Join Date: '.e($p_account_date).'</li>
               <li class="orange-text">Rank: '.e($p_user_group).'</li>
               <li>
                 <div class="send-message"><a class="orange-text">Message
                     <svg id="Layer_1" version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" x="0px" y="0px" viewbox="0 0 50 50" enable-background="new 0 0 50 50" xml:space="preserve">
                       <path d="M41.798,33.763v-18c0-0.513-0.39-0.919-0.887-0.977c-0.009-0.002-0.017-0.004-0.025-0.005                      c-0.03-0.003-0.057-0.018-0.088-0.018h-30c-0.032,0-0.06,0.015-0.091,0.018c-0.065,0.006-0.126,0.019-0.189,0.038                      c-0.065,0.019-0.125,0.041-0.183,0.072c-0.053,0.028-0.1,0.062-0.147,0.099c-0.056,0.044-0.105,0.09-0.151,0.145                      c-0.019,0.023-0.045,0.035-0.062,0.06c-0.019,0.027-0.022,0.06-0.038,0.088c-0.034,0.06-0.059,0.12-0.08,0.187                      c-0.02,0.065-0.033,0.129-0.039,0.196c-0.003,0.033-0.019,0.062-0.019,0.096v18c0,0.553,0.447,1,1,1h30                      C41.351,34.763,41.798,34.315,41.798,33.763z M37.502,16.763l-11.745,8.092l-11.745-8.092H37.502z M11.798,32.763V17.666                      l13.392,9.227c0.171,0.118,0.369,0.177,0.567,0.177s0.396-0.059,0.567-0.177l13.474-9.283v15.153H11.798z"></path>
                     </svg></a></div>
               </li>
             </ul>
           </div>
           <div class="right">
             <div class="post-heading">
               <h2 class="post-h2">'.$p_title.'</h2>
             </div>
             <div class="post-body">
               <p>'.$p_body.'</p>
             </div>
             <div class="signature">
               <div class="signature-heading">Signature:</div>
               <div class="signature-body">'.e($p_signature).'</div>
             </div>
           </div>
         </div>
       </div>
       ';
     }
     mysqli_stmt_free_result($stmt_poster);
}
/**
 * function to get the threads replies
 */
function thread_replies($con, $session_tz, $thread_id, $x='', $y=''){

  #thread replies
  $sql_reply =
  "SELECT users.username, users.id_users_sha1, users.signature, DATE_FORMAT(CONVERT_TZ(users.activation_date, 'UTC', ?), '%d %b %y'),threads.id_thread, posts.id_posts, posts.message_post, DATE_FORMAT(CONVERT_TZ(posts.date_posted, 'UTC', ?), '%d-%b-%Y %h:%i %p'),   user_groups.name_user_group
   FROM users
   JOIN posts ON users.id_users = posts.id_user
   JOIN threads ON threads.id_thread = posts.id_thread
   JOIN user_groups ON users.user_group = user_groups.id_user_group
   WHERE threads.id_thread_sha1=? LIMIT ?,?
  ";
  $stmt_reply = mysqli_prepare($con, $sql_reply);
  mysqli_stmt_bind_param($stmt_reply, 'sssss', $session_tz, $session_tz, $thread_id, $x, $y );
  mysqli_execute($stmt_reply);
  mysqli_stmt_bind_result($stmt_reply, $username, $id_sha1, $signature, $account_date, $id_thread, $id_posts, $message, $date_posted, $user_group);
  mysqli_stmt_store_result($stmt_reply);

  while(mysqli_stmt_fetch($stmt_reply)){

    echo '

    <div class="reply-main-container">
      <div class="reply-time-container">
        <div class="reply-post-number orange-text">#'.e($id_posts).'</div>
        <div class="reply-post-time">'.e($date_posted).'</div>
      </div>
      <div class="reply-post">
        <div class="reply-left">
          <div class="reply-img"><img class="reply-img1" src="_dependancies/image_proxy.php?id='.e($id_sha1).'"></div>
          <ul>
            <li>Username: '.e($username).'</li>
            <li>Join date: '.e($account_date).'</li>
            <li>Rank: '.e($user_group).' </li>
            <li>
              <div class="send-message"><a>Message
                  <svg id="Layer_1" version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" x="0px" y="0px" viewbox="0 0 50 50" enable-background="new 0 0 50 50" xml:space="preserve">
                    <path d="M41.798,33.763v-18c0-0.513-0.39-0.919-0.887-0.977c-0.009-0.002-0.017-0.004-0.025-0.005                        c-0.03-0.003-0.057-0.018-0.088-0.018h-30c-0.032,0-0.06,0.015-0.091,0.018c-0.065,0.006-0.126,0.019-0.189,0.038                        c-0.065,0.019-0.125,0.041-0.183,0.072c-0.053,0.028-0.1,0.062-0.147,0.099c-0.056,0.044-0.105,0.09-0.151,0.145                        c-0.019,0.023-0.045,0.035-0.062,0.06c-0.019,0.027-0.022,0.06-0.038,0.088c-0.034,0.06-0.059,0.12-0.08,0.187                        c-0.02,0.065-0.033,0.129-0.039,0.196c-0.003,0.033-0.019,0.062-0.019,0.096v18c0,0.553,0.447,1,1,1h30                        C41.351,34.763,41.798,34.315,41.798,33.763z M37.502,16.763l-11.745,8.092l-11.745-8.092H37.502z M11.798,32.763V17.666                        l13.392,9.227c0.171,0.118,0.369,0.177,0.567,0.177s0.396-0.059,0.567-0.177l13.474-9.283v15.153H11.798z"></path>
                  </svg></a></div>
            </li>
          </ul>
        </div>
        <div class="reply-right">
          <div class="reply-post-body">
            <p>'.e($message).'</p>
          </div>
          <div class="signature2">
            <div class="signature-heading2">Signature:</div>
            <div class="signature-body2">'.e($signature).'</div>
          </div>
        </div>
      </div>
    </div>



    ';
  }
  mysqli_stmt_free_result($stmt_reply);

}

/**
 * function to show all the threads
 */
function show_threads($con, $session_tz, $forum_id, $x, $y){

  #get threads
  $sql =
  "SELECT threads.id_thread, threads.id_thread_sha1, threads.title_thread, threads.body_thread, threads.created_on AS created, count(posts.id_thread) AS totalposts, ifnull(DATE_FORMAT(CONVERT_TZ(max(posts.date_posted), 'UTC', ?), '%d-%b %h:%i %p'), 'No reply yet') AS lastposted, forum_overview.id_forum_sha1, users.username
  FROM threads
   LEFT JOIN posts ON threads.id_thread = posts.id_thread
   JOIN forum_overview ON forum_overview.id_forum = threads.id_forum
   JOIN users ON users.id_users = threads.id_user
   WHERE forum_overview.id_forum_sha1=?
   GROUP BY threads.id_thread
   ORDER BY totalposts DESC, created DESC LIMIT ?,?
  ";
  $stmt = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt, 'ssss', $session_tz, $forum_id, $x, $y );
  mysqli_execute($stmt);
  mysqli_stmt_bind_result($stmt, $id_thread, $id_thread_sha1, $title_thread, $body_thread, $created, $totalposts, $last_posted, $id_forum, $username);
  mysqli_stmt_store_result($stmt);

  while(mysqli_stmt_fetch($stmt)){
    $body_thread = strlen($body_thread) > 40 ? substr($body_thread, 0, 300) .'...' : $body_thread;
    $totalposts = $totalposts === 0 ? 'No reply yet' : $totalposts;
    echo '

    <div class="thread">
      <div class="thread-combo"><a href="view_thread.php?id1='.urlencode($id_forum).'&id2='.urlencode($id_thread_sha1).'">
          <h3>'.e($title_thread).'</h3></a>
        <div class="description">'.e($body_thread).'</div>
      </div>
      <div class="thread-stats-additional">
        <ul>
          <div class="stats-heading">Thread Stats
            <li>
              <div class="details">Created by:</div>
              <div class="value">'.e($username).'</div>
            </li>
            <li>
              <div class="details">Replies:</div>
              <div class="value">'.e($totalposts).'</div>
            </li>
            <li>
              <div class="details">last-reply:</div>
              <div class="value">'.e($last_posted).'</div>
            </li>
          </div>
        </ul>
      </div>
    </div>


    ';

  }
  mysqli_stmt_free_result($stmt);
  mysqli_stmt_close($stmt);

}
/**
 * function inserts thread view in database
 */
 function thread_view_log($con, $id1, $id2, $session_id){

   $sql = "SELECT idt, idf FROM view_all_thread_info WHERE idtsecure=? AND idfsecure=?";
   $stmt = mysqli_prepare($con, $sql);
   mysqli_stmt_bind_param($stmt, 'ss', $id2, $id1);
   mysqli_execute($stmt);
   $result = mysqli_stmt_get_result($stmt);
   $row = mysqli_fetch_array($result, MYSQLI_NUM);
   $id_thread = $row[0];
   $id_forum = $row[1];
   mysqli_stmt_free_result($stmt);

   $sql = "INSERT INTO thread_views(id_forum, id_thread, id_user, timestamp) VALUES(?,?,?, UTC_TIMESTAMP())";
   $stmt = mysqli_prepare($con, $sql);
   mysqli_stmt_bind_param($stmt, 'iii', $id_forum, $id_thread, $session_id);
   mysqli_execute($stmt);
   mysqli_stmt_free_result($stmt);

 }
<file_sep><?php
session_start();

#includes
  #sql connection
require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #forum-thread
  require_once('app/forum-thread-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  //session variables
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);

  #pagination requirements NB change the query to what is needed
  $y = 10;

  //see if pages has been set
  if(isset($_GET['pages']) && is_numeric($_GET['pages'])){
    $total_pages = $_GET['pages'];
  }else{
    $csql =
    "SELECT COUNT(id_user) FROM posts WHERE id_user = ?";
    $cstmt = mysqli_prepare($con, $csql);
    mysqli_stmt_bind_param($cstmt, 'i', $session_id);
    mysqli_execute($cstmt);
    $result = mysqli_stmt_get_result($cstmt);
    $row = mysqli_fetch_array($result, MYSQLI_NUM);
    $total_records = $row[0];

    if($total_records > $y){
      $total_pages = ceil($total_records / $y);
    }else{
      $total_pages = 1;
    }
    mysqli_stmt_free_result($cstmt);
  }
  // databse start point
  if(isset($_GET['x']) && is_numeric($_GET['x'])){
    $x = $_GET['x'];
  }else{
    $x = 0;
  }


?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

  <section class="main-container">
    <?php
      #show all the users specific posts
      your_posts_threads(1, $con, $x, $y, $session_id, $session_tz);
    ?>
  </section>

  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #security functions
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);




if($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['change_password'])){

 list($indicator, $data) = change_password($con, $_POST['password'], $_POST['new_password'], $_POST['<PASSWORD>'], $session_id);

}



?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <?php

      if(isset($indicator) && $indicator === TRUE){

        foreach($data as $msg)

        echo '

        <section class="message-container">
          <div class="message-area green">'.$msg.'</div>
        </section>

        ';
        die();

      }
      if(isset($indicator) && $indicator === FALSE){

        foreach($data as $msg){

          echo '

          <section class="message-container red-border">
            <div class="message-area red">'.$msg.'</div>
          </section>

          ';

        }
      }

    ?>

    <div class="section main-container no-border">
      <div class="profile-container cpw-align">
        <form action="change_password.php" method="POST">
          <div class="profile-details-contain">
            <div class="profile-header">
              <h1>Change your password</h1>
            </div>
            <h3>Current Password:</h3>
            <input type="<PASSWORD>" name="password" placeholder="<PASSWORD>" class="text-input" value="<?php if(isset($_POST['password'])) echo e($_POST['password']);?>">
            <h3>New Password:</h3>
            <input type="<PASSWORD>" name="new_password" placeholder="<PASSWORD>" class="text-input" value="<?php if(isset($_POST['new_password'])) echo e($_POST['new_password']);?>">
            <h3>Confirm new Password:</h3>
            <input type="<PASSWORD>" name="new_password2" placeholder="<PASSWORD>" class="text-input" value="<?php if(isset($_POST['new_password2'])) echo e($_POST['new_password2']);?>">
            <input type="submit" value="Change Password" class="profile-update" name="change_password">
          </div>
        </form>
      </div>
    </div>





  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);


  if($_SERVER['REQUEST_METHOD'] === 'POST' && $_POST['update']){

   #get server name of picture
   $profile_pic = $_FILES['profile']['tmp_name'];
   $timezone = $_POST['timezone'];
   $signature = $_POST['signature'];


   list($indicator, $data) = update_profile($con, $profile_pic, $timezone, $signature, $session_id, $session_secure, $session_tz);


  }


?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <?php

    if(isset($indicator) && $indicator === TRUE){

      foreach($data as $msg)

      echo '

      <section class="message-container">
        <div class="message-area green">'.$msg.'</div>
      </section>

      ';

    }
    if(isset($indicator) && $indicator === FALSE){

      foreach($data as $msg){

        echo '

        <section class="message-container red-border">
          <div class="message-area red">'.$msg.'</div>
        </section>

        ';

      }
    }

    ?>

    <div class="section main-container no-border">
      <div class="profile-container">
        <form action="profile.php" method="POST" enctype="multipart/form-data">
          <div class="profile-image-contain">

            <?php

            get_profile($con, $session_id, $session_tz);

            ?>
            <h3>Change profile picture</h3>
            <input type="file" class="file" name="profile" id="profile">
            <input type="submit" value="Update Profile" class="profile-update" name="update">
          </div>
        </form>
      </div>
    </div>



  </body>
</html>
<file_sep><?php
session_start();
#includes
  #sql connection
  require_once('_dependancies/sql.php');
  #navigation
  require_once('app/navigation-functions.php');
  #forum-thread
  require_once('app/forum-thread-functions.php');
  #security
  require_once('app/security-functions.php');
  #additional functionality
  require_once('app/additional-functionality-functions.php');
  #check the users login
  check_login(1);
  #validate url
  validate_url(1, $con, $_GET['id1']);
  //session variables
  $session_id = $_SESSION['user_id'];
  $session_secure = $_SESSION['secure'];
  $session_tz = $_SESSION['timezone'];
  #user logs
  user_logs($con, $session_id);


  #flag for which pagniation page
  $p_flag = 1;

if(isset($_GET['id1']) && strlen($_GET['id1']) === 40){

    #set url values to variables
    $forum_id = $_GET['id1'];

    $y = 10;

    //see if pages has been set
    if(isset($_GET['pages']) && is_numeric($_GET['pages'])){
      $total_pages = $_GET['pages'];
    }else{
      $sql =
      "SELECT COUNT(threads.id_forum)
       FROM threads
       JOIN forum_overview ON forum_overview.id_forum = threads.id_forum
       WHERE forum_overview.id_forum_sha1=?";
      $cstmt = mysqli_prepare($con, $sql);
      mysqli_stmt_bind_param($cstmt, 's', $forum_id);
      mysqli_execute($cstmt);
      $result = mysqli_stmt_get_result($cstmt);
      $row = mysqli_fetch_array($result, MYSQLI_NUM);
      $total_records = $row[0];

      if($total_records > $y){
        $total_pages = ceil($total_records / $y);
      }else{
        $total_pages = 1;
      }
      mysqli_stmt_free_result($cstmt);
    }
    // databse start point
    if(isset($_GET['x']) && is_numeric($_GET['x'])){
      $x = $_GET['x'];
    }else{
      $x = 0;
    }

}

?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <title><?php pageTitle();?></title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta description="the description">
    <link rel="stylesheet" href="assets/css/app.css" type="text/css">
  </head>
  <body>
    <?php navigation();?>

    <div class="pagination-container">
      <div class="pagination">
        <div class="pagination-links">
          <div class="tree">
            <?php navigation_modal($con, $_GET['id1']);?>
          </div>
          <div class="links-contain">

            <?php
            #pagination to transverse through the different threads
            pagination($p_flag, $total_pages, $forum_id, '' , $x, $y);
            ?>

          </div>
        </div>
      </div>
    </div>

    <section class="main-container">

      <?php
      #show all the threads in the specific forum
      show_threads($con, $session_tz, $forum_id, $x, $y);
      ?>

    </section>

    <div class="pagination-container">
      <div class="pagination">
        <div class="pagination-links">
          <div class="tree">
            <?php navigation_modal($con, $_GET['id1']);?>
          </div>
          <div class="links-contain">

            <?php
            #pagination to transverse through the different threads
            pagination($p_flag, $total_pages, $forum_id, '' , $x, $y);
            ?>

          </div>
        </div>
      </div>
    </div>


  </body>
</html>
<file_sep>-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 20, 2016 at 01:41 AM
-- Server version: 10.1.16-MariaDB
-- PHP Version: 5.6.24

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `retro`
--

-- --------------------------------------------------------

--
-- Table structure for table `forgot_password`
--

CREATE TABLE `forgot_password` (
  `id_change_password` int(10) UNSIGNED NOT NULL,
  `id_user` int(10) UNSIGNED NOT NULL,
  `id_secure` char(40) NOT NULL,
  `new_password` char(40) NOT NULL,
  `request_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `forum_overview`
--

CREATE TABLE `forum_overview` (
  `id_forum` int(11) UNSIGNED NOT NULL,
  `id_forum_sha1` char(40) NOT NULL,
  `name_forum` varchar(100) NOT NULL,
  `details_forum` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `limbo`
--

CREATE TABLE `limbo` (
  `id_limbo` int(10) UNSIGNED NOT NULL,
  `username` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  `email_sha1` char(40) NOT NULL,
  `password` char(40) NOT NULL,
  `activation_code` char(40) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE `posts` (
  `id_posts` int(11) NOT NULL,
  `id_thread` int(10) UNSIGNED NOT NULL,
  `id_forum` int(11) UNSIGNED NOT NULL,
  `message_post` text NOT NULL,
  `id_user` int(10) UNSIGNED NOT NULL,
  `date_posted` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `threads`
--

CREATE TABLE `threads` (
  `id_thread` int(11) UNSIGNED NOT NULL,
  `id_thread_sha1` char(40) NOT NULL,
  `id_forum` int(11) UNSIGNED NOT NULL,
  `title_thread` varchar(100) NOT NULL,
  `body_thread` text NOT NULL,
  `id_user` int(10) UNSIGNED NOT NULL,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `thread_views`
--

CREATE TABLE `thread_views` (
  `id_thread_views` int(10) UNSIGNED NOT NULL,
  `id_forum` int(10) UNSIGNED NOT NULL,
  `id_thread` int(10) UNSIGNED NOT NULL,
  `id_user` int(10) UNSIGNED NOT NULL,
  `timestamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

-- --------------------------------------------------------

--
-- Table structure for table `time_zones`
--

CREATE TABLE `time_zones` (
  `id_time_zones` int(10) UNSIGNED NOT NULL,
  `name_time_zones` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id_users` int(10) UNSIGNED NOT NULL,
  `id_users_sha1` char(40) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` char(40) NOT NULL,
  `email` varchar(100) NOT NULL,
  `profile` blob NOT NULL,
  `time_zone` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `signature` varchar(250) NOT NULL,
  `user_group` int(11) NOT NULL DEFAULT '5',
  `activation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `user_groups`
--

CREATE TABLE `user_groups` (
  `id_user_group` int(10) UNSIGNED NOT NULL,
  `name_user_group` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `user_logs`
--

CREATE TABLE `user_logs` (
  `id_user_logs` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `ip` varchar(50) NOT NULL,
  `ip_method` enum('client_ip','x_forward','remote','') NOT NULL,
  `hostaddr` varchar(50) NOT NULL,
  `phpsess` varchar(50) NOT NULL,
  `agent` varchar(250) NOT NULL,
  `page_name` varchar(25) NOT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ucs2;

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_all_forum_info`
--
CREATE TABLE `view_all_forum_info` (
`idf` int(11) unsigned
,`idfsecure` char(40)
,`fname` varchar(100)
,`fdetails` varchar(250)
,`totalthreads` varchar(21)
,`newestthread` varchar(19)
,`totalposts` varchar(21)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_all_thread_info`
--
CREATE TABLE `view_all_thread_info` (
`idt` int(11) unsigned
,`idtsecure` char(40)
,`title` varchar(100)
,`body` text
,`createdon` datetime
,`createbyuserid` int(10) unsigned
,`idf` int(11) unsigned
,`idfsecure` char(40)
,`createdby` varchar(20)
,`totalposts` varchar(21)
,`lastpostdate` varchar(19)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_forum_stats`
--
CREATE TABLE `view_forum_stats` (
`id_forum` int(11) unsigned
,`totalthreads` bigint(21)
,`newestthread` varchar(19)
,`totalposts` bigint(21)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `view_thread_stats`
--
CREATE TABLE `view_thread_stats` (
`id_thread` int(11) unsigned
,`totalposts` bigint(21)
,`lastpostdate` varchar(19)
);

-- --------------------------------------------------------

--
-- Structure for view `view_all_forum_info`
--
DROP TABLE IF EXISTS `view_all_forum_info`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_all_forum_info`  AS  select `forum_overview`.`id_forum` AS `idf`,`forum_overview`.`id_forum_sha1` AS `idfsecure`,`forum_overview`.`name_forum` AS `fname`,`forum_overview`.`details_forum` AS `fdetails`,if((`view_forum_stats`.`totalthreads` = 0),'no-threads-yet',`view_forum_stats`.`totalthreads`) AS `totalthreads`,`view_forum_stats`.`newestthread` AS `newestthread`,if((`view_forum_stats`.`totalposts` = 0),'no-posts-yet',`view_forum_stats`.`totalposts`) AS `totalposts` from (`forum_overview` join `view_forum_stats` on((`forum_overview`.`id_forum` = `view_forum_stats`.`id_forum`))) ;

-- --------------------------------------------------------

--
-- Structure for view `view_all_thread_info`
--
DROP TABLE IF EXISTS `view_all_thread_info`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_all_thread_info`  AS  select `threads`.`id_thread` AS `idt`,`threads`.`id_thread_sha1` AS `idtsecure`,`threads`.`title_thread` AS `title`,`threads`.`body_thread` AS `body`,`threads`.`created_on` AS `createdon`,`threads`.`id_user` AS `createbyuserid`,`forum_overview`.`id_forum` AS `idf`,`forum_overview`.`id_forum_sha1` AS `idfsecure`,`users`.`username` AS `createdby`,if((`view_thread_stats`.`totalposts` = 0),'no-reply-yet',`view_thread_stats`.`totalposts`) AS `totalposts`,`view_thread_stats`.`lastpostdate` AS `lastpostdate` from ((((`threads` left join `posts` on((`threads`.`id_thread` = `posts`.`id_thread`))) join `forum_overview` on((`forum_overview`.`id_forum` = `threads`.`id_forum`))) join `users` on((`users`.`id_users` = `threads`.`id_user`))) join `view_thread_stats` on((`threads`.`id_thread` = `view_thread_stats`.`id_thread`))) group by `threads`.`id_thread` ;

-- --------------------------------------------------------

--
-- Structure for view `view_forum_stats`
--
DROP TABLE IF EXISTS `view_forum_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_forum_stats`  AS  select `forum_overview`.`id_forum` AS `id_forum`,count(distinct `threads`.`id_thread`) AS `totalthreads`,ifnull(max(`threads`.`created_on`),'no-threads-yet') AS `newestthread`,count(distinct `posts`.`id_posts`) AS `totalposts` from ((`forum_overview` left join `threads` on((`forum_overview`.`id_forum` = `threads`.`id_forum`))) left join `posts` on((`forum_overview`.`id_forum` = `posts`.`id_forum`))) group by `forum_overview`.`id_forum` ;

-- --------------------------------------------------------

--
-- Structure for view `view_thread_stats`
--
DROP TABLE IF EXISTS `view_thread_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_thread_stats`  AS  select `threads`.`id_thread` AS `id_thread`,count(`posts`.`id_thread`) AS `totalposts`,ifnull(max(`posts`.`date_posted`),'No reply yet') AS `lastpostdate` from ((`threads` left join `posts` on((`threads`.`id_thread` = `posts`.`id_thread`))) join `forum_overview` on((`forum_overview`.`id_forum` = `threads`.`id_forum`))) group by `threads`.`id_thread` ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `forgot_password`
--
ALTER TABLE `forgot_password`
  ADD PRIMARY KEY (`id_change_password`),
  ADD UNIQUE KEY `id_secure` (`id_secure`);

--
-- Indexes for table `forum_overview`
--
ALTER TABLE `forum_overview`
  ADD PRIMARY KEY (`id_forum`);

--
-- Indexes for table `limbo`
--
ALTER TABLE `limbo`
  ADD PRIMARY KEY (`id_limbo`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `posts`
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`id_posts`),
  ADD KEY `id_thread` (`id_thread`),
  ADD KEY `id_user` (`id_user`),
  ADD KEY `date_posted` (`date_posted`),
  ADD KEY `id_user_2` (`id_user`);

--
-- Indexes for table `threads`
--
ALTER TABLE `threads`
  ADD PRIMARY KEY (`id_thread`);

--
-- Indexes for table `thread_views`
--
ALTER TABLE `thread_views`
  ADD PRIMARY KEY (`id_thread_views`);

--
-- Indexes for table `time_zones`
--
ALTER TABLE `time_zones`
  ADD PRIMARY KEY (`id_time_zones`),
  ADD UNIQUE KEY `name_time_zones` (`name_time_zones`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id_users`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `id_users_sha1` (`id_users_sha1`);

--
-- Indexes for table `user_groups`
--
ALTER TABLE `user_groups`
  ADD PRIMARY KEY (`id_user_group`),
  ADD UNIQUE KEY `name_user_group` (`name_user_group`);

--
-- Indexes for table `user_logs`
--
ALTER TABLE `user_logs`
  ADD PRIMARY KEY (`id_user_logs`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `forgot_password`
--
ALTER TABLE `forgot_password`
  MODIFY `id_change_password` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `forum_overview`
--
ALTER TABLE `forum_overview`
  MODIFY `id_forum` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `limbo`
--
ALTER TABLE `limbo`
  MODIFY `id_limbo` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `posts`
--
ALTER TABLE `posts`
  MODIFY `id_posts` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `threads`
--
ALTER TABLE `threads`
  MODIFY `id_thread` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `thread_views`
--
ALTER TABLE `thread_views`
  MODIFY `id_thread_views` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `time_zones`
--
ALTER TABLE `time_zones`
  MODIFY `id_time_zones` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id_users` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user_groups`
--
ALTER TABLE `user_groups`
  MODIFY `id_user_group` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user_logs`
--
ALTER TABLE `user_logs`
  MODIFY `id_user_logs` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
