Re: [PHP-DB] Dates - Best Approach
If you are using unix-like OSes then you could write a program or a script using (PHP, perl, bash, csh even C) whatever it suits you and use cron daemon to run it every month. This script should query the database and find out if is needed to be sent. The alternative (not the suggested solution for this specific task) is to write a daemon process and configure it to run using an interval like every 15 days or a week or whatever you need. In case you use windowz (not helping yourself very much) then perhaps the task scheduler could be of use to help you do something similar. I have very little experience of that kind of masochism so I 'll let someone else to do this dirty work :-) . Malcolm Clark wrote: Hi! I am designing a contact management system, so that entries (client records) can be flagged, so that an email is sent out after 'n' months, to remind the user to contact this particular client. So, there would simply be a drop down menu with options: None, 1 month, 2 month, ... 12 months etc I also imagine that when the client record is viewed, it states how long is left until the client is to be contacted. eg "36 days" or "approx 3 weeks" My question? Are there any classes or packages that might help me with this task. Maybe somebody has got some other ideas? Kind Regards, Malcolm Clark -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates prior to Dec 31, 1969
Ramil and Jochem, Having mySQL format the date is the best solution... thanks! Frank -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates prior to Dec 31, 1969
Frank Marousek wrote: I'm using the following code to display dates returned from a query of a mySQL database. What kind of field is it? (int,timestamp,datetime,etc) $new_timestamp = strtotime($row_SearchPlayerRcrdSt['Date']); what do the following contain: $new_timestamp $row_SearchPlayerRcrdSt['Date'] you also might want to try connecting to the DB with the cmdline mysql app to take a look at the date values actually stored in the DB. $new_date_formatted = date ("m/d/y", $new_timestamp); echo $new_date_formatted; It appears that all dates prior to Dec 31, 1969 are displayed as 12/31/69. What am I doing wrong? Sounds like you have yet to come across the 'unix epoch' (try googling that). something to do with this looks to be tripping you up. Either the date in the DB is invalid (eg. equal to 0) or strtotime or date are truncating the date value in your version of php. (what version of php & mysql?) Also notice that you are taking a formatted date string, parsing it for a timestamp and then creating a new formatted date string. Why not let mySQL just format it how you want straight away and save 2 function calls? Thanks, Frank -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates prior to Dec 31, 1969
On Tue, 14 Dec 2004 15:39:52 -0600, Frank Marousek <[EMAIL PROTECTED]> wrote: > I'm using the following code to display dates returned from a query of a > mySQL database. > > $new_timestamp = strtotime($row_SearchPlayerRcrdSt['Date']); > $new_date_formatted = date ("m/d/y", $new_timestamp); > echo $new_date_formatted; > > It appears that all dates prior to Dec 31, 1969 are displayed as 12/31/69. > > What am I doing wrong? > Thanks, > Frank > On my system it displays at 01/01/70. This may be related to that note in the strtotime page of the php manual: http://ww.php.net/manual/en/function.strtotime.php Note: The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer.) Additionally, not all platforms support negative timestamps, therefore your date range may be limited to no earlier than the Unix epoch. This means that e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux distributions, and a few other operating systems. How about letting MySQL format the date for you? http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html#IDX1384 ramil http://ramil.sagum.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
The code that seems to work eventually is: $thisdate=date("Y-m-d",$thisdate); // does not work where the delimiter is / rather than -, I thought various delimiters were allowed in the relaxed mysql code! $query="SELECT WHERE..arrival<='$thisdate'&& DATE_ADD(arrival,INTERVAL nights-1 DAY) >'$thisdate'"; INTERVAL means what it says, so from 1/1/04 add an interval of 1 gives 3/1/04, not 2/1/04. Hence my -1. John - Original Message - From: "ioannes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 02, 2004 7:42 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday The answer seems to be DATE_ADD but I haven't got it to work yet...asking the MySQL people. I need to do this in a SQL query: (arrival+nights)>='$thisdate' -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Quoting ioannes <[EMAIL PROTECTED]>: > The answer seems to be DATE_ADD but I haven't got it to work yet...asking > the MySQL people. Check your version of MySQL... I think that's a newer function. -Ed This message was sent using IMP, the Internet Messaging Program. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
The answer seems to be DATE_ADD but I haven't got it to work yet...asking the MySQL people. I need to do this in a SQL query: (arrival+nights)>='$thisdate' -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Sorry to visit this once again, but this is a problem with bits the solution to which causes other problems. I need to do this in a SQL query: (arrival+nights)>='$thisdate' 'arrival' is a date field, and 'nights' is an integer, so the above makes no sense, being like 2004/12/31+7 which is probably 00/00/00. However, if I use UNIX_TIMESTAMP(arrival)+nights*86400 I get the Summer time errors noted previously - if arrival is before one of the cross over dates (30 Oct and ?) and the departure (arrival+nights) is after. Adding 86400*nights will give a time which is an hour incorrect and maybe the wrong date. So how do I do a SQL query where I add a number of days to a date and avoid DST errors? Is there a way of forcing SQL to use GMT and not DST? Do I have to extract all the values and do it in php? Thanks, John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Final problem on this was adding a number of nights to an arrival date in a query: $query="SELECT field FROM Table WHERE (UNIX_TIMESTAMP(arrival)+nights*86400-86400+3600)>='$thisdate')"; When the arrival date is in Summer, adding a number of night requires adjusting for the hour [3600], because the UNIX_TIMESTAMP is DST whereas I had adjusted $thisdate to GMT to be free of time saving complications. When the arrival date is in Winter, I don't need to add the hour. I added a condition like: "&& arrival<'2004/10/31'..." to differentiate. I realise now that I have to think about what if the Winter arrival date plus the nights brings the total back to Summer. ..seeing as I did not know the mysql expression for GMT timestamps, which would be easier. Thanks to all. John -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
I'd still like to find out if there is a mysql equivalent of gmmktime for use in queries like: "SELECT UNIX_TIMESTAMP(mydate) FROM MyTable WHERE UNIX_TIMESTAMP(mydate)<='$thisdate'"; and $thisdate is a gm date. Otherwise I need to convert the GMT date back to Summer time just for the query. Is there a function for that? There is, so I found. As John Holmes suggested, the use of date("I",$unixdate) was in order. $thisdate=$unixdate-3600*(date("I",$unixdate)); converts $thisdate to a unix number that will give the right date in a Summer time context - for use in the query. date("I") gives 1 where the $unixdate is in Summer time. The expression deducts an hour where the date would be in Summer time. So, I suppose 1am on 30 Oct becomes midnight on 30 October when evaluated as a date. And so it does, below. http://uk2.php.net/manual/en/function.gmdate.php $unixdate=mktime(1,0,0,10,30,2004); //1am on 30 Oct 2004 $thisdate=$unixdate-3600*(date("I",$unixdate)); $conv_date=date("l dS of F Y h:i:s A",$thisdate); print("$conv_date"); //Saturday 30th of October 2004 12:00:00 AM =>Summer time as 1 hour deducted Good. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Of course, it's because the whole of Summer is an hour out and this changes 31 Oct. I had it backwards. I'd still like to find out if there is a mysql equivalent of gmmktime for use in queries like: "SELECT UNIX_TIMESTAMP(mydate) FROM MyTable WHERE UNIX_TIMESTAMP(mydate)<='$thisdate'"; and $thisdate is a gm date. Otherwise I need to convert the GMT date back to Summer time just for the query. Is there a function for that? John Using the MySQL UNIX_TIMESTAMP() seemed to give the wrong date, 08 10 2004. I did not find the gmdate equivalent in mysql.com. Conclusion: Using gmmktime and gmdate seems to work and is needed where intervals are to be calculated and number of days added to dates. Query: why the above SQL gave the wrong date with mktime when the date was 9 October -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
As Simon suggests, gmdate and gmmktime gives the right answer when: - computing difference between two dates in a MySQL table eg 15/10/2004 and 15/11/2004 - should be 31 - adding a number of days to the start date to get the end date, eg 15/10/2004 + 31 - should be 15/11/2004 $start=gmmktime(0,0,0,10,15,2004); $end=gmmktime(0,0,0,11,15,2004); $gm_interval=($end-$start)/86400; print("Interval computes $gm_interval"); //gives 31 right $end_add=$start+31*86400; //add 31 days of seconds $end_add_gm_interval=gmdate("d m Y",$end_add); print("Add 31 days computes $end_add_gm_interval"); //15 11 2004 right //whereas date() will fail on the DST issue $start=mktime(0,0,0,10,15,2004); $end=mktime(0,0,0,11,15,2004); $date_interval=($end-$start)/86400; print("date()"); print("Interval computes $date_interval"); //31.041667 wrong $end_add=$start+31*86400; //add 31 days of seconds $end_add_date_interval=date("d m Y",$end_add); print("Add 31 days computes $end_add_date_interval"); //14 11 2004 wrong However, in the following queries, mktime gives 08 10 2004 whereas the date is 09 10 2004. gmmktime gives the right date. There is no 08 10 2004 in the table. This seems to be a different issue from daylight savings as it is not 31 October when the hour changes. $query = "SELECT mydates FROM MyTable ORDER BY mydates"; $result=mysql_db_query($db,$query,$connection) or die("$query failed: ".mysql_error()); $tbl_dates=array(); $x=0; while(list($a)=mysql_fetch_array($result)){ $temp_day=substr($a,8,2);//parse table date $temp_mth=substr($a,5,2); $temp_year=substr($a,0,4); $tbl_dates[$x]=mktime(0,0,0,$temp_mth,$temp_day,$temp_year); //wrong - gmmktime gives right date $x++; } Using the MySQL UNIX_TIMESTAMP() seemed to give the wrong date, 08 10 2004. I did not find the gmdate equivalent in mysql.com. Conclusion: Using gmmktime and gmdate seems to work and is needed where intervals are to be calculated and number of days added to dates. Query: why the above SQL gave the wrong date with mktime when the date was 9 October Thanks, John - Original Message - From: "Simon Rees" <[EMAIL PROTECTED]> To: "ioannes" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 3:16 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday Isn't this due to how your operating system handles the switch from daylight saving time to GMT (or local equivilent)? Or is this handled by PHP? That could account for different experiences. Of course if your locale doesn't switch from DST on this date you won't see the issue either... Using gmdate doesn't cause this anomaly. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Isn't this due to how your operating system handles the switch from daylight saving time to GMT (or local equivilent)? Or is this handled by PHP? That could account for different experiences. Of course if your locale doesn't switch from DST on this date you won't see the issue either... Using gmdate doesn't cause this anomaly. Apologies if this is what you're discussing and the issue is when the DST switch takes place... cheers Simon -- Simon Rees | -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
From: "Craig Brothers" <[EMAIL PROTECTED]> I think your problem might be due to daylight savings time. 10/31/04 is the day that the clocks get set back 1 hour (at 2am). therefore we have 25 hours in the day on 10/31. not sure how to get around it though, or why my system accounts for it. You're just in a different time zone, more than likely. When I ran the code it went from 1800 on the 30th to 1700 on the 31st. Still "appearing" to be 23 hours, yet it's really 24 because of DST. The times you'll run into trouble (like the OP) is when the first time starts from - 0059 on 31 Oct. Then, adding 24 hours still leaves you in the same day. There is an "I" tag in date() that'll return a 1 or 0 if it's DST and a flag in mktime() to account for DST, also. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Magnificent. - Original Message - From: "John Holmes" <[EMAIL PROTECTED]> To: "ioannes" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 3:10 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday From: "ioannes" <[EMAIL PROTECTED]> Sunday 31st of October 2004 12:00:00 AM unix: 1099177200 - first day Saturday 30th of October 2004 12:00:00 AM unix: 1099090800 - previous day Sunday 31st of October 2004 11:00:00 PM unix: 1099263600 - day after first day if I add 86400 to first - 1099177200 - I get 1099177200+86400=1099263600 [day after first day above], but this gives me 11pm on 31 Oct as above. Daylight Savings Time strikes again!!! http://webexhibits.org/daylightsaving/b.html ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
According to www.greenwichmeantime.com/info/noon.htm 12:00:00 AM has no meaning as PM starts immediately after noon midday. John Think infinity, and you will be approximately right. - Original Message - From: "ioannes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 2:53 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday Sunday 31st of October 2004 12:00:00 AM unix: 1099177200 - first day Saturday 30th of October 2004 12:00:00 AM unix: 1099090800 - previous day Sunday 31st of October 2004 11:00:00 PM unix: 1099263600 - day after first day if I add 86400 to first - 1099177200 - I get 1099177200+86400=1099263600 [day after first day above], but this gives me 11pm on 31 Oct as above. ? - Original Message - From: "John Holmes" <[EMAIL PROTECTED]> To: "ioannes" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 2:31 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday From: "ioannes" <[EMAIL PROTECTED]> When I run this code I get: 31 10 2004 31 10 2004 http://www.shortstay-london.com/testdates.php Try printing out the hours, minutes and seconds along with the date and you'll see the issue. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
I think your problem might be due to daylight savings time. 10/31/04 is the day that the clocks get set back 1 hour (at 2am). therefore we have 25 hours in the day on 10/31. not sure how to get around it though, or why my system accounts for it. -- Craig Brothers [EMAIL PROTECTED] On Wed, 2004-09-01 at 09:53, ioannes wrote: > Sunday 31st of October 2004 12:00:00 AM unix: 1099177200 - first day > Saturday 30th of October 2004 12:00:00 AM unix: 1099090800 - previous day > Sunday 31st of October 2004 11:00:00 PM unix: 1099263600 - day after first > day > > if I add 86400 to first - 1099177200 - I get 1099177200+86400=1099263600 > [day after first day above], but this gives me 11pm on 31 Oct as above. > > ? > > > - Original Message - > From: "John Holmes" <[EMAIL PROTECTED]> > To: "ioannes" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, September 01, 2004 2:31 PM > Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday > > > > From: "ioannes" <[EMAIL PROTECTED]> > > > >> When I run this code I get: > >> > >> 31 10 2004 > >> 31 10 2004 > >> > >> http://www.shortstay-london.com/testdates.php > > > > Try printing out the hours, minutes and seconds along with the date and > > you'll see the issue. > > > > ---John Holmes... > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
From: "ioannes" <[EMAIL PROTECTED]> Sunday 31st of October 2004 12:00:00 AM unix: 1099177200 - first day Saturday 30th of October 2004 12:00:00 AM unix: 1099090800 - previous day Sunday 31st of October 2004 11:00:00 PM unix: 1099263600 - day after first day if I add 86400 to first - 1099177200 - I get 1099177200+86400=1099263600 [day after first day above], but this gives me 11pm on 31 Oct as above. Daylight Savings Time strikes again!!! http://webexhibits.org/daylightsaving/b.html ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
Sunday 31st of October 2004 12:00:00 AM unix: 1099177200 - first day Saturday 30th of October 2004 12:00:00 AM unix: 1099090800 - previous day Sunday 31st of October 2004 11:00:00 PM unix: 1099263600 - day after first day if I add 86400 to first - 1099177200 - I get 1099177200+86400=1099263600 [day after first day above], but this gives me 11pm on 31 Oct as above. ? - Original Message - From: "John Holmes" <[EMAIL PROTECTED]> To: "ioannes" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 2:31 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday From: "ioannes" <[EMAIL PROTECTED]> When I run this code I get: 31 10 2004 31 10 2004 http://www.shortstay-london.com/testdates.php Try printing out the hours, minutes and seconds along with the date and you'll see the issue. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
From: "ioannes" <[EMAIL PROTECTED]> When I run this code I get: 31 10 2004 31 10 2004 http://www.shortstay-london.com/testdates.php Try printing out the hours, minutes and seconds along with the date and you'll see the issue. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday
When I run this code I get: 31 10 2004 31 10 2004 http://www.shortstay-london.com/testdates.php - Original Message - From: "Craig Brothers" <[EMAIL PROTECTED]> To: "ioannes" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, September 01, 2004 2:00 PM Subject: Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal nextday what are you seeing for output? When I run your code I get the following 30 10 2004 31 10 2004 running on Fedora Core 1, Apache 2.0.5, PHP 5.0.1 -- Craig Brothers [EMAIL PROTECTED] On Wed, 2004-09-01 at 08:49, ioannes wrote: Code: "); $thisdate=date("d m Y",$next); print($thisdate); ?> how come? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal next day
I don't get you... your code outputs: "30 10 200431 10 2004" so, y added..: $next+=(60*60*24); print(""); $thisdate=date("d m Y",$next); print($thisdate); and the output is: "30 10 200431 10 200401 11 2004" nice Ref: Server: Apache/1.3.26 (Unix) PHP/4.3.8 On Wed, 1 Sep 2004 13:49:33 +0100, ioannes <[EMAIL PROTECTED]> wrote: > Code: > > //unix date is 1099177200 seconds since 01/01/1970 > //date based on unix 31 10 2004 > $thisdate=date("d m Y",1099177200); > print($thisdate); > $next=1099177200+60*60*24; //86400 > //add 86400 should give next unix date > //date based on this is also 31 10 2004 > print(""); > $thisdate=date("d m Y",$next); > print($thisdate); > ?> > > how come? > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Pablo M. Rivas. http://pmrivas.ipupdater.com --- [EMAIL PROTECTED] -- [EMAIL PROTECTED] -- [EMAIL PROTECTED] -- ICQ:1668030 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal next day
what are you seeing for output? When I run your code I get the following 30 10 2004 31 10 2004 running on Fedora Core 1, Apache 2.0.5, PHP 5.0.1 -- Craig Brothers [EMAIL PROTECTED] On Wed, 2004-09-01 at 08:49, ioannes wrote: > Code: > > //unix date is 1099177200 seconds since 01/01/1970 > //date based on unix 31 10 2004 > $thisdate=date("d m Y",1099177200); > print($thisdate); > $next=1099177200+60*60*24; //86400 > //add 86400 should give next unix date > //date based on this is also 31 10 2004 > print(""); > $thisdate=date("d m Y",$next); > print($thisdate); > ?> > > how come? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates - adding to unix 86400 seconds not equal next day
From: "ioannes" <[EMAIL PROTECTED]> "); $thisdate=date("d m Y",$next); print($thisdate); ?> I get 30 10 2004 31 10 2004 So what's the issue? You could also use this: print(date('d m Y H:i:s')); print(''); print(date('d m Y H:i:s',strtotime('+1 day'))); ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Dates and Count
Hi, $query="select * from clients where date between $date1 AND $date2"; zareef ahmed --- Shannon Doyle <[EMAIL PROTECTED]> wrote: > > Hi Pablo, > > Its exactly what I wanted. Thanks. > > I now have an additional question. > > I need to show results that appear between two dates > as specified by the > site visitor. > > At the moment I am using the following SQL: > > WHERE NextContact >= '$today' && NextContact <= > '$week'"; > > Second I need the same scenario, however I want to > search from a variety of > different fields, show those that are between the > dates and are equal to one > other criteria. > > Any help on these two? > > Cheers, > > Shannon > > > -Original Message----- > From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] > > Sent: Monday, 28 June 2004 9:21 AM > To: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Dates and Count > > Hello Shannon, > > > SD> First, I have two dates stored in a MySQL DB > that I want to do a compare > on > SD> and then only display via a web page those > records that have a 5 or > greater > SD> day difference in them. What would be the best > way to achieve this. > Depends on your mysql version... > select * from mytable where date1 + INTERVAL 5 > DAY<=date2 or date2 + > INTERVAL 5 DAY <=date1; > > select * from mytable where > to_days(date1)-todays(date2)>5 or > to_days(date2)-todays(date1)>5 > from the mysql manual: "For other dates before > 1582, results from this > function are undefined" > > if your mysql > 4.1.1: > select * from mytable where > DATEDIFF(date1,date2)>5 or > DATEDIFF(date1,date2)<-5 > > but take a look to optimization.. the first > script took 0.0234 > secs to return 256 row of 3096 rows > the second took 0.0413 secs to return the same > couldn't test the third (I have a mysql > 4.0.18-standard-log) > > SD> Second, I want to be able to return a list of > 'clients' from the MySQL > DB > SD> that have a specific number of a particular type > of entry associated > with > SD> them. I am assuming that the 'count' would be > used, but how would I > first > SD> display this count, and second only return those > that have the number of > SD> entries that I am looking for. > > Select count(operation_id) as howmany, > client_id, client_name from > clients left join operations on > operations.client_id = > clients.client_id group by clients.client_id > having howmany=5 > > Then... mysql_num_rows will give you how many > clients have 5 > operations, and each row will tell you: > howmany (always = 5), id of the client, and name > of the client. > > ¿is this what you where looking for?... > > -- > Best regards, > Pablo > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > = Zareef Ahmed :: A PHP Developer in Delhi(India). Homepage :: http://www.zasaifi.com __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Dates and Count
Hi Pablo, Its exactly what I wanted. Thanks. I now have an additional question. I need to show results that appear between two dates as specified by the site visitor. At the moment I am using the following SQL: WHERE NextContact >= '$today' && NextContact <= '$week'"; Second I need the same scenario, however I want to search from a variety of different fields, show those that are between the dates and are equal to one other criteria. Any help on these two? Cheers, Shannon -Original Message- From: Pablo M. Rivas [mailto:[EMAIL PROTECTED] Sent: Monday, 28 June 2004 9:21 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] Dates and Count Hello Shannon, SD> First, I have two dates stored in a MySQL DB that I want to do a compare on SD> and then only display via a web page those records that have a 5 or greater SD> day difference in them. What would be the best way to achieve this. Depends on your mysql version... select * from mytable where date1 + INTERVAL 5 DAY<=date2 or date2 + INTERVAL 5 DAY <=date1; select * from mytable where to_days(date1)-todays(date2)>5 or to_days(date2)-todays(date1)>5 from the mysql manual: "For other dates before 1582, results from this function are undefined" if your mysql > 4.1.1: select * from mytable where DATEDIFF(date1,date2)>5 or DATEDIFF(date1,date2)<-5 but take a look to optimization.. the first script took 0.0234 secs to return 256 row of 3096 rows the second took 0.0413 secs to return the same couldn't test the third (I have a mysql 4.0.18-standard-log) SD> Second, I want to be able to return a list of 'clients' from the MySQL DB SD> that have a specific number of a particular type of entry associated with SD> them. I am assuming that the 'count' would be used, but how would I first SD> display this count, and second only return those that have the number of SD> entries that I am looking for. Select count(operation_id) as howmany, client_id, client_name from clients left join operations on operations.client_id = clients.client_id group by clients.client_id having howmany=5 Then... mysql_num_rows will give you how many clients have 5 operations, and each row will tell you: howmany (always = 5), id of the client, and name of the client. ¿is this what you where looking for?... -- Best regards, Pablo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates and Count
Hello Shannon, SD> First, I have two dates stored in a MySQL DB that I want to do a compare on SD> and then only display via a web page those records that have a 5 or greater SD> day difference in them. What would be the best way to achieve this. Depends on your mysql version... select * from mytable where date1 + INTERVAL 5 DAY<=date2 or date2 + INTERVAL 5 DAY <=date1; select * from mytable where to_days(date1)-todays(date2)>5 or to_days(date2)-todays(date1)>5 from the mysql manual: "For other dates before 1582, results from this function are undefined" if your mysql > 4.1.1: select * from mytable where DATEDIFF(date1,date2)>5 or DATEDIFF(date1,date2)<-5 but take a look to optimization.. the first script took 0.0234 secs to return 256 row of 3096 rows the second took 0.0413 secs to return the same couldn't test the third (I have a mysql 4.0.18-standard-log) SD> Second, I want to be able to return a list of 'clients' from the MySQL DB SD> that have a specific number of a particular type of entry associated with SD> them. I am assuming that the 'count' would be used, but how would I first SD> display this count, and second only return those that have the number of SD> entries that I am looking for. Select count(operation_id) as howmany, client_id, client_name from clients left join operations on operations.client_id = clients.client_id group by clients.client_id having howmany=5 Then... mysql_num_rows will give you how many clients have 5 operations, and each row will tell you: howmany (always = 5), id of the client, and name of the client. ¿is this what you where looking for?... -- Best regards, Pablo -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] dates and updates recommendation?
[snip] > Would I be better off spending a few bytes extra per record and storing > things as DATETIME rather than an INT? If I'm looking at the possibily > making the application more database-portable in the future, are there > gotchas I should be aware of with any particular field types? This has been argued back and forth quite a few times on here. If you want your application portable, use an INT column, store UNIX timestamps, and have PHP do all of the conversion/comparisons for you. If you're just going to be using MySQL, then I definitely recommend using it's date/time format. MySQL has a wealth of date/time functions that make it really easy to get exactly what you want out of the table. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] dates and updates recommendation?
On Sunday 26 January 2003 17:02, Paul Chvostek wrote: > So what's the best way to store a date? I've always liked storing epoch > seconds as INTs, and leaving the translation to/from a human-readable > date up to the application, but aside from the relative unreadability of > this, are there any significant disadvantages to this method, aside from > the rollover problem in 2038? (The S32b bug?) > > Would I be better off spending a few bytes extra per record and storing > things as DATETIME rather than an INT? If I'm looking at the possibily > making the application more database-portable in the future, are there > gotchas I should be aware of with any particular field types? I think it really depends on where you think you would be doing most of your date manipulations. If most will be done within your SQL queries then use the native DBMS date formats (as a plus MySQL has loads of useful date functions). If your date stuff will mostly be done in PHP then use unix timestamps. On the whole I would stick to a DBMS native date format because: - it's human readable - built-in date functions - not limited to post epoch dates -- Jason Wong -> Gremlins Associates -> www.gremlins.biz Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * /* If Machiavelli were a hacker, he'd have worked for the CSSG. -- Phil Lapsley */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Dates
> I am doing a database of things that have dates, some I know the month >and year of, some I only know the year. I suppose I could do a field for >the year, and one for month, and then allow the month to be NULL. But is >there a date type that allows years and / or months??? If that's all you have is a year, or a year-month, then you don't have a date. You have and year and you have a month. Use an integer or text column to store it, and process it with PHP. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates
May I recommend setting the field type to INT, and just using timestamps? That's what I usually do, then you can manipulate things however you want. Much more convenient than the date style type I think. Looking at how the MySQL docs talk about the DATETIME field, I bet the database is doing just that, but converting for you. If you use the PHP date/time functions instead to convert you have a lot more control. -Micah At 09:38 PM 9/27/2002 +0100, Steve Vernon wrote: >Hiya, > I am doing a database of things that have dates, some I know the > month and year of, some I only know the year. I suppose I could do a > field for the year, and one for month, and then allow the month to be > NULL. But is there a date type that allows years and / or months??? > > Thanks, > > > Steve > >-- > > > > _.-..-```---._..``\--.._ >../\```-._ ``-._ > _/_ o \ _.-```.``. \`-._ _>=-. > _.-`\_\ \ o `.(_`.__./ / |--``-`._##.` > -` \ o `. `-`` _` o \#// > / \ _.._ \ o _.`--`` _.-`\_.\-<`\ > | .|`-.___\-` o__.-._. `._..-`` `.\ > |/ \__...--`.-`\.-`_.--`. |) > | _|.-``\``_...-``\ /`. `. `. // > \/.#.`=._ /_ \ \ `._/ \`// >\`#`_.--`|_`--.\ __ `. \\_ `. `.`. > ``` `--._|__\ `.`._.\-`\ \ > `\ \ \ `. ` \ > `. `.`. \ | \ > `. \ ``-~` \ > `. \ _..-` > `-``` >Stephen Vernon, recently graduated from the University of Sheffield, where >I studied Computer Science. I could do with a job! >Stephen Vernon >Mobile 07971 446 956 >Mobile 07971 446 956 >
Re: [PHP-DB] Dates in MYSQL
Hi Alex, You seem to be struggling with both MySQL and PHP at the same time! Must be generating high frustration levels. Have you got yourself a tutorial/book to work from? I'll go with Mike's suggestion, although I'd go for a 'one stop shop' in MySQL - as long as we get the required result it's right! The latest problem is a mix-up of quotation marks - originally you enclosed the query within single quotes and the SQL/PHP parameters in double quotes. Now you have both the same (to accommodate the PHP variable $todaysdate). Change the DATE_FORMAT parameter quotes to singles. Other pieces of (unsolicited) advice: make sure that you have error checking and debugging in place FIRST. Surrounding this one query I would have a debug print of the query string. (1) to show me what has been constructed by PHP (syntax checking), and (2) so that I can copy-paste it out of my browser session and into a MySQL admin package or command-line query (SQL logic checking). Also add a call/calls to check that MySQL returned a valid result to PHP, how many rows were found, etc. (best to direct you to the online manual than to repeat that load of info). Keep on trucking, =dn > Mike, > > I have just tried it again (that was the first way I tried to do the query) > and get the error > > Unknown column '$todaysdate' in 'where clause' > > > -- > Alex Francis > Cameron Design > 35, Drumillan Hill > Greenock PA16 0XD > > Tel 01475 798106 > [EMAIL PROTECTED] > http://www.camerondesign.co.uk > > This message is sent in confidence for the addressee only. It may contain > legally privileged information. > Unauthorised recipients are requested to preserve this confidentiality and > to advise the sender > immediately of any error in transmission. > Mike <[EMAIL PROTECTED]> wrote in message > 001b01c1e658$6f483c00$[EMAIL PROTECTED]">news:001b01c1e658$6f483c00$[EMAIL PROTECTED]... > > Alex, > > > > > enterdate text NOT NULL > > > eventdate text NOT NULL > > > > First, I think I would change the above to a date or datetime or one of > the > > other date & time fields used by mysql instead of text fields. > > > > Second I don't think you really need to use TO DAYS and now. This is what > I > > do. > > > > use php date function to get the current date, > > > > $todaysdate = date("Ymd"); > > > > then do the query, > > > > $query = "SELECT id, eventheading, DATE_FORMAT(eventdate, "%D %M %Y") as > > evdt FROM notices WHERE eventdate >= $todaysdate ORDER BY > > eventdate"; > > > > > > > > > > Mike > > - Original Message - > > From: "Alex Francis" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, April 17, 2002 5:26 PM > > Subject: Re: [PHP-DB] Dates in MYSQL > > > > > > > Tried the following as suggested - Still no dates showing > > > > > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M > %Y")as > > > "evdt" FROM notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > > > eventdate'; > > > > > > > > > $result=mysql_db_query($dbname, $query, $link); > > > > > > if (!$result) { echo( mysql_error()); } > > > else > > > > > > > > > while ($row = mysql_fetch_array($result)) > > > > > > > > > $entername = $row[entername]; > > > $eventdate = $row[evdt]; > > > $eventheading = $row[eventheading]; > > > $id = $row[id]; > > > > > > Database table "notices" as follows > > > id int NULL autoincrement > > > entername text NOT NULL > > > enterdate text NOT NULL > > > eventdate text NOT NULL > > > eventheading text NOT NULL > > > eventbody text NOT NULL > > > > > > > > > > > > > > > -- > > > Alex Francis > > > Cameron Design > > > 35, Drumillan Hill > > > Greenock PA16 0XD > > > > > > Tel 01475 798106 > > > [EMAIL PROTECTED] > > > http://www.camerondesign.co.uk > > > > > > This message is sent in confidence for the addressee only. It may > contain > > > legally privileged information. > > > Unauthorised recipients are requested to preserve this confidentiality > and > > > to advise the sender > > > immediately of any error in transmission. > > >
Re: [PHP-DB] Dates in MYSQL
Mike, I have just tried it again (that was the first way I tried to do the query) and get the error Unknown column '$todaysdate' in 'where clause' -- Alex Francis Cameron Design 35, Drumillan Hill Greenock PA16 0XD Tel 01475 798106 [EMAIL PROTECTED] http://www.camerondesign.co.uk This message is sent in confidence for the addressee only. It may contain legally privileged information. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. Mike <[EMAIL PROTECTED]> wrote in message 001b01c1e658$6f483c00$[EMAIL PROTECTED]">news:001b01c1e658$6f483c00$[EMAIL PROTECTED]... > Alex, > > > enterdate text NOT NULL > > eventdate text NOT NULL > > First, I think I would change the above to a date or datetime or one of the > other date & time fields used by mysql instead of text fields. > > Second I don't think you really need to use TO DAYS and now. This is what I > do. > > use php date function to get the current date, > > $todaysdate = date("Ymd"); > > then do the query, > > $query = "SELECT id, eventheading, DATE_FORMAT(eventdate, "%D %M %Y") as > evdt FROM notices WHERE eventdate >= $todaysdate ORDER BY > eventdate"; > > > > > Mike > ----- Original Message - > From: "Alex Francis" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, April 17, 2002 5:26 PM > Subject: Re: [PHP-DB] Dates in MYSQL > > > > Tried the following as suggested - Still no dates showing > > > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M %Y")as > > "evdt" FROM notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > > eventdate'; > > > > > > $result=mysql_db_query($dbname, $query, $link); > > > > if (!$result) { echo( mysql_error()); } > > else > > > > > > while ($row = mysql_fetch_array($result)) > > > > > > $entername = $row[entername]; > > $eventdate = $row[evdt]; > > $eventheading = $row[eventheading]; > > $id = $row[id]; > > > > Database table "notices" as follows > > id int NULL autoincrement > > entername text NOT NULL > > enterdate text NOT NULL > > eventdate text NOT NULL > > eventheading text NOT NULL > > eventbody text NOT NULL > > > > > > > > > > -- > > Alex Francis > > Cameron Design > > 35, Drumillan Hill > > Greenock PA16 0XD > > > > Tel 01475 798106 > > [EMAIL PROTECTED] > > http://www.camerondesign.co.uk > > > > This message is sent in confidence for the addressee only. It may contain > > legally privileged information. > > Unauthorised recipients are requested to preserve this confidentiality and > > to advise the sender > > immediately of any error in transmission. > > Dl Neil <[EMAIL PROTECTED]> wrote in message > > 0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown... > > > Hi Alex, > > > > > > > Got my select statement to work as follows: > > > > $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) >= > > > TO_DAYS(now()) > > > > order by eventdate'; > > > > > > =well done! > > > However the TO-DAYS calls do seem a bit OTT. > > > Please post the schema for tbl:notices - specifically the datatype for > > > eventdate. > > > > > > > Now trying to get the date more user friendly and tried: > > > > > > > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M > > > %Y")FROM > > > > notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > > > eventdate'; > > > ... > > > > When I echo ($eventdate) I get nothing. Not even an error. > > > > > > > > > The problem is that > > > > > > $eventdate = "$row[eventdate]"; > > > > > > (which doesn't need the " around the whole of the RHS (some would put > > > them around eventdate), BTW) > > > doesn't tie up with: > > > > > > DATE_FORMAT("eventdate", "%D %M %Y") > > > > > > Recommend you change them to: > > > > > > DATE_FORMAT("eventdate", "%D %M %Y") AS evdt > > > and > > > $eventdate = $row[evdt]; > > > > > > See how the SQL and PHP tie together? > > > Now you need to reconsider the other $row[] assignments. > > > > > > =Keep it coming! > > > =dn > > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates in MYSQL
Alex, > enterdate text NOT NULL > eventdate text NOT NULL First, I think I would change the above to a date or datetime or one of the other date & time fields used by mysql instead of text fields. Second I don't think you really need to use TO DAYS and now. This is what I do. use php date function to get the current date, $todaysdate = date("Ymd"); then do the query, $query = "SELECT id, eventheading, DATE_FORMAT(eventdate, "%D %M %Y") as evdt FROM notices WHERE eventdate >= $todaysdate ORDER BY eventdate"; Mike - Original Message - From: "Alex Francis" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, April 17, 2002 5:26 PM Subject: Re: [PHP-DB] Dates in MYSQL > Tried the following as suggested - Still no dates showing > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M %Y")as > "evdt" FROM notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > eventdate'; > > > $result=mysql_db_query($dbname, $query, $link); > > if (!$result) { echo( mysql_error()); } > else > > > while ($row = mysql_fetch_array($result)) > > > $entername = $row[entername]; > $eventdate = $row[evdt]; > $eventheading = $row[eventheading]; > $id = $row[id]; > > Database table "notices" as follows > id int NULL autoincrement > entername text NOT NULL > enterdate text NOT NULL > eventdate text NOT NULL > eventheading text NOT NULL > eventbody text NOT NULL > > > > > -- > Alex Francis > Cameron Design > 35, Drumillan Hill > Greenock PA16 0XD > > Tel 01475 798106 > [EMAIL PROTECTED] > http://www.camerondesign.co.uk > > This message is sent in confidence for the addressee only. It may contain > legally privileged information. > Unauthorised recipients are requested to preserve this confidentiality and > to advise the sender > immediately of any error in transmission. > Dl Neil <[EMAIL PROTECTED]> wrote in message > 0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown... > > Hi Alex, > > > > > Got my select statement to work as follows: > > > $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) >= > > TO_DAYS(now()) > > > order by eventdate'; > > > > =well done! > > However the TO-DAYS calls do seem a bit OTT. > > Please post the schema for tbl:notices - specifically the datatype for > > eventdate. > > > > > Now trying to get the date more user friendly and tried: > > > > > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M > > %Y")FROM > > > notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > > eventdate'; > > ... > > > When I echo ($eventdate) I get nothing. Not even an error. > > > > > > The problem is that > > > > $eventdate = "$row[eventdate]"; > > > > (which doesn't need the " around the whole of the RHS (some would put > > them around eventdate), BTW) > > doesn't tie up with: > > > > DATE_FORMAT("eventdate", "%D %M %Y") > > > > Recommend you change them to: > > > > DATE_FORMAT("eventdate", "%D %M %Y") AS evdt > > and > > $eventdate = $row[evdt]; > > > > See how the SQL and PHP tie together? > > Now you need to reconsider the other $row[] assignments. > > > > =Keep it coming! > > =dn > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates in MYSQL
Tried the following as suggested - Still no dates showing $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M %Y")as "evdt" FROM notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by eventdate'; $result=mysql_db_query($dbname, $query, $link); if (!$result) { echo( mysql_error()); } else while ($row = mysql_fetch_array($result)) $entername = $row[entername]; $eventdate = $row[evdt]; $eventheading = $row[eventheading]; $id = $row[id]; Database table "notices" as follows id int NULL autoincrement entername text NOT NULL enterdate text NOT NULL eventdate text NOT NULL eventheading text NOT NULL eventbody text NOT NULL -- Alex Francis Cameron Design 35, Drumillan Hill Greenock PA16 0XD Tel 01475 798106 [EMAIL PROTECTED] http://www.camerondesign.co.uk This message is sent in confidence for the addressee only. It may contain legally privileged information. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. Dl Neil <[EMAIL PROTECTED]> wrote in message 0b8401c1e62e$03072ad0$0600a8c0@jrbrown">news:0b8401c1e62e$03072ad0$0600a8c0@jrbrown... > Hi Alex, > > > Got my select statement to work as follows: > > $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) >= > TO_DAYS(now()) > > order by eventdate'; > > =well done! > However the TO-DAYS calls do seem a bit OTT. > Please post the schema for tbl:notices - specifically the datatype for > eventdate. > > > Now trying to get the date more user friendly and tried: > > > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M > %Y")FROM > > notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by > eventdate'; > ... > > When I echo ($eventdate) I get nothing. Not even an error. > > > The problem is that > > $eventdate = "$row[eventdate]"; > > (which doesn't need the " around the whole of the RHS (some would put > them around eventdate), BTW) > doesn't tie up with: > > DATE_FORMAT("eventdate", "%D %M %Y") > > Recommend you change them to: > > DATE_FORMAT("eventdate", "%D %M %Y") AS evdt > and > $eventdate = $row[evdt]; > > See how the SQL and PHP tie together? > Now you need to reconsider the other $row[] assignments. > > =Keep it coming! > =dn > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dates in MYSQL
Hi Alex, > Got my select statement to work as follows: > $query = ' SELECT * FROM notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) > order by eventdate'; =well done! However the TO-DAYS calls do seem a bit OTT. Please post the schema for tbl:notices - specifically the datatype for eventdate. > Now trying to get the date more user friendly and tried: > > $query = ' SELECT id, eventheading, DATE_FORMAT("eventdate", "%D %M %Y")FROM > notices WHERE TO_DAYS(eventdate) >= TO_DAYS(now()) order by eventdate'; ... > When I echo ($eventdate) I get nothing. Not even an error. The problem is that $eventdate = "$row[eventdate]"; (which doesn't need the " around the whole of the RHS (some would put them around eventdate), BTW) doesn't tie up with: DATE_FORMAT("eventdate", "%D %M %Y") Recommend you change them to: DATE_FORMAT("eventdate", "%D %M %Y") AS evdt and $eventdate = $row[evdt]; See how the SQL and PHP tie together? Now you need to reconsider the other $row[] assignments. =Keep it coming! =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] dates in MYSQL
Hi Alex Francis, > SELECT From the database WHERE date >= todays date. ^ somethink like now() or date() an other method you put the date in a variable that you get by the datecommand and make SELECT From WHERE date >= datevar (variable with current date) Regards, Ruprecht -- E-Mail: Ruprecht Helms <[EMAIL PROTECTED]> Date: 17-Apr-02 Time: 11:37:25 to be informed -> http://www.rheyn.de <- This message was sent by XFMail -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] dates in db
Try something like: date_format(news_date, '%d %M %Y @ %H:%I:%S') Go to http://www.mysql.com/doc/D/a/Date_and_time_functions.html and look for date_format to determine what parameters to use in order to format the date as you want it. Note that this method will actually format the date returned in your SQL statement and does not require any PHP functions, though PHP will do this to. In most situations I do it using the MySQL function because it uses one less call to format the date. [Romans 10:14) How then shall they call on him in whom they have not believed? and how shall they believe in him of whom they have not heard? and how shall they hear without a preacher? - Original Message - From: "Adam Jackson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, January 05, 2002 8:28 PM Subject: [PHP-DB] dates in db i have a date in my mysql database in the format of 20011201 how do I output that to show for example 01 December 2001 or something similar? Cheers Adam -- PHP Database 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 Database 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]