How to extract database info from current month and year
I am storing records with a date value in the format: yyyy-mm-dd (2017-03-25).
I want to now SELECT all records in the month that it is at the time of viewing the page. So, if I view today, it should show me all records for March 2017. If I view in April, then it should show me all records for April 2017and not April for every year i.e. 2016, 2015, 2014 etc.
In my SELECT statement I don't know if that is where I should try to strip out the day and just extract the month and year or if that would have to be done elsewhere.
I can get the current month and year like:
But then I am not sure how to handle the SQL. I tried something like this but no joy.
I want to now SELECT all records in the month that it is at the time of viewing the page. So, if I view today, it should show me all records for March 2017. If I view in April, then it should show me all records for April 2017and not April for every year i.e. 2016, 2015, 2014 etc.
In my SELECT statement I don't know if that is where I should try to strip out the day and just extract the month and year or if that would have to be done elsewhere.
I can get the current month and year like:
$current_month = date("m");
$current_year = date("Y");
But then I am not sure how to handle the SQL. I tried something like this but no joy.
SELECT `lost`, EXTRACT (YEAR FROM `summary`) AS currentyear WHERE currentyear = ?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This is why you have 104 points for PHP :)
Yes, years of drudgery :-)
Somewhere on this site there is an article with a title like "Beware of Between" that might be worth a look. Without your test data we can't be sure anything will work as we have assumed. Just a thought for going forward in the Q&A process: SSCCE.
Somewhere on this site there is an article with a title like "Beware of Between" that might be worth a look. Without your test data we can't be sure anything will work as we have assumed. Just a thought for going forward in the Q&A process: SSCCE.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If performance is an issue, you can verify the way SQL is handling your query with EXPLAIN SELECT.
I think the real problem with BETWEEN goes a bit beyond indexes and performance to the issue of exact output. Here's my concern. It's subtle but I've seen this issue in real life.
Let's say you have columns of type DATE and your BETWEEN clause identifies ISO-8601-standard start and end dates. Things work perfectly.
Now let's say you ALTER TABLE and change the column definition from DATE to DATETIME. Instead of something like 2017-03-31, newly inserted rows will potentially have additional time information, something like 2017-03-31 14:56:00. If your query says BETWEEN ... AND '2017-03-31' you will not get the rows from 2017-03-31 that have the additional TIME information. You can get this to work correctly if you use the DATE() function on the DATETIME columns. Or you can get this to work correctly if you add 23:59:59 to the end of the last date in the BETWEEN clause.
This is not a problem for the first date, only the last date in the BETWEEN clause.
I think the real problem with BETWEEN goes a bit beyond indexes and performance to the issue of exact output. Here's my concern. It's subtle but I've seen this issue in real life.
Let's say you have columns of type DATE and your BETWEEN clause identifies ISO-8601-standard start and end dates. Things work perfectly.
Now let's say you ALTER TABLE and change the column definition from DATE to DATETIME. Instead of something like 2017-03-31, newly inserted rows will potentially have additional time information, something like 2017-03-31 14:56:00. If your query says BETWEEN ... AND '2017-03-31' you will not get the rows from 2017-03-31 that have the additional TIME information. You can get this to work correctly if you use the DATE() function on the DATETIME columns. Or you can get this to work correctly if you add 23:59:59 to the end of the last date in the BETWEEN clause.
This is not a problem for the first date, only the last date in the BETWEEN clause.
Looks like the "can't use indexes" was a regression bug that is now fixed.
https://bugs.mysql.com/bug.php?id=58190
https://bugs.mysql.com/bug.php?id=58190
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Open in new window