Jump to content


Photo

help with querying database


  • Please log in to reply
7 replies to this topic

#1 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 03 March 2006 - 02:36 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


#2 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 03 March 2006 - 05:34 PM

Anything??
Anyone???

#3 JasperBosch

JasperBosch
  • New Members
  • Pip
  • Newbie
  • 8 posts
  • LocationApeldoorn, The Netherlands

Posted 05 March 2006 - 08:08 PM

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:
WHERE time BETWEEN '2005-01-16' AND '2006-01-15'


#4 webezine

webezine
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 06 March 2006 - 02:41 AM

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?

#5 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 06 March 2006 - 11:23 AM

could use this:

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


If that is what your after?
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#6 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 07 March 2006 - 07:25 PM

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??

#7 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 07 March 2006 - 07:41 PM

[!--quoteo(post=352578:date=Mar 7 2006, 02:25 PM:name=k4pil)--][div class=\'quotetop\']QUOTE(k4pil @ Mar 7 2006, 02:25 PM) View Post[/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.
--
[a href=\"http://blog.godshell.com\" target=\"_blank\"]XenoPhage[/a]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Something mysterious is formed, born in the silent void. Waiting alone and unmoving, it is at once still and yet in constant motion. It is the source of all programs. I do not know its name, so I will call it the Tao of Programming.[/quote]

#8 k4pil

k4pil
  • Members
  • PipPip
  • Member
  • 23 posts

Posted 07 March 2006 - 09:43 PM

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)

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

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

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;





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users