Web Analytics Made Easy -
StatCounter Timing Script - Change SQL Field - CodingForum

Announcement

Collapse
No announcement yet.

Timing Script - Change SQL Field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Timing Script - Change SQL Field

    Hey,

    I was wondering if there was a way to monitor SQL rows which carry for example; "STATUS = in" and if the "LAST ACTIVE" field is 10 minutes ago to change the "STATUS" to "out"?

    Trying to create a timeout script for a basic chat room which i am creating which monitors the database constantly and updates the rows when needed... and perhaps inserts a new record into a logs file to say that the user timed out?

    Any help would be much appreciated!

  • #2
    What is monitoring the database constantly. Cron?
    You can not say you know how to do something, until you can teach it to someone else.

    Comment


    • #3
      Not familiar with crons... i know they work through the server though right?

      Was thinking a PHP script but i think it might be a bit too resource intensive to have a PHP script doing it maybe... ?

      Comment


      • #4
        Originally posted by weir-07 View Post
        Not familiar with crons... i know they work through the server though right?

        Was thinking a PHP script but i think it might be a bit too resource intensive to have a PHP script doing it maybe... ?
        Cron is a service that runs on the server. It lets you execute scheduled commands, like you could use it to run a script every minute to check your database like you want.
        OracleGuy

        Comment


        • #5
          Originally posted by weir-07 View Post
          Not familiar with crons... i know they work through the server though right?

          Was thinking a PHP script but i think it might be a bit too resource intensive to have a PHP script doing it maybe... ?
          The reason i asked is because it would be easily achievable with cron. You can use cron to run php scripts at set times. However it depends on your host. E.g not all shared hosting servers provide services for cron. My personal host http://servage.net does (i.e. the one i use for personal websites). If you have root access to your server then it will be no problem at all. If however you don't have the ability for cron jobs then i suggest a different approach maybe look into sessions/cookies?
          You can not say you know how to do something, until you can teach it to someone else.

          Comment


          • #6
            I don't have root access; but we're on a reseller account that does have cron access. Going to look into this a bit further as i'm unsure of how to go about scripting such a php script to edit the said users....

            Anyways - thanks!

            Comment


            • #7
              Ah well they might offer some support for cron but thinking about it i doubt they will allow scripts to be automatically run every 2 minutes or so. And thinking about it a little more I'm not sure it's the best way to go. I would seriously consider using sessions. Give me a bit of time and i'll come up with a solution for you
              You can not say you know how to do something, until you can teach it to someone else.

              Comment


              • #8
                First thing is setting the session up.

                PHP Code:
                session_start();
                $_SESSION['STATUS'] = "in";
                $_SESSION['TIMEIN']= time();
                echo 
                session_save_path() . "<br />";
                echo 
                session_id(); 
                Try running this on your machine and see if you get the same as me. I run this and I am outputted with

                c:/wamp/tmp
                d8c8f700471a331e47278a333cba6bb6
                I goto c:/wamp/tmp and look down the list of files that are the sessions running. One of the files is named. sess_d8c8f700471a331e47278a333cba6bb6

                I open it with a text editor and it has

                STATUS|s:2:"in";TIMEIN|i:1239749187;

                I can use this data to check if the timestamp recorded here exeeds 10 mins. Infact i don't really need the STATUS session variable here i could just use TIMEIN to simplify. What i intend to do it change the setting session up script like so

                PHP Code:
                <?php
                // Initialise session must be called at top of every page the user goes to.
                session_start();
                $_SESSION['TIMEIN']= time();
                Last edited by timgolding; Apr 14, 2009, 09:02 PM.
                You can not say you know how to do something, until you can teach it to someone else.

                Comment


                • #9
                  I would just create a TIMESTAMP column that updates whenever any column is changed in that row. Then use PHP to check the current time with the TIMESTAMP and if the difference is more than 10 minutes the user would be timed out.

                  I don't think PHP has a date function that communicates with the MySQL time format very good so you would have to create your own custom PHP date function, but this would be very easy.




                  -------------
                  Leonard Whistler

                  Comment


                  • #10
                    i have the following set up for my current users file which basically contains all of the settings for any individuals chat session which is inserted into whenever a user logs in.

                    PHP Code:
                    `chat_users` (
                      `
                    idint(6unsigned zerofill NOT NULL auto_increment,
                      `
                    usernamevarchar(250NOT NULL,
                      `
                    handlevarchar(250NOT NULL,
                      `
                    handle_fontvarchar(100NOT NULL,
                      `
                    handle_colourvarchar(100NOT NULL,
                      `
                    rankvarchar(250NOT NULL,
                      `
                    clvarchar(100NOT NULL,
                      `
                    patron_colourvarchar(100NOT NULL,
                      `
                    avatarvarchar(600NOT NULL,
                      `
                    patron_statusvarchar(100NOT NULL,
                      `
                    patron_ipvarchar(100NOT NULL,
                      `
                    timevarchar(100NOT NULL,
                      `
                    datevarchar(100NOT NULL,
                      
                    PRIMARY KEY  (`id`)
                    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 
                    The "patron_status" field is always set to either "in", "out" or "timeout"
                    The "time" and "date" fields are updated whenever a user transmits or receives a message in the chat room. When logging in the system checks whether somebody is already in or not; if there is no record of them being "in" then they are required to login; if they are currently set as "in" then they simply re-enter the chat room and their "time" and "date" fields are updated.

                    Onto the actual problem...

                    I planned on somehow figuring a way to simply compare the current time and date and if the result is over 10 minutes then the "patron_status" is updated to "timeout" so that the user can receive a timeout notification if they try to use the chat after the timeout period.

                    Going by the above i would need to insert the SESSION TIME into my time field and compare that... but i don't understand how i would go about comparing the two times in order to activate an SQL Update. I see how it could be done if a user was again active after 10 minutes - but what about when they aren't... i can't get my head around how i can timeout somebody without that person activating the process... if that makes any sense?

                    Could anybody shed any light on this or am i over thinking it as usual? :S

                    Comment


                    • #11
                      Originally posted by weir-07 View Post
                      I can't get my head around how i can timeout somebody without that person activating the process... if that makes any sense?

                      Could anybody shed any light on this or am i over thinking it as usual? :S
                      Nope you are not over thinking ...... You have brought up the limitations of PHP/MySQL in this situation, the user must reload the page for PHP to update the users status.

                      I know very little about Javascript and Ajax but I think if you want a real time update solution without reloading the page you might have to try Javascript and Ajax.




                      ---
                      Leonard Whistler

                      Comment


                      • #12
                        Use string to time:
                        http://us3.php.net/manual/en/function.strtotime.php

                        PHP Code:
                        //GET DATE FROM DB AND ASSIGN IT TO $y
                        $db_date date('l jS \of F Y h:i:s A'strtotime($y));
                        $now strtotime("now");
                        $ten_min strtotime("+10 minutes");

                        $compare $now $ten_min;

                        if (
                        $db_date $compare){
                        //do the alert or change the db.... HERE!!! :)

                        Untested....

                        Comment


                        • #13
                          Now you need the script that check if a user is timeout or not so lets say we want to check if user with user_id=7 is timeout or not. By the way you will have to set up a session variable for "user_id" when the user logs in. $_SESSION["user_id"] = 7;


                          Here's the script to check if user with user_id =7 session has expired.

                          PHP Code:

                          if ($handle opendir(session_save_path())) {
                          $userfound=false;
                          while (
                          false !== ($file readdir($handle))) {
                                  if(!
                          in_array($file, array(".""..")))
                              if(
                          is_file(session_save_path()."/".$file))
                              {
                                  
                          $session_string file_get_contents(session_save_path()."/".$file);
                                  if(
                          strstr($session_string"user_id|i:7;"))
                                  {
                                      
                          $user_session_string $session_string;
                                      
                          $userfound=true;
                                  }
                              }
                              }
                              
                              
                          closedir($handle);
                          }

                          if(
                          $userfound)
                          {
                              
                          //compare timestamp
                               
                          preg_match("^TIMEIN\|i:[0-9]{10,100};^"$user_session_string$match);
                              
                          $userstamp rtrim(ltrim($match[0], "TIMEIN\|i:"), ";");
                              
                          $userstamp_plus10 = (int)$userstamp + (10 60);
                              if(
                          $userstamp_plus10 time())
                                  echo 
                          "session expired";
                              else
                                  echo 
                          "not expired";
                              
                              
                          }
                          else
                          {
                              echo 
                          "session expired";

                          That all works fine and i have tested it. You can also set timeout on your sessions. sorry if i confused you earlier i shouldn't have mentioned adding the session id to the database as there is no need when using sessions. So i deleted all that garbage from my post. So literally all you need to do to start the session is this.

                          PHP Code:
                          <?php
                          // Initialise session must be called at top of every page the user goes to.
                          session_start();
                          $_SESSION['TIMEIN']= time(); 
                          ?>
                          Last edited by timgolding; Apr 14, 2009, 09:34 PM.
                          You can not say you know how to do something, until you can teach it to someone else.

                          Comment


                          • #14
                            Originally posted by sea4me View Post
                            Use string to time:
                            http://us3.php.net/manual/en/function.strtotime.php

                            PHP Code:
                            //GET DATE FROM DB AND ASSIGN IT TO $y
                            $db_date date('l jS \of F Y h:i:s A'strtotime($y));
                            $now strtotime("now");
                            $ten_min strtotime("+10 minutes");

                            $compare $now $ten_min;

                            if (
                            $db_date $compare){
                            //do the alert or change the db.... HERE!!! :)

                            Untested....
                            Ah thank you Now i can just work on updating the database for all users and not just the ones who activate the script at their end to trigger their account timeout.

                            Originally posted by timgolding View Post
                            Now you need the script that check if a user is timeout or not so lets say we want to check if user with user_id=7 is timeout or not. By the way you will have to set up a session variable for "user_id" when the user logs in. $_SESSION["user_id"] = 7;


                            Here's the script to check if user with user_id =7 session has expired.

                            PHP Code:

                            if ($handle opendir(session_save_path())) {
                            $userfound=false;
                            while (
                            false !== ($file readdir($handle))) {
                                    if(!
                            in_array($file, array(".""..")))
                                if(
                            is_file(session_save_path()."/".$file))
                                {
                                    
                            $session_string file_get_contents(session_save_path()."/".$file);
                                    if(
                            strstr($session_string"user_id|i:7;"))
                                    {
                                        
                            $user_session_string $session_string;
                                        
                            $userfound=true;
                                    }
                                }
                                }
                                
                                
                            closedir($handle);
                            }

                            if(
                            $userfound)
                            {
                                
                            //compare timestamp
                                 
                            preg_match("^TIMEIN\|i:[0-9]{10,100};^"$user_session_string$match);
                                
                            $userstamp rtrim(ltrim($match[0], "TIMEIN\|i:"), ";");
                                
                            $userstamp_plus10 = (int)$userstamp + (10 60);
                                if(
                            $userstamp_plus10 time())
                                    echo 
                            "session expired";
                                else
                                    echo 
                            "not expired";
                                
                                
                            }
                            else
                            {
                                echo 
                            "session expired";

                            That all works fine and i have tested it. You can also set timeout on your sessions. sorry if i confused you earlier i shouldn't have mentioned adding the session id to the database as there is no need when using sessions. So i deleted all that garbage from my post. So literally all you need to do to start the session is this.

                            PHP Code:
                            <?php
                            // Initialise session must be called at top of every page the user goes to.
                            session_start();
                            $_SESSION['TIMEIN']= time(); 
                            ?>
                            Hey again - thanks muchly but i don't really see how this interacts with my database... :S

                            Comment


                            • #15
                              It doesn't. It doesn't need to it uses sessions. The advantage of doing it this way is you can set timeout for your sessions. If sessions are set to timeout after 10 mins then alot of this code would be redundant.
                              You can not say you know how to do something, until you can teach it to someone else.

                              Comment

                              Working...
                              X