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
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.
$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
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
// 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 query_first() but you should be using query_read() I suspect?

[Medina]

If we started about time, how can I set time above the header. But not the javascript one, what`s installed on the time off your pc. But the time in the header, that`s refreshing the time if you refresh your page or moving to a thread or something like that.

[PaulSonny]

Just to confirm, I have just copied and pasted the code I have into the original file I have were I have my main script and it worked no problem.

But when I take it out and put it in a seperate .php file to link to a cron job it wont work so I dont understand why.

When I try and echo first condition it doesnt show up but it works on another page.

Thanks, Paul.

[Medina]

Sorry duplicate!