at0mic Posted April 17, 2007 Share Posted April 17, 2007 I've made a form which posts News Articles into MySQL. The articles are then shown on the main page. I've been asked to add a Start Date to the form and enable it to only show News Articles after the Start Date (the articles could be submitted weeks in advance). I've also been asked to add an option so you can select how many days you want the Article to appear on the main page for. How can I query such information so it can be viewed? Many thanks Atomic Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/ Share on other sites More sharing options...
paul2463 Posted April 17, 2007 Share Posted April 17, 2007 $query = "SELECT news_articles FROM table WHERE Start_date > CURDATE()"; //CURDATE() returns todays date Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-231247 Share on other sites More sharing options...
at0mic Posted April 17, 2007 Author Share Posted April 17, 2007 $query = "SELECT news_articles FROM table WHERE Start_date > CURDATE()"; //CURDATE() returns todays date Thanks thats works But I want to use three Fields for the date called Day,Month,Year so I can use drop downs to make sure people use the correct format for the date. Is there a way I could still use the CURDATE command? If not I'll just make it clear on the form how users must enter the day Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-231346 Share on other sites More sharing options...
paul2463 Posted April 17, 2007 Share Posted April 17, 2007 it is fairly difficult to check for dates in the future when the year month and day are in separate columns, you cant use GREATER THAN easily for any of them lets take a date(today in fact) year = 2007 / month = 04 / day = 17 and a date in the future year = 2008 / month = 03 / day = 10 now if you say year GREATER THAN todays year, you have lost the rest of this year because it will only be looking for dates in 2008 onward, not such a big problem because for the year part, GREATER THAN OR EQUAL TO, will work Month is a problem, 2008 would make the year part true but if the month was March then the month cannot be GREATER THAN because mathematically it is LESS THAN Day is also a problem, it is however in the future but you cant use GREATER THAN because mathematically it is SMALLER THAN yet in the future. I hope all this drivel has helped in some way an option you could use, its not quite economic with columns, and I will be derided by Fenway probably for mentioning this, but if you store the dates in your database as day, month and year, then have another column in that table to store the date as a full date for this purpose alone. Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-231408 Share on other sites More sharing options...
at0mic Posted April 18, 2007 Author Share Posted April 18, 2007 thanks very much for your advise. I'll just have one date column for the time being. When I've done everything else I'll revisit the issue. Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-231998 Share on other sites More sharing options...
paul2463 Posted April 19, 2007 Share Posted April 19, 2007 come across this which may help you $query ="Select str_to_date(Day+"-"+Month+"-"+Year) as mydate"; takes the column names day month year, concatenates them and then uses STR_TO_DATE() to make it into a timestamp Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-232991 Share on other sites More sharing options...
at0mic Posted April 19, 2007 Author Share Posted April 19, 2007 thank you for that, I'll give it a go Link to comment https://forums.phpfreaks.com/topic/47397-query-date-number-of-days/#findComment-233329 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.