Best practices: Sessions Page 3

Lets suppose now we have another table called memos which stores memos written by users of this secure
website where access level privilges dictate who can update a memo.When this table is normalised it will
look something like this:

   relSecurityID    details                  date     time    memo_id

      2             what is gnu/linux ?      020823    1426     1
      6             heard of open source?    020823    1705     2
      4             dependencies!!!          020824    0852     3

In this we don’t need to store the access level privileges or the name of the person who writes/updates the
memo all we need to store is the security_id, stored under relSecurityID (foreign key) in this table,
because this allows us to potentially reconstruct any security information we may need.

For example suppose an authorised user is searching/scrolling through a screen of memo extracts on his
browser from which he can select to see the full memo by clicking on a hyperlink, it will be necessary to
trap the memo_id and pass it to the next page this will then allow to reconstruct the complete memo and
potentially any associated security related information through the foreign key relSecurityID:

<?php
   $SQL=" SELECT details,date,time FROM memo   "; 
    $memo = mysql_query($SQL);
       while($row = mysql_fetch_array($memo) ) {     // produce all memos extracts....
               $details = $row['details']; 
                $date = $row['date'];               .... // etc
?> 
                  //    display a particular memo extract..
                  //    hyperlink...
      <a href="update_memo.php?memo_id=<? echo $memo_id; ?>"> memo </a> 
  <?     }   ?>

On reaching the update memo page I would then consider registering the memo_id to the session
($sess_memo_id) as then you can potentially reconstruct that last memo read/updated using previous
techniques.

Once on this page you may need to determine whether the memo information displayed in a text box
is read only by checking access level privileges of the person who wrote the memo against those of the
person currently viewing the memo.
We have seen how to determine the access level privileges of the current authorised user, to determine
those of the user who wrote the message we constuct a query like the following:

<?php
 $SQL = " SELECT access_level FROM security,memo 
             WHERE security.security_id = memo.relSecurityID
                AND memo_id = '$sess_memo_id' "  ;
?>

This query will join the information for the particular memo with its associated information in the
security table to determine access level privileges of the person who wrote the message ,other
related information could be extracted if required by extending the select statement.

Take time to understand the above SQL query, if you are not familiar to joins,
as extracting information from normalised tables in this way is an important skill to master,

      ________________________        ____________________________
     |  X        security_id  |       | relSecurityID     memo_id  |
     |________________________|       |____________________________|

        security record                   memo record

it’s kind of like a game of dominoes where a particular security record can connect with many memo records
providing security_id and relSecurityID match !