PHP: Is my method alright?

Question

I'm making a "newest users" page.

All users have an timestamp in the column "unixdate", that it stores when you register. Dont know if this is smart, but I like how effective the UNIX time() is and how simple it is.

Now I am showing the newest users within 24 hours by doing this:

$long = "86400"; // 24 hours in seconds
$query = "SELECT * FROM users WHERE unixdate > time()-$long ORDER BY unixdate DESC";

Is this an good method to show on? And if I would like to show newest within 3 days, would it be * 3?

Thank you

Answer 1

Your method looks perfectly fine. You might want to select just the fields you want to use, especially if you then access them as a non-associative array. If the database schema changes later, surprises will happen. Also make sure there's an index on the column unixdate of course.

Answer 2

The only thing i would change is the name/type of the variable.

$seconds_in_day = 86400;
$query = "SELECT * FROM users WHERE unixdate > time()-$seconds_in_day ORDER BY unixdate DESC";

Now when someone reads your code they will know exactly what that variable does. "long" didn't help at all. Also no point in making it a string, make it an integer.

If you were going to have multiple queries i would also change the query variable name. I also prefer sprintf, i think it makes code more readable as well

$seconds_in_day = 86400;
$query_new_members_1day = sprintf( "SELECT * FROM users WHERE unixdate > time()-%d ORDER BY unixdate DESC", $seconds_in_day );
$query_new_members_3days = sprintf( "SELECT * FROM users WHERE unixdate > time()-%d ORDER BY unixdate DESC", $seconds_in_day*3 );

Answer 3

It's quite all right. A few minor things:

• don't use SELECT *, only choose the columns that you want
• if you see performance problems, you may want to actually pass time()-$long as a constant (for more efficient query - TIME() gets re-evaluated for every row)
• index on unixdate might be useful
• UNIX time is undefined beyond the year 2038
• the query returns the newest users "in the last 24 hours", that could be different from "in the last day" (DST), but if you're aware of this distinction, it's not a big deal.

.

$yesterday = time() - 86400;  
$query = "SELECT somecolumn1,somecolumn2,othercolumn FROM users
                 WHERE unixdate > $yesterday ORDER BY unixdate DESC";

Answer 4

I also store date in Unix Timestamp format always. I find it a lot easy when working in PHP.

You should use

$long = time() - 86400;

I prefer

$long = time() - 259200;

over 3 * 86400 as it would save a few CPU cycle.

I would also add an index on unixdate field.