help with querying database

[k4pil]

Hi there people...

I have the following table in my database

activity (activityID, userID, time, page)

It keeps track of which page the users have visited and when, a bit like big brother i guess.

I have just finished a view activity script, where the user enters the name of a user and the script outputs the activity of the user specified.

However, if the user has been busy, this can be many (100's) of rows of data. I need to allow the user to search a specified date range, i.e. 15 January 2005 - 15 January 2006.

How can i do this???

The date is stored as;
Friday 24th of February 2006 05:24:39 PM

Any suggestion???

Regards pps

[k4pil]

Anything??
Anyone???

[JasperBosch]

Hi,

Is your time stored as a date or as a string?

If it's a string you hav a BFP. (The B stands for Big and the P for Problem)
But if it is stored as date or timestamp you can query on it. The where-statement should look like this:
[code]
WHERE time BETWEEN '2005-01-16' AND '2006-01-15'
[/code]

[webezine]

Hi i need some help on somethign liek this too! I need to do a search of a database from todays date for all events within the next 2 days. Any ideas?

[shocker-z]

could use this:

DATE_SUB(CURDATE(),INTERVAL 0 DAY) <= table_field;");


If that is what your after?

[k4pil]

Well the way the date is stored is;

$time = date('l dS \of F Y h:i:s A');

and $time is inserted into the database. Any suggestions??

[XenoPhage]

[!--quoteo(post=352578:date=Mar 7 2006, 02:25 PM:name=k4pil)--][div class=\'quotetop\']QUOTE(k4pil @ Mar 7 2006, 02:25 PM) [snapback]352578[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well the way the date is stored is;

$time = date('l dS \of F Y h:i:s A');

and $time is inserted into the database. Any suggestions??
[/quote]

Ugh.. re-factor and use the database specified types... There are a multitude of date/time types available in MySQL and they all have associated functions to allow for simpler searching.

[k4pil]

XenoPhage was totally right.

I used timestamp which is a datatype provided be mySql and everything is soooo much easier.

For the benefit of webezine il go over what i have.

Firstly a table called activity which lists the activity of users;
activity(activityID, userID, time, pageAccessed)

[code]
$query = "INSERT INTO activity VALUES ('', '$userID', NOW(), '$pageTitle')";
    $result = mysql_query($query);
[/code]

When i created the table in mySql, the syntax was something like

[code]
CREATE TABLE `activity` (
`activityID` INT( 11 ) NOT NULL ,
`userID` INT( 11 ) NOT NULL ,
`time` TIMESTAMP NOT NULL ,
`page` VARCHAR( 20 ) NOT NULL ,
UNIQUE (
`activityID`
)
) TYPE = MYISAM;
[/code]