Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: Quick Tutorial

  1. #1
    New to the CF scene
    Join Date
    Jan 2011
    Location
    Northampton, UK
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quick Tutorial

    I thought I would post a little tutorial for everyone . The scenario is a quick login system (literally a quickie).

    First of all let's get the MySQL stuff underway, considering this is a MYSQL board.

    Code:
    --
    -- CREATE OUR USERS TABLE
    --
    
    CREATE TABLE IF NOT EXISTS `pfx_users` (
      
      `u_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `u_level` ENUM('admin', 'mod', 'user') NOT NULL DEFAULT 'user',
      `u_first_name` VARCHAR(45) NOT NULL,
      `u_surname` VARCHAR(45) DEFAULT NULL, -- surname isn't required.
      `u_email` VARCHAR(155) NOT NULL,
      `u_password` TINYBLOB NOT NULL, -- AES (Advanced Encryption Standard), used in SSL Certificates (128-bytes).
      `u_banned` ENUM('0','1') NOT NULL DEFAULT '0',
      `u_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- automatically create date/time on entry! (NEVER UPDATE)
      `u_last_login` DATETIME DEFAULT NULL,
      
      PRIMARY KEY (`u_id`),
      UNIQUE `USRMAIL` (`u_email`)
      
    ) ENGINE=InnoDB AUTO_INCREMENT=1;
    
    --
    -- CREATE OUR BANNED RECORDS TABLE (NO PRIMARY KEY HERE)
    --
    
    CREATE TABLE IF NOT EXISTS `pfx_users_banned` (
      
      `u_id` INT(11) UNSIGNED NOT NULL, -- the user account id who is banned
      `ub_expired` ENUM('0','1') NOT NULL DEFAULT '0',
      `ub_expires` DATETIME DEFAULT NULL,
      `ub_reason` VARCHAR(255) NOT NULL,
      `ub_issued` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `ub_issuer` INT(11) UNSIGNED NOT NULL, -- Aministrator/Moderator ID
      
      INDEX `USRBAN` (`u_id`),
      INDEX `ADMBAN` (`ub_issuer`)
      
    ) ENGINE=InnoDB;
    Okay, so before we move on let me clear up some things relating to the tables above.

    First fo all, the reason I used InnoDB over MyISAM, is purely on the principles that with LARGE amounts of data, InnoDB will only LOCK individual rows, where as MyISAM locks entire tables.

    Now starting with our pfx_users table.

    1. UNSIGNED simply means 0 or greater, NO NEGATIVES.
    2. TINYBLOB allows us to entire binary data (for AES).
    3. TIMESTAMP is used because we can have MySQL append dates automatically on insert for us.
    4. UNIQUE `USRMAIL` will ensure no duplicate e-mail addresses are registered.

    Now let's move on to our pfx_users_banned table.

    1. ub_expired will be Zero (0) for active ban, One (1) for inactive ban.
    2. ub_expires if ub_expired is ZERO and ub_expires is NULL, this is a permanent ban (Active ban, with NULL expire date). Otherwise a DATETIME of expiry goes here.
    3. INDEX `USRBAN` indexes our user's ID, there is also another index on the admin/mod ID who issued the ban.

    Right, well that's the MySQL section completed, however more queries will come shortly. Let's start with some minor PHP to show the tables in action.

    login.php
    PHP Code:
    <?php
    session_start
    ();

    if (isset(
    $_SESSION['isLoggedIn']))
      
    header('location: loggedin.php'); // redirect if loggedin...

    if (isset($_POST['btnAccountLogin'])) {
      
    // Create our db instance once form is submitted (consume server work).
      
    MySQL::Instance();
      
      
    // let's check if our account exists.
      
    $user mysql_real_escape_string($_POST['userMail']);
      
    $pass mysql_real_escape_string($_POST['userPasswd']);
      
      
    $qLog mysql_query("SELECT u_id, u_banned FROM pfx_users WHERE u_email = '{$user}' AND AES_DECRYPT(u_password, 's$m3_EncR7p710n_K3y') = '{$pass}'");
      list(
    $accountID$accountBanned) = @mysql_fetch_array($qLog);
      
      if (!
    $accountID) {
        die(
    'Invalid username/password combination.');
      } else {
        
    // check if the user is banned...
        
    if ($accountBanned) {
          
    // collect our banned results, let's join our user table TWICE (for two separate records)
          // i will explain this query after this explain further.
          
    $qBan mysql_query("
            SELECT 
              ban.ub_reason banReason,
              IF(ban.ub_expires IS NULL, 'never', ban.ub_expires) banExpires,
              CONCAT_WS(' ', usr.first_name, usr.surname) userBannedName,
              CONCAT_WS(' ', adm.first_name, adm.surname) adminName,
              adm.u_level adminLevelRank
            FROM
              pfx_users_banned ban LEFT JOIN pfx_users usr ON (ban.u_id = usr.u_id)
                LEFT JOIN pfx_users adm ON (ban.bn_issuer = adm.u_id)
            WHERE 
              user.u_id = {$accountID}
                AND ub_expired = '0'
          "
    );
          
          
    $banInfo mysql_fetch_assoc($qBan);
          
    /*
          Array (
            [banReason] => Some reason text here.
            [banExpires] => NULL (permanent) or 2011-01-01 00:00:00 (datetime of expire)
            [userBannedName] => Example Person
            [adminName] => Some Person
            [adminLevelRank] => mod
          )
          */
          
          // do your checks here, if unbanned remember to set this ban record to expired ='1'
          // remember also to set the user account record u_banned = '0'
          
          
    die('Person is banned, until you check if it has expired');
        }
        
        
    // no active ban; login...
        
        
    $_SESSION['isLoggedIn'] = true;
        
    $_SESSION['accountID'] = $accountID;
        
        
    // quickly update last login, and redirect...
        
    mysql_query("UPDATE pfx_users SET u_last_login = NOW() WHERE u_id = {$accountID}");
        
        
    header('location: loggedin.php');
      }
    }
    ?>
    <form method="login.php">
      <input type="text" name="userMail" />
      <input type="password" name="userPasswd" />
      <input type="submit" name="btnAccountLogin" value="Login" />
    </form>
    Okay, so I want to quickly back-reference the banned query. I went slightly over-the-top just to show a GREAT example of table joining.

    However this isn't any ordinary table joining, how to join not just TWO tables, but how to join the SAME table TWICE collecting distinctive results.

    I also select specific fields to help the database engine.

    1. ban.ub_reason banReason, Just so you know, "AS" is not required, that's exactly what i'm doing getting the ub_reason field from BAN table AS banReason.
    2. CONCAT_WS(' ', usr.first_name, usr.surname) userBannedName, This is a concatenate method, _WS is With Separator. So we join first name and surname together by space AS userBannedName.
    3. pfx_users_banned ban LEFT JOIN pfx_users usr ON (ban.u_id = usr.u_id) ban represents a reference to the Banned table. usr represents a reference to the USERS table but joined by USER_ID.
    4. LEFT JOIN pfx_users adm ON (ban.bn_issuer = adm.u_id) We're joining another USERS TABLE instance onto the end of the result-set this time as ADM to get our admin/mod record.

    show_members.php
    PHP Code:
    <?php
    // Create our db instance once form is submitted (consume server work).
    MySQL::Instance();

    $qUsers mysql_query("
      SELECT
        CONCAT_WS(' ', u_first_name, u_surname) userName,
        u_level userLevel,
        CASE
          WHEN u_level = 'admin' THEN 1
          WHEN u_level = 'mod' THEN 2
          WHEN u_banned = '0' THEN 3
          ELSE 4
        END `rankSort`
      FROM
        pfx_users
      ORDER BY
        rankSort, userName ASC
    "
    );
    The above query will list all of your accounts, ordering them by Admin, Mod, Unbanned Users, Banned Users Ascending, and then their names alphabetically.

    I hope this helps (sorry it was a little rushed).

    Ash.

  • #2
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    380
    Thanks
    9
    Thanked 39 Times in 39 Posts
    Thanks for that. The only thing I would say is that your code could be open to SQL injection attacks since you are effectively concatenating the user input into the query. Using params for DB interaction may be better. (See the PEAR adodb extension).
    Regards, Stooshie
    O


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •