'DatePosted' field defined as int(10)

[Bhaal]

Will this work?

I'd like a page that displays 'newest' members. This has to be pretty common, right?

There's a DatePosted field defined as int(10) that holds a numeric string - I'm assuming this is a Unix time stamp. (Sample data: 1141830780)

This DatePosted field records the date of registration.

I need to display members who have registered 45 days or less. Once they pass 45 days as a registered user, they'd just drop out of the recordset from the query.

So - will this work even though DatePosted is not defined as a DATE but as int?:

mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 45 DAY) <= DatePosted;

Thanks for any assistance...

[keeB]

[!--quoteo(post=357935:date=Mar 24 2006, 02:33 PM:name=Bhaal)--][div class=\'quotetop\']QUOTE(Bhaal @ Mar 24 2006, 02:33 PM) [snapback]357935[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Will this work?

I'd like a page that displays 'newest' members. This has to be pretty common, right?

There's a DatePosted field defined as int(10) that holds a numeric string - I'm assuming this is a Unix time stamp. (Sample data: 1141830780)

This DatePosted field records the date of registration.

I need to display members who have registered 45 days or less. Once they pass 45 days as a registered user, they'd just drop out of the recordset from the query.

So - will this work even though DatePosted is not defined as a DATE but as int?:

mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 45 DAY) <= DatePosted;

Thanks for any assistance...
[/quote]

So you know the syntax, but you haven't tried? Silly man.

I doubt that it will work, though.. Option 1 would be to preserve the data (like back the table up into a temp table) and then alter the table to date format, or Option 2 would be to write your own Unix Timestamp date function for the current day, and then one for 45 days ago, and construct a WHERE statement.

Either approach would be quite simple, but let me know if you need help ;)

[Bhaal]

Yep I need help...I feel like I'm lost on Query Island. I've tinkered quite a bit, but the raft keeps sinking. (And then Walt gets taken..."right from my own HANDS!!!!")

The original query is simple enough:

[code]
$q1 = " select * from listings, members where members.MemberID = listings.MemberID ";
[/code]

Then, there's a field that I need to filter the results down from called [DatePosted] - this field is defined as int(10).

I do know that the following snippet 'converts' [DatePosted] into an actual date:

[code]
$PostedDate = date('d-M-Y', $a1[DatePosted]);
[/code]

How would I take the results from $q1 and re-query them based on a 45-day range (from today) of '[DatePosted]' (or rather, '$PostedDate')?

[Bhaal]

My bump, my bump, my bump...my little query bump...check it out...

Maybe I need to restate the problem, since things can get confused:

I need to find records based on a date; basically, find all records within a 45-day range from 'today'.

A twist is that the 'date' field isn't a date datatype, it's int(10).

(This is inherited code with an existing data structure...as an aside, would changing the datatype from int to date screw up the code that writes to this field?)

Help appreciated!

[Bhaal]

One more bump...any help appreciated!

[Eddyon]

set your time limit as 45 days:

$limit_time = time() - 3888000;

and then in your query include this where clause:

WHERE UNIX_TIMESTAMP(dateposted) >= $limit_time

Try giving that a go, let us know how you get on.

[Bhaal]

Thanks for the reply, Eddyon...but the results of that where clause were blank. (No record in the test data is over 2 weeks old, so it should catch all of them, but it seems to have filtered out all of them.)