www.webdeveloper.com
Results 1 to 6 of 6

Thread: Moving away from flatfiles to SQL, a lot of help needed!

  1. #1
    Join Date
    Mar 2003
    Location
    Somewhere on Planet Earth, I hope.
    Posts
    987

    Moving away from flatfiles to SQL, a lot of help needed!

    Hello,
    Not quite sure where to begin on this, but I really really would be thankful for any help I could get from you pro PHP/SQL wizzes.

    For a long time now, we've used flatfiles to deal with pretty much everything on our site. However, it's now becoming a little bit of a pain, especially as we want to include some new things such as a script for our staff to add other staff members and change credentials -- just more functionality.

    Before we begin that though, we need to move over our current login system, and logging system from flatfiles to databases... and I really need help doing that.

    We actually don't have that much, so please keep reading *pray*

    --------------------------------------------------------------------
    FORMS:
    --------------------------------------------------------------------

    Firstly, at the moment on every form on our website, we issue a unique ID. The way we do this is with the following code on each of our PHP mailers:

    PHP Code:
    $fh fopen("/home/hidden/support.db","r");
    $id fread($fhfilesize("/home/hidden/support.db"));
    fclose($fh);

    $id++;

    $fh fopen("/home/hidden/support.db","w");
    fwrite($fh$id);
    fclose($fh); 
    Then to echo it out:

    PHP Code:
    $subject "Direct [ID:" $id ."]";

    and

    $msg .= "ID................. Direct $id\n"
    Is that easy to migrate into a simple SQL database? We use more than one though, as we have multiple forms. Each form has a unique ID so a seperate ID would be needed...

    --------------------------------------------------------------------
    LOGIN SYSTEM:
    --------------------------------------------------------------------

    For our login system, we have a /staff area which is protected by a login. Each page within that login checks that the user is successfully logged in, AND that they have the right permissions -- we use "groups" to do that.

    Firstly, our /staff/index.php is the actual HTML login -- so don't need any help there. It submits to a login.php which has:
    As you'll be able to see, as well as checking all the relevant details, it logs the login itself. This is explained later under "EVENTS:" of how we currently show it.

    PHP Code:
    <?php
    session_start
    ();

    function 
    output_error($text='')
    {
        include_once(
    '/home/header.php'); 
          echo 
    "<p class=\"pagetitle\">There were some errors in your submission...</p>\n<div class=\"messagebox\">\n<p>Please correct the following:</p>\n<ul>\n" $text "\n</ul>\n</div>\n";
        include_once(
    '/home/footer.php');
        exit();
    }

    if (!isset(
    $_SESSION['correctcode'])) {
        
    output_error('<li><span>Please enable cookies.</span></li>');
    }

    $correctcode $_SESSION['correctcode'];
    $securitycode $_POST['securitycode'];
    if (
    $securitycode != $correctcode) {
        
    output_error('<li><span>The human validation check failed; please try again.</span></li>');
    }

    $user_data file("/home/hidden/users.db");
    foreach(
    $user_data as $val)
    {
      list(
    $user$pwd) = explode(","trim($val));
      
    $users[$user] = $pwd;
    }

    $account $_POST['account'];
    $password md5($_POST['password']);

    if (
    array_key_exists($account$users))
    {
        if (
    $password == $users[$account])
        {
          
    $_SESSION['logged'] = true;
          
    $_SESSION['account'] = $account;
          
    $_SESSION['password'] = $password;
          
    $_SESSION['ip'] = $_SERVER['REMOTE_ADDR'];
          
    setcookie('logindate'date('d/m/Y'));
          
    setcookie('logintime'date('H:i:s'));
          if (isset(
    $_SESSION['location'])) {
            
    header("Location: "$_SESSION['location']);
            
    $stuff_to_prepend "STAFF PORTAL LOGIN: " date('D M d H:i:s Y T') . ": " $_SESSION['account'] . " using IP: " $_SESSION['ip'] . ".";
            
    $filename "/home/hidden/logins.db";
            
    $file_contents file_get_contents($filename);
            if(
    $fp fopen($filename'w+'))
            {
              
    fwrite($fp$stuff_to_prepend."\n".$file_contents);
              
    fclose($fp);
            }
            unset(
    $_SESSION['location']);
            exit();
          }
          
    header ("Location: http://us.net/staff/home.php");
          
    $stuff_to_prepend "STAFF PORTAL LOGIN: " date('D M d H:i:s Y T') . ": " $_SESSION['account'] . " using IP: " $_SESSION['ip'] . ".";
          
    $filename "/home/hidden/logins.db";
          
    $file_contents file_get_contents($filename);
          if(
    $fp fopen($filename'w+'))
          {
            
    fwrite($fp$stuff_to_prepend."\n".$file_contents);
            
    fclose($fp);
          }
          unset(
    $_SESSION['location']);
          exit();
        }
        else
        {
          
    output_error('<li><span>Sorry, your password has been entered incorrectly. <a href="/staff/index.php">Please login again</a>.</span></li>');
        }
    }
    else
    {
        
    output_error('<li><span>Sorry, your account has not been recognised. <a href="/staff/index.php">Please login again</a>.</span></li>');
    }
    ?>
    Our users.db looks like:

    Code:
    user,md5hash
    user2,md5hash
    Our logout.php:

    PHP Code:
    <?php
     ob_start
    ();
     require_once(
    '/home/staff/protect.php');

     
    setcookie('logindate'''time()-60);
     
    setcookie('logintime'''time()-60);

     unset(
    $_SESSION['logged']);
     
    header("Location: http://us.net/staff/index.php");
     
    ob_end_flush();
     
    ?>
    Our protect.php which is included by all files that are protected to check if the user is logged in:

    PHP Code:
    <?php
    $back 
    "<form method=\"post\" action=\"/staff/index.php\"><div><input type=\"submit\" name=\"login\" class=\"bg\" value=\"Log in\" /></div></form>\n";
    $acc_denied "<p class=\"pagetitle\">Sorry, you must be logged into your Staff Portal account in order to access this page.</p>\n".$back;

    session_start();

    if (!isset(
    $_SESSION['logged'])) {
        
    $location $_SERVER['PHP_SELF'];
        
    $_SESSION['location'] = $location;
        
    header("Location: /staff/index.php");
        exit();
    }
     
    ?>
    Our "groups.php" includes what permissions each user has... this is explained in the next "sample.php" of how we check for them:

    PHP Code:
    <?php
     
    include_once('/home/staff/protect.php');

    class 
    Group
    {
      
    // Attributes:
      
    var $groups = array();

      
    // Methods:

      // constructor:
      
    function Group()
      {
        
    // populate groups with users:
        
    $this->groups = array("NM" => array("User1",
                                            
    "User2",
                                            
    "User3"),
                              
    "NOC" => array("User1"),
                              
    "SM" => array("User2",
                                            
    "User3") );
      } 
    // end constructor

      // Determine if user is member of at least one of the specified group(s)
      // Usage: in_group("username", "group1[,group2[...,groupn]]");
      
    function in_group($user$group)
      {
        
    $list explode(","$group);
        foreach(
    $list as $value)
        {
          if(
    in_array($user$this->groups[$value]))
          {
            return(
    TRUE);  // found it
          
    }
        }
        return(
    FALSE);
      } 
    // end in_group()

      // return array of groups to which user is a member, or false if not
      // a member of any:
      
    function get_groups($user)
      {
        
    $groups = array();
        foreach(
    $this->groups as $key => $value)
        {
          if(
    in_array($user$value))
          {
            
    $groups[] = $key;
          }
        }
        if(
    count($groups) == 0)
        {
          return(
    FALSE);
        }
        return(
    $groups);
      } 
    // end get_groups()
    // end class Group
     
    ?>
    A sample.php file, which shows how we check the user is logged in, and if they're in the right groups (based from groups.inc.php):

    PHP Code:
    <?php
     
    include_once('/home/staff/protect.php');
     include_once(
    '/home/header.php');
     require 
    "/home/staff/groups.inc.php";

     
    $grp = new Group();

    if(
    $grp->in_group($_SESSION['account'], "NM,NOC"))
    {
     
    ?>

    Content goes here.

    <?php
     
    include_once('/home/footer.php');
    }
    else
    {
      echo 
    "<p class=\"pagetitle\">You don't have permission to view this. You need at least NOC</p>\n";
     include_once(
    '/home/footer.php');
    }
     
    ?>
    --------------------------------------------------------------------
    EVENTS (LOGGING) SYSTEM:
    --------------------------------------------------------------------

    As mentioned before, from the login.php we log certain details that can be displayed.

    logins.php

    PHP Code:
    <?php
     
    include_once('/home/staff/protect.php');
     include_once(
    '/home/header.php');
     require 
    "/home/staff/groups.inc.php";

     
    $grp = new Group();

    if(
    $grp->in_group($_SESSION['account'], "NM,NOC"))
    {
     
    ?>


                <div class="services">
                  <p><code><?php
    $lines_to_show 
    $_POST['lines']; 

    $mail_db fopen("/home/hidden/logins.db""r"); 

    for(
    $i 0$i $lines_to_show$i++) { 
         
    $lines .= fgets($mail_db); 


    echo 
    nl2br(strip_tags($lines))
    ?></code></p>
                </div>

    <?php
     
    include_once('/home/footer.php');
    }
    else
    {
      echo 
    "<p class=\"pagetitle\">You don't have permission to view this. You need at least NOC</p>\n";
     include_once(
    '/home/footer.php');
    }
     
    ?>
    What do you think?

    Thank you very much in advance.
    Kind regards,
    Daniel.

  2. #2
    Join Date
    Mar 2003
    Location
    Somewhere on Planet Earth, I hope.
    Posts
    987
    And lastly...password changing:

    PHP Code:
    <?php
    session_start
    ();
     include_once(
    '/home/staff/protect.php');
     require 
    "/home/staff/groups.inc.php";

     
    $grp = new Group();

    if(
    $grp->in_group($_SESSION['account'], "NM,NOC,SM"))
    {

    function 
    output_error($text='')
    {
        require_once(
    '/home/staff/protect.php');
        include_once(
    '/home/header.php');
          echo 
    "<p class=\"pagetitle\">Alert...</p>\n<div class=\"messagebox\">\n<p>Alert:</p>\n<ul>\n" $text "\n</ul>\n</div>\n";
        include_once(
    '/home/footer.php');
        exit();
    }

    if (
    $newpass != $newpass2) {
        
    output_error('<li><span>Your new and confirmed passwords do not match.</span></li>');
    }
    if (
    $_POST['nowpass'] == $_POST['newpass']) {
        
    output_error('<li><span>Your old and new passwords appear to be the same. Please only use this facility to change your password.</span></li>');
    }
    if (!isset(
    $_SESSION['correctcode'])) {
        
    output_error('<li><span>Please enable cookies.</span></li>');
    }

    $correctcode $_SESSION['correctcode'];
    $securitycode $_POST['securitycode'];
    if (
    $securitycode != $correctcode) {
        
    output_error('<li><span>The human validation check failed; please try again.</span></li>');
    }

    $user $_SESSION['account'];
    $nowpass md5($_POST['nowpass']);

    $newpass md5($_POST['newpass']);
    $newpass2 md5($_POST['newpass2']);

    if( 
    $nowpass == $_SESSION['password'] ) {

        
    $filename "/home/hidden/users.db";
        
    $file file$filename );
        
    $newfile '';

        foreach(
    $file as $val) {
            
    $val trim($val);
            
    // There was space at the beginning and end of the line for me
            // to see the space in action uncomment the next line
            // var_dump($val);
            
    if($val == "{$user},{$nowpass}") {
                
    $newfile .= "{$user},{$newpass}\n";
            } else {
                
    $newfile .= "{$val}\n";
            }
        }

        if(
    is_writable($filename)) {

            if(!
    $fp fopen$filename"w" )) {
                
    output_error('<li><span>There was an error talking to the database. Please try again later.</span></li>');
            }

            if(
    fwrite$fp$newfilestrlen($newfile) ) === FALSE) {
                
    output_error('<li><span>There was an error writing the database. Please try again later.</span></li>');
            }

            
    fclose($fp);
            
    $stuff_to_prepend "STAFF PASS CHANGED: " date('D M d H:i:s Y T') . ": " $_SESSION['account'] . " using IP: " $_SESSION['ip'] . ".";
            
    $filename "/home/hidden/passwd.db";
            
    $file_contents file_get_contents($filename);
            if(
    $fp fopen($filename'w+'))
            {
              
    fwrite($fp$stuff_to_prepend."\n".$file_contents);
              
    fclose($fp);
            }
            
    output_error('<li><span>Success! Your password has been successfully changed.</span></li>');

        } else {
            
    output_error('<li><span>The database seems to be non-writable. Please try again later.</span></li>');
        }

    } else {
        
    output_error('<li><span>Sorry, your old password has been entered incorrectly.</span></li>');
    }

    }
    else
    {
     include_once(
    '/home/header.php');
      echo 
    "<p class=\"pagetitle\">You lack access.</p>\n";
     include_once(
    '/home/footer.php');
    }
    ?>
    Kind regards,
    Daniel.

  3. #3
    Join Date
    Feb 2006
    Location
    Where I live, people get shot. I need to move.
    Posts
    634
    All a database consists of is a set / collection of flat files that are stored in a database (container)

    The process of migration should then be far simpler with that knowledge in mind.
    If life gives you a hard knock once in a while, your lucky. All I keep getting is a savage beating... everyday!
    AJAX! - Javascript Sha1 & MD5 - ASCII tables - Zend DevZone - My Space
    Stop Badware - Create "TEXT FILE" in Javascript - . - wikipedia on PHP -

  4. #4
    Join Date
    Mar 2003
    Location
    Somewhere on Planet Earth, I hope.
    Posts
    987
    Yep, I know that. The migration means it needs SQL queries and stuff, which I'm not familiar with
    Kind regards,
    Daniel.

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    There are a lot of books out there on the subject "Dynamic websites using PHP and MySQL"

    You might want to start with one of those to understand the concepts, and if you have questions doing the conversion you should ask. The concepts in those books are typically database independent, but focus on MySQL since it's the most popular amongst PHP platform users.

    Also, netbuddy, MySQL's actually one of the few DBMS's around that store all of their data directly in flat files. most dbms store in binary files.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  6. #6
    Join Date
    Feb 2006
    Location
    Where I live, people get shot. I need to move.
    Posts
    634
    How the data is presented to the end user is what I am getting at, you view the tables in the database as you would any other database, the actual storage method of the server is not the concern of the user just the fact that you can easily visualise a set of tables (flat files) inside a database, if you go pointing out that thats not how things are stored, you only go to confuse the matter further.
    Last edited by netbuddy; 11-17-2006 at 10:14 AM.
    If life gives you a hard knock once in a while, your lucky. All I keep getting is a savage beating... everyday!
    AJAX! - Javascript Sha1 & MD5 - ASCII tables - Zend DevZone - My Space
    Stop Badware - Create "TEXT FILE" in Javascript - . - wikipedia on PHP -

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles