Log Table For User Interaction.

Yii 1.1 General Discussion
1

Hello! This isn’t really a Yii problem, but I didn’t really know in which other board to post this.

The problem is simple. I want to have a table where I save the user’s interaction in the application. For example…

Table name: access

columns: id_access BIGINT,id_user INT, date TIMESTAMP, ip VARCHAR, latitude VARCHAR, longitude VARCHAR

Each time a user does something like adding an item to a table (say, a product), updates, deletes, etc., the user’s action would be saved to this table for future reference. This log would be for many things like products, sales, etc., not just a single table.

[b]That was the problem. The following is my particular situation.

[/b]

Since the application will eventually become big, there are some issues I need to address. First, I’m considering not using MySQL for this log information, since it will eventually be huge and as far as I’ve read, MySQL may have some trouble with tables that have tons of rows (talking about 10,000,000+). I was thinking about trying MongoDB for the log information, though I don’t know much (if anything) about it yet, and it seems that it may make it harder to analyze the log data and compare it with the MySQL information. This comparisons will eventually be useful for business analytics in the application.

[b]I’m looking for suggestions about how to tackle this situation. This is what I came up with, but I’m not sure if it will work.

[/b]

The MongoDB solution would involve having the MySQL database exactly as it is, but move the log table into a MongoDB Collection where its humongous row size wouldn’t be troublesome.

There’s also the problem about logging the information correctly (as in, store which user deleted which item from the database). I was thinking that MongoDB could be useful in this case, too. Depending on what the users does (basically any CRUD), I could insert the appropiate information in the MongoDB collection for later retrieval. For example…

User creates an item - The MongoDB collection would have something like: id_access,id_user, date, ip, latitude, longitude, user_action (in this case, create), item_name, item_id, etc…

User deletes an item - The MongoDB collection would have something like: id_access,id_user, date, ip, latitude, longitude, user_action (in this case, delete), item_name, item_id, etc…

When deleting, the item would no longer exist in the MySQL database, but it wouldn’t matter since there would be no table relation inconsistencies in MySQL and the information in the MongoDB collection could eventually be used in conjunction with the information in MySQL to generate some interesting reports. I’m not exactly sure how this last part would work, so I’m just guessing there.

Does anyone have experience from a similar situation? Any suggestions on what to do? Alternatives?

Thank you for your time.

2

Hello, I need to do the same exactly thing, track user behavior in mongodb while keep my MySQL store working, I just need a little ‘connection’ between some id’s in both databases, did you get something? Could you finally solve it?

Thanks

Alex

3

Hello Alex,

This response is way late, I know. I actually went forward with MongoDB and stored every action there. In the Controller class from which every other controller inherited, I did the following:




public function beforeAction($action){

     //Inserted all needed data into my mongo collection

     mongo=new MongoClient();

        $collection=$mongo->user_log->user_log;


        //This is saved in the session to avoid calling the php browscap file in every request, since it's a bit heavy.

        if(!isset(Yii::app()->session['browserInfo'])){

            $browserInfo=array();

            $browser=Yii::app()->request->browser;

            $browserInfo['browser']=$browser['browser'];

            $browserInfo['version']=$browser['version'];

            $browserInfo['platform']=$browser['platform'];

            $browserInfo['platformVersion']=$browser['platform_version'];

            $browserInfo['mobile']=$browser['ismobiledevice'];

            $browserInfo['cssVersion']=$browser['cssversion'];

            $browserInfo['crawler']=$browser['crawler'];

            Yii::app()->session['browserInfo']=$browserInfo;

        }


        $info=Yii::app()->session['browserInfo'];


        $stamp=time();

        $userLog=array(

            'userId'=>Yii::app()->user->isGuest ? 'guest' : Yii::app()->user->id,

            'userTypes'=>Yii::app()->user->isGuest ? 'guest' : Yii::app()->user->userTypes,

            'date'=>date('Y-m-d H:i:s', $stamp),

            'timestamp'=>$stamp,

            'module'=>$action->controller->module==null ? null : $action->controller->module->id,

            'controller'=>$action->controller->id,

            'action'=>$action->id,

            'ip'=>Yii::app()->request->userHostAddress,

            'sessionId'=>Yii::app()->session->sessionID,

            'browserInfo'=>array(

                'browser'=>$info['browser'],

                'version'=>$info['version'],

                'platform'=>$info['platform'],

                'platformVersion'=>$info['platformVersion'],

                'mobile'=>$info['mobile'],

                'cssVersion'=>$info['cssVersion'],

                'crawler'=>$info['crawler']

            )

        );

        $collection->insert($userLog);

     return true;

}

We never actually had a chance to use the data, and eventually we just started to implement third party solutions like MixPanel. However, I do believe that the above is a very viable solution if you can’t pay for a service. The user Id is stored in the Mongo Collection so you can use it later if you need to make some analytics together with MySQL.

Hope it helps!

4

[color="#1C2837"]Many thanks ASPtoYiiDev, I will look it in detail. In the meanwhile I was playing with a easy and quite good solution using caching and mysql, to avoid overloading but being able to get the data easily[/color]

[color="#1C2837"]thanks again,[/color]

[color="#1C2837"]Alex[/color]