Programming Forums > Web Development and Design > PHP

k4pil · Mar 3rd, 2006, 3:55 PM

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

DaWei · Mar 3rd, 2006, 4:05 PM

Make sure that your date-time is stored in the DB in a manner that the DB construes as a date-time, then just build a query according to their rules.

guess · Mar 6th, 2006, 7:17 AM

I have just done that kind of script searching a database by means of day and many more.U must format ur text according to the information in the database or vice-versa.
No matter which programming language u use,there must be a date function which is getting date according to a given format.U can also find their formatting usage...

Arevos · Mar 6th, 2006, 8:29 AM

Quote:

Originally Posted by k4pil

How can i do this???

What database software are you using?

Ooble · Mar 6th, 2006, 11:51 AM

Quote:

Originally Posted by k4pil

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

Have you stored the date as a TEXT/VARCHAR or as a DATETIME/TIMESTAMP? The former will not allow comparisons; it's a string, nothing more. The latter will allow you to compare it to other dates in SQL. Most databases will be able to parse the following data:

(Toggle Plain Text)

2006-02-24 17:24:39

That's YYYY-MM-DD HH:mm:ss. Check your database - if the column is storing it as an actual date/time, not a string, you can compare it using the following SQL statement:

(Toggle Plain Text)

SELECT `time` FROM `activity` WHERE `time` >= '2005-01-15' AND `time` <= '2006-01-15' ORDER BY `time` ASC

To help more, we'll need to know what database software you're using.

k4pil · Mar 9th, 2006, 1:11 PM

I'v managed to fix it.

My stupid self had stored the date as a string - changed it to time stamp and some time later - its working fine now!

thanks!!

Ooble · Mar 9th, 2006, 5:01 PM

Congrats.

Mar 3rd, 2006, 3:55 PM	#1
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	Querying database - help 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

Mar 3rd, 2006, 4:05 PM	#2
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	Make sure that your date-time is stored in the DB in a manner that the DB construes as a date-time, then just build a query according to their rules. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Mar 9th, 2006, 5:01 PM	#7
Ooble I eat cake for breakfast. Join Date: Jul 2004 Location: In my box. Posts: 4,434 Rep Power: 9	Congrats. __________________ Me :: You :: Them

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2006, 7:17 AM	#3
guess Programmer Join Date: Feb 2006 Posts: 40 Rep Power: 0	I have just done that kind of script searching a database by means of day and many more.U must format ur text according to the information in the database or vice-versa. No matter which programming language u use,there must be a date function which is getting date according to a given format.U can also find their formatting usage...

Mar 9th, 2006, 1:11 PM	#6
k4pil Programmer Join Date: Aug 2005 Location: Leeds - UK Posts: 69 Rep Power: 4	I'v managed to fix it. My stupid self had stored the date as a string - changed it to time stamp and some time later - its working fine now! thanks!!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Web Development and Design > PHP

Querying database - help