Time not working

[Gubbins]

I cant seem to get the time to disply, am i doing something worong?

Please help!

 

<tr align="center">
    <td width="50%" height="104"><table width="50%" border="1" align="center" cellpadding="2" cellspacing="0" bordercolor="#000000" class="tbl">
      <tr>
        <td align="center" colspan="2" class="hdr">Total Active Players</td>
        </tr>
      <tr>
        <th width="160" align="left" class="background" scope="row">Past Hour:</th>
        <td class="background"><div align="center"><? print"".($pst1).""; ?></div></td>
      </tr>
      <tr>
        <th align="left" class="background" scope="row">Past 24 Hours:</th>
        <td class="background"><div align="center"><? print"".($pst2).""; ?></div></td>
      </tr>
      <tr>
        <th align="left" class="background" scope="row">Past Week: </th>
        <td class="background"><div align="center"><? print"".($pst3).""; ?></div></td>
      </tr>
      <tr>
        <th align="left" class="background" scope="row">Past Month:</th>
        <td class="background"><div align="center"><? print"".($pst4).""; ?></div></td>
      </tr>
    </table>
    </td>
<?php
$t = time();

$q = mysql_query("SELECT `playername` FROM `players` WHERE `active` > '$t' ORDER BY `rankpoints` DESC")or die(mysql_error());

while ($t = mysql_fetch_array($q)){

$pst1b=60*60;
$pst2b=3600*24;
$pst3b=$pst2b*7;
$pst4b=$pst3b*4;

$pst1a=$tim-$pst1b;
$pst2a=$tim-$pst2b;
$pst3a=$tim-$pst3b;
$pst4a=$tim-$pst4b;

$pst1=mysql_num_rows(mysql_query("SELECT id FROM players WHERE active>'$pst1a'"));
$pst2=mysql_num_rows(mysql_query("SELECT id FROM players WHERE active>'$pst2a'"));
$pst3=mysql_num_rows(mysql_query("SELECT id FROM players WHERE active>'$pst3a'"));
$pst4=mysql_num_rows(mysql_query("SELECT id FROM players WHERE active>'$pst4a'"));
}
?>

[Mchl]

What is the datatype for `active` field in `players` table?

[Gubbins]

What is the datatype for `active` field in `players` table?

 

`active` varchar(255) NOT NULL,

 

 

[Mchl]

`active` varchar(255) NOT NULL,

 

Not good... And how it is stored then?

[Gubbins]

Not good... And how it is stored then?

 

In seconds, but i think i need a new entry as 'active' is used by another file.

So what would you recommend?

[Mchl]

There are specific data types meant to store time.

These are DATE, DATETIME, TIME and TIMESTAMP. You should probably use either DATETIME or TIMESTAMP. Once it's done, the queries you need are pretty straightforward

 

-- one hour
SELECT id FROM players WHERE TIMEDIFF(NOW(),active) < 010000   
-- one day
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 1
-- one week   
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 7
-- four weeks
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 28

 

[Gubbins]

There are specific data types meant to store time.

These are DATE, DATETIME, TIME and TIMESTAMP. You should probably use either DATETIME or TIMESTAMP. Once it's done, the queries you need are pretty straightforward

 

-- one hour
SELECT id FROM players WHERE TIMEDIFF(NOW(),active) < 010000   
-- one day
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 1
-- one week   
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 7
-- four weeks
SELECT id FROM players WHERE DATEDIFF(NOW(),active) < 28

 

I will give that a go, thank you.

Should the entry still be in:-

 

`TIMEDIFF` varchar(255) NOT NULL,

`DATEDIFF` varchar(255) NOT NULL,

[Gubbins]

I will give that a go, thank you.

Should the entry still be in:-

 

`TIMEDIFF` varchar(255) NOT NULL,

`DATEDIFF` varchar(255) NOT NULL,

 

and how would i tie it into my original code?

[Mchl]

No. TIMEDIFF and DATEDIFF are MySQL date time functions

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 

They are used with date and time datatypes

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html