select records only wth current month

[jeff5656]

I have a date filed where the record is stored as 2009-12-31.

How do I select all records with the current month?  I put an "xxxx" in where I think it has to go but not sure exactly how to do it thanks!

 

<?php
$current_month= date(xxxx);
$sql = "Select * from $DB_TBLName2 where vent_today='y' and bundle_date=  '$current_month' ";

[Daniel0]

SELECT * FROM table WHERE MONTH(date) = MONTH(CURDATE());

 

Where date is the field containing the date obviously.

[jeff5656]

Hi, I tried to duplicate your suggestion but did not get any results. 

 

$current_date = date("Y-m-d");

$sql = "Select * from $DB_TBLName2 where vent_today='y' and MONTH (bundle_date) = 'month ($current_date())' ";

[jeff5656]

I'm going to ask this in the php help forum if you don't mind since it's really an issue with how to frame the DATE and where to put the quotes.

[Daniel0]

Uh, yeah I do mind. And it's not a PHP issue just because you wrote your script in PHP. The issue is with the query, which by nature is a database problem. The reason why what you wrote didn't work is because you used the wrong syntax in the query.

 

13. Duplication of topics is strictly prohibited.

 

Anyway, why didn't you just do what I posted:

 

$sql = "Select * from $DB_TBLName2 where vent_today='y' and MONTH (bundle_date) = MONTH(CURDATE())";

 

MySQL can figure out the current month just fine on its own.

[jeff5656]

Uh, it is a php issue.  Why do you think no one has replied here in this "correct" forum - I got a solution to my problem by reposting it under php coding help.  There was a prblem with how I worded the DATE function, and a user gave me the correct syntax and my problem is solved.  If I have a question about a function that isn't working should we make a "function" forum?  It turns out it WAS the syntax that was the problem (for instance your solution has syntax that does not work - a member from the php forum was able to supply me with the correct solution before my question was moved).

 

[jeff5656]

Also I see you deleted the question in the other forum.  I am thankful I was able to see the member's response before you deleted it and now my code works.  It is unfortunate others who may have a similar problem will not have the opportunity to see the solution.  This may be an example of being so "efficient" in putting everything in the absolute correct box that it becomes less functional.

[aschk]

The point Daniel was attempting to make is that you don't need to use PHP at all to calculate the current month.

Hence:

 

<?php
// No need to calculate current month from PHP as MySQL can do this already.
$sql = "Select * from $DB_TBLName2 where vent_today='y' and MONTH(bundle_date) = MONTH(CURDATE())";

 

The above will work just fine.

[jeff5656]

No that did not work for me (I don't know why it did not, which is why I needed help with  the code).  The solution that did solve my problem (now deleted by the sysop) was this:

 

$current_date = date("Y-m-d");

 

$sql = "Select * from $DB_TBLName2 where vent_today='y' and MONTH (bundle_date) = month ('$current_date') ";

 

That worked but the above did not. I do not know why but that's because I am a php newbie.  A discussion of alternative solutions to the above cannot be offered anymore since he deleted the thread.  If it was not deleted maybe others would have chimed in about why one solution worked and one didn't.

[jeff5656]

Ok I tried your code and it also works.  but I still like the other forum since it's so active and you get a reply to your solution sometimes in *minutes*.  (for instance I was able to get  reply even before the sysop was able to delete it).  That's why I love this web site.

[Daniel0]

If you cannot accept our rules, you are at the same time not accepting our Terms of Service. If you do not accept these you are not authorized to use this website, its forum or any other resources available on this website. As long as you are here you will be required to follow our rules. End of discussion.

 

If you don't like it you can go elsewhere, but I'm pretty sure that few other places allow you to blatantly disregard their rules. We have a rule that prohibits posting the same topic in multiple sections. That's a pretty normal rule that exist on the majority of forums.

 

You are in no way any special person, so there is no reason why you should be allowed to disregard our rules. It's a MySQL issue because you are having trouble writing a query. The fact that the query is run via PHP is irrelevant. You are not having trouble with running the query, but trouble with creating the query itself. That is a MySQL issue.

 

You may have a table and some content like this:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


INSERT INTO `test` (`id`, `name`, `date`) VALUES
(1, 'Previous month', '2009-07-13'),
(2, 'This month', '2009-08-13'),
(3, 'Next month', '2009-09-13');

 

And you may then run these queries:

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2009-08-13 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(CURDATE());
+------------------+
| MONTH(CURDATE()) |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `test` WHERE MONTH(`date`) = MONTH(CURDATE());
+----+------------+------------+
| id | name       | date       |
+----+------------+------------+
|  2 | This month | 2009-08-13 |
+----+------------+------------+
1 row in set (0.00 sec)

 

Now, date('Y-m-d') will evaluate to 2009-08-13 today, so if we run:

mysql> SELECT MONTH('2009-08-13');
+---------------------+
| MONTH('2009-08-13') |
+---------------------+
|                   8 |
+---------------------+
1 row in set (0.00 sec)

 

We get the exact same thing. So the two queries are equivalent.

 

Again, read this entire page. If there is anything there, which do cannot accept I'm afraid you are no longer allowed to use this website. If you find any of our rules to be unreasonable I suppose you can bring it up in the feedback/suggestions board, but only with a compelling reason why it should be changed.