Checking The Time in DB Field.

[PaulSonny]

Hello Everyone,

I have a database field which has a timestamp in it, named datalastupdate.

I have an admin option, named helpcenter_auto_close which sets the period of time that has to pass.

I have the following php code:

PHP Code:

<?php
if ($vbulletin->options['helpcenter_auto_close']>0){
    $ticketreply = $vbulletin->db->query_first("SELECT * FROM " . TABLE_PREFIX ."helpcenter_ticket");
    while ($ticket = $vbulletin->db->fetch_array($ticketreply))
    {    
        $ticketid = $ticket['ticketid'];
        $hc_time1 = $vbulletin->options['helpcenter_auto_close'];
        $hc_time1 = TIMENOW;
        $hc_timediff = intval($hc_time1-$hc_time2);
        if ($ticket['datelastupdate'] <= $hc_timediff){
        $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX ."helpcenter_ticket SET ticketstatus=0 WHERE ticketid=$ticketid");
        }
    }
}
?>

I'm trying to get it so that if the ticket has not been replied to for the set period in the admin options then it will close the ticket, but it wont work. Im doing this will a scheduled task.

I cant see what i'm doing wrong, can anybody help me?

Thanks, Paul.

[MoT3rror]

Is your column in your database store UNIX time or date and time like in this format, year-month-day hour:minute:second?

Also you are overwriting the value of $hc_time1 here and $hc_time2 doesn't look to be set.

PHP Code:

$hc_time1 = $vbulletin->options['helpcenter_auto_close'];
        $hc_time1 = TIMENOW; 


[PaulSonny]

To be honest i'm not sure what is used. When adding the timestamp to the database i've just used TIMENOW the same as I have used it above.

Just realised the error with the $hc_time and have sorted it thanks, but still not working.

Thanks, Paul.

Updated Code:

PHP Code:

<?php
if ($vbulletin->options['helpcenter_auto_close']>0){
    $ticketreply = $vbulletin->db->query_first("SELECT * FROM " . TABLE_PREFIX ."helpcenter_ticket");
    while ($ticket = $vbulletin->db->fetch_array($ticketreply))
    {    
        $ticketid = $ticket['ticketid'];
        $hc_time1 = TIMENOW;
        $hc_time2 = $vbulletin->options['helpcenter_auto_close'];
        $hc_timediff = intval($hc_time1-$hc_time2);
        if ($ticket['datelastupdate']<=$hc_timediff){
        $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX ."helpcenter_ticket SET ticketstatus=0 WHERE ticketid=$ticketid");
        }
    }
}
?>

[MoT3rror]

Have you tried running the cron in your browser to see if any errors occur? Also if the time you are supplying isn't in UNIX time it wouldn't work.

Also does $vbulletin->options['helpcenter_auto_close'] contain a number above 0?

[PaulSonny]

Does using TIMENOW when adding to the DB not store it in UNIX?

I have tried running it in the browser but I just get a blank white page, when I get home later today i'll put some print statements in to test how far it is getting

It does hold a value above 0, ive set it to 120 to test it.

Thanks, Paul.

[Dismounted]

TIMENOW is the UNIX-style timestamp. You really don't need to assign it to a variable.

[PaulSonny]

Ah right just when I was looking at other examples it has been assigned to a variable.

So could I use instead:

$hc_timediff = intval(TIMENOW-$hc_time2);

Thanks, Paul.

[Opserty]

Throw in some var_dump()'s and echo()'s to check which is being executed it maybe that it isn't fetching stuff from the database. Blank pages are useless for debugging!

[PaulSonny]

What i dont understand is ive put a print statement at the very top and when I run the file it doesnt even print that line like it should.

Thanks, Paul.

[Opserty]

Good code layout is essential I can easily say that more organised code will gaurantee that you can troubshoot problems faster. Please, please, please layout code properly!

Here is how I would have done it (I've commented in lines I would try, to solve problems e.t.c.):

PHP Code:

<?php
// echo 'Starting file';
if ($vbulletin->options['helpcenter_auto_close'] > 0)
{
    // echo 'First condition passed';
    $ticketreply = $vbulletin->db->query_first("SELECT * FROM " . TABLE_PREFIX ."helpcenter_ticket");
    
    // echo 'query good';
    while ($ticket = $vbulletin->db->fetch_array($ticketreply))
    {    
         echo 'while good';
        
        $ticketid =& $ticket['ticketid'];
        $hc_time2 = $vbulletin->options['helpcenter_auto_close'];
        $hc_timediff = intval(TIMENOW - $hc_time2);
        
        // var_dump($ticketid, $hc_time1, $hc_time2, $hc_timediff, $ticket )
        if ($ticket['datelastupdate'] <= $hc_timediff)
        {
            $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX ."helpcenter_ticket SET ticketstatus = 0 WHERE ticketid = $ticketid");
        }
    }
}
?>

But whilst I was reorganising your code I found you were using [minicode]query_first()[/minicode] but you should be using [minicode]query_read()[/minicode] I suspect?