matfish Posted August 16, 2006 Share Posted August 16, 2006 Hi,Having a few probs php/mysql.Got a table with datetime (0000-00-00 00:00:00) and needing to do a mysql query on the months only (like archiving the months only) but how do I search for the months out of that type of date?Many thanks Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted August 16, 2006 Share Posted August 16, 2006 I'm not sure if this will work, but try something like: [code]SELECT * FROM whatever WHERE date LIKE '%-month_here-% %:%:%';[/code] Quote Link to comment Share on other sites More sharing options...
obsidian Posted August 16, 2006 Share Posted August 16, 2006 it's as easy as MONTH():[code]SELECT * FROM tableName WHERE MONTH(dateCol) = '2';[/code]that would return all records from february Quote Link to comment Share on other sites More sharing options...
matfish Posted August 16, 2006 Author Share Posted August 16, 2006 Brilliant - thank you Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Got a table with datetime (0000-00-00 00:00:00) and need to insert the date one year on from today.How do I go about doing this? Also do you need ' ' around the datetime when inserting into the database? HAving a bit of trouble...Many thanks Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Inserting using php, would I also need ' ' around the 'NOW()' or just NOW()?Many thanks Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 Just now() without the quotes...[code]$sql="INSERT into table_name (col1, col2, col3) VALUES ("data1", "data2", now())";[/code]RegardsRich Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Thats cool thanks - any ideas on adding on months/years etc?Thanks Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 Is the field type 'datetime'?Rich Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Hi,yeah the fieldtype is datetime. Trying to make an expiry date from an order, so:expirydate = now() + 1 year. Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 OK, this can be one of two ways, you can manipulate the date in PHP first, or when you insert it into the database.I'm assuming you're using MySQL?Rich Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Hi- thanks for the replies.Yes using MySQL, been trying to manipulate it in php before inserting with no joy :(Thanks Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 5, 2006 Share Posted September 5, 2006 if you're wanting to manipulate with PHP, just use the following for one year from today:[code]<?php// insert this value into the datetime field$date = date('Y-m-d', strtotime("today + 1 year"));?>[/code]if you'd rather do it with mySQL directly, you can do the following query:[code]INSERT INTO tableName (dateCol) VALUES (DATE_ADD(CURDATE(), INTERVAL 1 YEAR));[/code]hope this helps Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 Hi - thank you - Works a treat!I gather if I wanted to add on months the same would apply?Much appreciated! Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted September 5, 2006 Share Posted September 5, 2006 Beat me to it :(I had the comma missing from my statement when testing it and couldn't figure out why this wasn't working...[code]INSERT into tableName (start_date, end_date) VALUES (now(), DATE_ADD(now() INTERVAL 1 YEAR));[/code]Well done Obs. Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 I thank you both for your prompt replies! Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 5, 2006 Share Posted September 5, 2006 [quote author=matfish link=topic=104492.msg428501#msg428501 date=1157469633]Hi - thank you - Works a treat!I gather if I wanted to add on months the same would apply?Much appreciated![/quote]yes, check out the INTERVAL keyword in the mysql manual. you can use it to declare anything from seconds to years to whatever. Quote Link to comment Share on other sites More sharing options...
matfish Posted September 5, 2006 Author Share Posted September 5, 2006 how about if I was using the php manipulation way? Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 5, 2006 Share Posted September 5, 2006 [quote author=matfish link=topic=104492.msg428516#msg428516 date=1157470287]how about if I was using the php manipulation way?[/quote]same idea. read up in the manual on strtotime(). it will handle practically any human readable date format or interval and calculate the differences.good luck Quote Link to comment Share on other sites More sharing options...
matfish Posted September 8, 2006 Author Share Posted September 8, 2006 Hi-again.How do I read a datetime (0000-00-00 00:00:00) easily in php? I just wanted day-month-year? It keeps bringing back a stupid date of 1st Jan 1970...Thanks... Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 8, 2006 Share Posted September 8, 2006 check out strtotime():[code]<?php$ts = "2006-02-14 12:00:00";echo date('y-m-d', strtotime($ts));?>[/code] Quote Link to comment Share on other sites More sharing options...
matfish Posted September 8, 2006 Author Share Posted September 8, 2006 Perfect once again, thank you! Quote Link to comment Share on other sites More sharing options...
matfish Posted September 20, 2006 Author Share Posted September 20, 2006 Hi, Dont know why I have so much trouble with dates... but - in regards to the above (using datetime) how can I -5days (or whatever) from a date from the database? It keeps bringing back weird dates etc..Many thanks Quote Link to comment Share on other sites More sharing options...
matfish Posted September 20, 2006 Author Share Posted September 20, 2006 Its ok, $packageexpiry = date("d-m-Y", strtotime("-5 days", strtotime($hosting['packageexpirydate']))); kinda did it Quote Link to comment Share on other sites More sharing options...
coalduststar Posted October 3, 2006 Share Posted October 3, 2006 brilliant- this helped me out too! was trying to format just a TIME and DATE separately- THANKS! ;D Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.