12 Replies Latest reply on Mar 11, 2008 4:29 PM by (Sean_DeMerchant)

    Login History

    (Laurence_Lewis)
      Hi Gunter

      I was looking at your tutorial

      http://www.guenter-schenk.com/tutorials/tutorial.php?id=1

      I have the same setup. However I was wondering if it were possible to add a Name field to this log.

      In other words when I view the list from the log history table I can see the name of the person (taken from the user table)as well as the session id etcetera.

      This way I know who was logged in and when.

      Cheers
      laurence
        • 1. Re: Login History
          Günter Schenk Level 4
          Hi Laurence,

          the "login_stats" table column "id_log" stores the "login" table´s primary key value, so all you´d need to do is to create a Join or whatever to retrieve the related username from the main table.

          Adding additional columns to the login_stats table (respectively having ADDT´s login components automatically add some values in here) would actually require to modify ADDT´s "includes" libraries, and I honestly couldn´t even tell you which file that would be :-)

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Login History
            (Laurence_Lewis) Level 1
            Hi Gunter

            Thanks, perhaps they will include me on the ADDT improvements panel hahahahah

            Cheers
            laurence
            • 3. Re: Login History
              Günter Schenk Level 4
              Hi Laurence,

              yeah, you really deserve to get immortalized anyway -- so where may I raise your statue ? :-)

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Login History
                (Laurence_Lewis) Level 1
                Hahaha :-)

                The statue of the pretender
                • 5. Re: Login History
                  (Laurence_Lewis) Level 1
                  Anyway I have been messing about and can't get it to work so i'll put it on the back burner and think about it later
                  :-)
                  • 6. Re: Login History
                    Günter Schenk Level 4
                    What about raising it in your local amusement park ? :-)

                    Cheers,
                    Günter Schenk
                    Adobe Community Expert, Dreamweaver
                    • 7. Re: Login History
                      (Laurence_Lewis) Level 1
                      Hi Gunter

                      FROM (members LEFT JOIN logger_log ON logger_log.id_log=members.id_member)

                      It works :-)

                      Cheers
                      laurence
                      • 8. Re: Login History
                        The solution to this one is to remove the `id` column from the user table and instead utilize the `username` column as the primary key.

                        This will require changing the foreign key in the log table to be a VARCHAR too.

                        As an aside, with MySQL, one can always use a VARCHAR(255) as the storage is optimized at runtime so using the largest possible field limit imposes next to zero cost and simplifies code.

                        This will in turn accomplish 2 things:

                        1) You will now have the `username` as the foreign key in the log table which is what you desire.

                        2) The second and more important affect is that the database will implicitly handle the check for duplicate usernames and enforce this constraint with the INSERT operation failing on duplicate usernames with a helpful error code. This removes the entire need to even worry about duplicate usernames.

                        The problem here with using an auto-increment `id` column is that multiple users can sign up with the same username and by having a database constraint handle this you end simplifying the software you need. One can still have a "check for username availability" feature if you want, but it should not be required any longer for correct functioning of you code.

                        In short, the usage of an auto-incremented numeric `id` column rather than using the `username` as the primary key is the source of your problem.

                        The following is some simpler database schema without all the features possible that does this (tested in MySQL 5.0.37 which means earlier MySQL versions may need modifications to this code and you may need to user a different CHARSET):

                        CREATE TABLE `addt_user` (
                        `username` varchar(255) NOT NULL default '',
                        `password` varchar(255) NOT NULL default '',
                        `email` varchar(255) NOT NULL default '',
                        `access_level` int(3) unsigned NOT NULL default '0',
                        PRIMARY KEY (`username`)
                        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

                        CREATE TABLE `addt_user_history` (
                        `id` int(10) unsigned NOT NULL auto_increment,
                        `user_id` varchar(255) NOT NULL default '0',
                        `ip_address` varchar(255) NOT NULL default '',
                        `last_login` timestamp NOT NULL default '0000-00-00 00:00:00',
                        `last_activity` timestamp NOT NULL default '0000-00-00 00:00:00',
                        `session` varchar(255) NOT NULL default '',
                        PRIMARY KEY (`id`)
                        ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                        • 9. Re: Login History
                          (Laurence_Lewis) Level 1
                          Hi Sean

                          Thank you very much for the detailed solution.

                          I will implement this as it is certainly better than my own.

                          Kind regards

                          laurence Lewis
                          • 10. Re: Login History
                            Level 1
                            "FROM (members LEFT JOIN logger_log ON logger_log.id_log=members.id_member) "

                            This does not work correctly. When using a LEFT JOIN you want the first referenced table to be the one you want to view while the second table is the one you want to use as a data source to augment the first table.

                            This is so that log entries that correspond to deleted users are still displayed (or log entries mapping to no user). This specific join will suppress such input as it will not show log entries that correspond to no user. And there is no reason to use LEFT or RIGHT JOIN syntax unless you are watching for such suspicious entries. Otherwise a normal JOIN will work fine and is easier to write if missing data is acceptable.
                            • 11. Re: Login History
                              (Laurence_Lewis) Level 1
                              Okay, thanks you have been very helpful, much appreciated
                              • 12. Re: Login History
                                Level 1
                                You are welcome. <smile> I am playing here right now because I am working on speeding up my workflow and this toolset is helpful and I want to learn more. And, solving problems and communicating ideas to other humans is the ultimate learning method.

                                enjoy,

                                Sean