Results 1 to 6 of 6

Thread: SQL Timer?

Hybrid View

  1. #1
    Join Date
    Jun 2004
    Location
    USA
    Posts
    59

    Talking SQL Timer?

    I was wondering if sql has a way to reset a value of a table based on time.

    an example would be like cookies you give it a time and they either get destoryed or deleted.

    I made a table called items

    item - Reset Time - active


    apple - 7days - 1

    I wanted to have the active chage to 0 after 7 days have passed.

    My problem is that I just started sql and none of the books I have help answer my question.

  2. #2
    Join Date
    Jul 2004
    Location
    N29° 22.725' X W95° 6.452'
    Posts
    343
    You can do it by running a php script with a cron job or through windows task scheduler (depending on the server OS you are using).

    have it run your script, ex: c:\>C:\php\php.exe reset_fields.php on a regular basis like every hour, once a day, once a month, etc... what ever you like.

    then have your script select the record you want, decide if it needs to be reset and then, if it needs it, reset it.

    I am assuming you know how to code the script. If not, post back and I can help you out.

  3. #3
    Join Date
    Jun 2004
    Location
    USA
    Posts
    59
    Still new to php and sql so any help on what the script would look like will be appreciated.

    Another question would be what if that server is not a home server but hosted? Will that script work still?

  4. #4
    Join Date
    May 2004
    Location
    Colorado
    Posts
    948
    well, it depends how many rows of data you are expecting in your database, and wether you want to reset all of the rows or just a single row.
    to do this, you could do something like:
    PHP Code:
    <?php
    $link 
    mysql_connect(args);
    //err check link

    //you do not need the WHERE arguments if you just want to reset every row in the table, or if thre is only one row

    $sql "UPDATE tbl_name SET col_name = \"\" WHERE col_header = \"param\"";
    $query mysql_query($sql$link);
    //error check query
    hope that helps you out.
    as for running an automated task on a hosted server, i have no idea, it might be possible if you host as something set up to allow you to do it, otherwise i think you might have to find another way. someone else can probably answer that for you though


    Absolute Power, its the only way to roll!

  5. #5
    Join Date
    Jul 2004
    Location
    N29° 22.725' X W95° 6.452'
    Posts
    343
    Check with your web host, most good web hosts will let you set up cron jobs, especially if they offer PHP-MYSQL support. If your host has a user interface like C-Panel or something look around for a cron feature. If you cant find it email their support and ask if they offer it and if so how to go about setting it up.

    If you need help with cron jobs, google it, its fairly simple. It is just setting up when and how often to do something on th e server (in this case run your php script to update your database).

  6. #6
    Join Date
    Jun 2004
    Location
    USA
    Posts
    59
    Came up with something on my own.

    PHP Code:
    <?
    /*

    exec something when date/time are right.
    I used the $flag  0 or 1 to figure out if it was right or wrong.
    this way I can know if the script is doing its job.

    */

    //server date
    $flag = date(" d ");

    //server time
    $time_flag = date(" His ");

    //Switch to date
    switch ($flag)
    {

    //case pre set on date
    case 7:
        //execute funtion to check if true
        $see = time_exec_7($time_flag);
        echo $see;
    break;

    }


    //function set for day 7
    function time_exec_7($time_flag)
    {
        //server reset time
        $server_time = 121212;

        //compare if time flag is greater than server time
        if($time_flag < $server_time)
        {
            //if its true then DO NOT DO ANYTHING since time has not passed.
            echo "time is less so NO execute ";
            $time_flag = 0;
        }else{
            //if its false then time has passed and get ready to check if we need to update.
            echo "time has passed.  Execute after check date? ";

            //confirms that first part is ok.
            $time_flag = 1;

                //we open the file and read
                $path = "date.txt";
                $open = fopen($path, "r+");
                $read = fread($open, filesize($path));
                $data = $read;
                fclose($open);

                //if the date we just read is less then 7
                if($data < "7")
                {
                    //TURE! if its true then show it by 1
                    $time_flag = 1;

                    //write 7 on the file so we dont do this again.
                    $path = "date.txt";
                    $open = fopen($path, "w");
                    $write = fwrite($open, "7");
                    fclose($open);

                }else{

                    //FALSE! we already have executed the script. Dont do it again.
                    $time_flag = 0;
                }

        }
    //return the last value.
    return $time_flag;
    }

    ?>
    Hope this helps someone that might be looking for the same thing I was.
    Last edited by Teske; 01-15-2005 at 04:05 AM.

Posting Permissions

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