Re: [PHP] select based on time/date
The reason my query didn't work is you have time_in as a varchar. Change it to a datetime field or timestamp field and it should work. I would suggest making it a timestamp field. That way whenever you update the row (probably update the session field with a new session) MySQL will automatically set time_in to Now(). This will avoid a lot of headache for you =) Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: Sheridan Saint-Michel [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 5:08 PM Subject: RE: [PHP] select based on time/date I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); Thanks again. __ Jason Dulberg Extreme MTB http://extreme.nas.net The problem with this is curtime returns a number in HHMMSS format and your extract returns MM so you get HHMMSS - MM which is always going to be greater than 60 unless you are running your script in the first minute of the day (ie 37 - 49). Try this instead: select * from sessions where $timein + interval 1 hour = now(); - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 2:35 PM Subject: RE: [PHP] select based on time/date I had a look at the DATE_FORMAT info on the mysql doc page and its a bit easier to understand than the CONCATS. Would I want to use the CURTIME() function since I would want to select sessions based on the hour? How would I go about combining CURTIME with the rest of my query? Theoretically, here is what I understand, please let me know whether or not I am correct: select * from sessions where (CURTIME() - EXTRACT(MINUTE FROM $timein) = 60); //where 60 is the lifespan of the session -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] select based on time/date
date_add(time_in,interval 1 hour) is the same as time_in + interval 1 hour as of MySQL 3.23 so unless you are using 3.22 you should get the same result (anything prior to 3.22 won't support date_add) Neither query is going to work, however, because time_in is a varchar rather than one of the date types. Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 6:24 PM Subject: RE: [PHP] select based on time/date I plugged your query in and it didn't result in an error - however, it came up with no results even though there should be. This one should have come up but didn't: INSERT INTO logged_in VALUES ( '5', 'b406e68a7cde49534a14f5fd8848006e', 'September 24, 2001, 3:27 pm', ''); -- the fields correspond to the table structure quoted below Thanks again! __ Jason Dulberg Extreme MTB http://extreme.nas.net -Original Message- From: Jack Dempsey [mailto:[EMAIL PROTECTED]] Sent: September 25, 2001 7:07 PM To: 'Jason Dulberg'; 'Sheridan Saint-Michel'; [EMAIL PROTECTED] Subject: RE: [PHP] select based on time/date select * from logged_in where date_add(time_in,interval 1 hour) = now() -jack -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 25, 2001 6:08 PM To: Sheridan Saint-Michel; [EMAIL PROTECTED] Subject: RE: [PHP] select based on time/date I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
You were right! I changed it to a timestamp (14) and the selection is working great now. Only problem is that when I logout which UPDATE's the table to clear the $session field, the $time_in timestamp gets updated because of the NOW() function. I need that date to stay the same when someone logs out. current logout.php: $out = UPDATE logged_in SET session='' WHERE (logged_in.userid='$aid') AND (logged_in.session='$sessid'); Is there a way to only allow the NOW() to work when a row is added to the table? Or perhaps another way Thank you again for your time. I've learned alot from this. __ Jason Dulberg Extreme MTB http://extreme.nas.net -Original Message- From: Sheridan Saint-Michel [mailto:[EMAIL PROTECTED]] Sent: September 26, 2001 9:38 AM To: Jason Dulberg; [EMAIL PROTECTED] Subject: Re: [PHP] select based on time/date The reason my query didn't work is you have time_in as a varchar. Change it to a datetime field or timestamp field and it should work. I would suggest making it a timestamp field. That way whenever you update the row (probably update the session field with a new session) MySQL will automatically set time_in to Now(). This will avoid a lot of headache for you =) Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: Sheridan Saint-Michel [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 5:08 PM Subject: RE: [PHP] select based on time/date I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); Thanks again. __ Jason Dulberg Extreme MTB http://extreme.nas.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
Awesome... Its all working perfectly now! Makes perfect sense why the update is like that. thanks again! __ Jason Dulberg Extreme MTB http://extreme.nas.net -Original Message- From: Sheridan Saint-Michel [mailto:[EMAIL PROTECTED]] Sent: September 26, 2001 1:59 PM To: Jason Dulberg; [EMAIL PROTECTED] Subject: Re: [PHP] select based on time/date Timestamp only sets itself to Now() if it isn't explicitly set (or set to NULL). So try this: $out = UPDATE logged_in SET session='',time_in=time_in WHERE (logged_in.userid='$aid') AND (logged_in.session='$sessid'); Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: Sheridan Saint-Michel [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 26, 2001 12:44 PM Subject: RE: [PHP] select based on time/date You were right! I changed it to a timestamp (14) and the selection is working great now. Only problem is that when I logout which UPDATE's the table to clear the $session field, the $time_in timestamp gets updated because of the NOW() function. I need that date to stay the same when someone logs out. current logout.php: $out = UPDATE logged_in SET session='' WHERE (logged_in.userid='$aid') AND (logged_in.session='$sessid'); Is there a way to only allow the NOW() to work when a row is added to the table? Or perhaps another way Thank you again for your time. I've learned alot from this. __ Jason Dulberg Extreme MTB http://extreme.nas.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] select based on time/date
On Tue, 25 Sep 2001 13:38, Maxim Maletsky \(PHPBeginner.com\) wrote: Don't do it with PHP. Instead use the native functions of mySQL itself. Use the combination of CONCAT(), some string and date functions of mySQL. Something like CONCAT(SUBSTRING(), SUBSTRING(), ...) so you end up in the right time format i.e.: -00-00. With that string you should be able to do time comparisons within mySQL database. In other words, go to mySQL documentation and read there. mySQL will do it way better and faster than PHP. Cheers, Maxim Maletsky PHPBeginner.com -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: martedì 25 settembre 2001 5.47 To: [EMAIL PROTECTED] Subject: [PHP] select based on time/date I am using sessions on my site and have noticed that people often don't click the logout button -- something which I need them to do in order to clear their session from the db and update some site stats. In the sessions table, I have a field $timein (in format F j, Y, g:i a) that gets added when the user logs in and a $timeout field that gets added when they hit logout. Basically, what I've been thinking of doing is using cron to run a php script every hour or so that will search the table for sessions with no $timeout field and $timein is more than X number of minutes old. My problem is that since I'm using the F j, Y, g:i a date/time format, how would I make the mysql select? Do I have to explode the $timein field? Any suggestions are appreciated. __ Jason Dulberg Extreme MTB http://extreme.nas.net And, as someone recently pointed out in response to a similar suggestion I made, there is a mysql DATE_FORMAT function that will do the same thing without the CONCATS. -- David Robley Techno-JoaT, Web Maintainer, Mail List Admin, etc CENTRE FOR INJURY STUDIES Flinders University, SOUTH AUSTRALIA For every vision there is an equal and opposite revision. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
I had a look at the DATE_FORMAT info on the mysql doc page and its a bit easier to understand than the CONCATS. Would I want to use the CURTIME() function since I would want to select sessions based on the hour? How would I go about combining CURTIME with the rest of my query? Theoretically, here is what I understand, please let me know whether or not I am correct: select * from sessions where (CURTIME() - EXTRACT(MINUTE FROM $timein) = 60); //where 60 is the lifespan of the session Thanks for your suggestions and time! __ Jason Dulberg Extreme MTB http://extreme.nas.net And, as someone recently pointed out in response to a similar suggestion I made, there is a mysql DATE_FORMAT function that will do the same thing without the CONCATS. -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: martedì 25 settembre 2001 5.47 To: [EMAIL PROTECTED] Subject: [PHP] select based on time/date I am using sessions on my site and have noticed that people often don't click the logout button -- something which I need them to do in order to clear their session from the db and update some site stats. In the sessions table, I have a field $timein (in format F j, Y, g:i a) that gets added when the user logs in and a $timeout field that gets added when they hit logout. Basically, what I've been thinking of doing is using cron to run a php script every hour or so that will search the table for sessions with no $timeout field and $timein is more than X number of minutes old. My problem is that since I'm using the F j, Y, g:i a date/time format, how would I make the mysql select? Do I have to explode the $timein field? Any suggestions are appreciated. __ Jason Dulberg Extreme MTB http://extreme.nas.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] select based on time/date
- Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 2:35 PM Subject: RE: [PHP] select based on time/date I had a look at the DATE_FORMAT info on the mysql doc page and its a bit easier to understand than the CONCATS. Would I want to use the CURTIME() function since I would want to select sessions based on the hour? How would I go about combining CURTIME with the rest of my query? Theoretically, here is what I understand, please let me know whether or not I am correct: select * from sessions where (CURTIME() - EXTRACT(MINUTE FROM $timein) = 60); //where 60 is the lifespan of the session The problem with this is curtime returns a number in HHMMSS format and your extract returns MM so you get HHMMSS - MM which is always going to be greater than 60 unless you are running your script in the first minute of the day (ie 37 - 49). Try this instead: select * from sessions where $timein + interval 1 hour = now(); Thanks for your suggestions and time! __ Jason Dulberg Extreme MTB http://extreme.nas.net No Problem... hope that helps Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); Thanks again. __ Jason Dulberg Extreme MTB http://extreme.nas.net The problem with this is curtime returns a number in HHMMSS format and your extract returns MM so you get HHMMSS - MM which is always going to be greater than 60 unless you are running your script in the first minute of the day (ie 37 - 49). Try this instead: select * from sessions where $timein + interval 1 hour = now(); - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 2:35 PM Subject: RE: [PHP] select based on time/date I had a look at the DATE_FORMAT info on the mysql doc page and its a bit easier to understand than the CONCATS. Would I want to use the CURTIME() function since I would want to select sessions based on the hour? How would I go about combining CURTIME with the rest of my query? Theoretically, here is what I understand, please let me know whether or not I am correct: select * from sessions where (CURTIME() - EXTRACT(MINUTE FROM $timein) = 60); //where 60 is the lifespan of the session -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
select * from logged_in where date_add(time_in,interval 1 hour) = now() -jack -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 25, 2001 6:08 PM To: Sheridan Saint-Michel; [EMAIL PROTECTED] Subject: RE: [PHP] select based on time/date I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); Thanks again. __ Jason Dulberg Extreme MTB http://extreme.nas.net The problem with this is curtime returns a number in HHMMSS format and your extract returns MM so you get HHMMSS - MM which is always going to be greater than 60 unless you are running your script in the first minute of the day (ie 37 - 49). Try this instead: select * from sessions where $timein + interval 1 hour = now(); - Original Message - From: Jason Dulberg [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 25, 2001 2:35 PM Subject: RE: [PHP] select based on time/date I had a look at the DATE_FORMAT info on the mysql doc page and its a bit easier to understand than the CONCATS. Would I want to use the CURTIME() function since I would want to select sessions based on the hour? How would I go about combining CURTIME with the rest of my query? Theoretically, here is what I understand, please let me know whether or not I am correct: select * from sessions where (CURTIME() - EXTRACT(MINUTE FROM $timein) = 60); //where 60 is the lifespan of the session -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
I plugged your query in and it didn't result in an error - however, it came up with no results even though there should be. This one should have come up but didn't: INSERT INTO logged_in VALUES ( '5', 'b406e68a7cde49534a14f5fd8848006e', 'September 24, 2001, 3:27 pm', ''); -- the fields correspond to the table structure quoted below Thanks again! __ Jason Dulberg Extreme MTB http://extreme.nas.net -Original Message- From: Jack Dempsey [mailto:[EMAIL PROTECTED]] Sent: September 25, 2001 7:07 PM To: 'Jason Dulberg'; 'Sheridan Saint-Michel'; [EMAIL PROTECTED] Subject: RE: [PHP] select based on time/date select * from logged_in where date_add(time_in,interval 1 hour) = now() -jack -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 25, 2001 6:08 PM To: Sheridan Saint-Michel; [EMAIL PROTECTED] Subject: RE: [PHP] select based on time/date I tried basically what you have but I got an error: $sql=select * from logged_in where time_in + interval 1 hour = now(); $result = mysql_query($sql); If it will help, here's the table structure -- looks like my structure is bit different from what I orignally posted: CREATE TABLE logged_in ( id tinyint(4) DEFAULT '0' NOT NULL auto_increment, session varchar(100) DEFAULT '0' NOT NULL, time_in varchar(50) NOT NULL, KEY id (id) ); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] select based on time/date
Don't do it with PHP. Instead use the native functions of mySQL itself. Use the combination of CONCAT(), some string and date functions of mySQL. Something like CONCAT(SUBSTRING(), SUBSTRING(), ...) so you end up in the right time format i.e.: -00-00. With that string you should be able to do time comparisons within mySQL database. In other words, go to mySQL documentation and read there. mySQL will do it way better and faster than PHP. Cheers, Maxim Maletsky PHPBeginner.com -Original Message- From: Jason Dulberg [mailto:[EMAIL PROTECTED]] Sent: martedì 25 settembre 2001 5.47 To: [EMAIL PROTECTED] Subject: [PHP] select based on time/date I am using sessions on my site and have noticed that people often don't click the logout button -- something which I need them to do in order to clear their session from the db and update some site stats. In the sessions table, I have a field $timein (in format F j, Y, g:i a) that gets added when the user logs in and a $timeout field that gets added when they hit logout. Basically, what I've been thinking of doing is using cron to run a php script every hour or so that will search the table for sessions with no $timeout field and $timein is more than X number of minutes old. My problem is that since I'm using the F j, Y, g:i a date/time format, how would I make the mysql select? Do I have to explode the $timein field? Any suggestions are appreciated. __ Jason Dulberg Extreme MTB http://extreme.nas.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
