如何从mysql数据库中查找与特定月份匹配的所有数据？(How to find all the data that match a particular month from mysql databa

如何从mysql数据库中查找与特定月份匹配的所有数据？(How to find all the data that match a particular month from mysql database?)

我目前在从数据库中获取与特定月份匹配的所有数据时遇到问题，并且仅显示该月份的所有数据。 这是我试过的。

<?php require('./connect.php'); ?> <?php $name1 = $conn->prepare("SELECT * FROM garden WHERE MONTH(Apr)"); $name1->execute(); while($row = $name1->fetch()) { $t = $row["temp"]; echo $t; echo "sdasds"; } ?>

数据库有time列，存储日期为12-Apr-2017 ，我想查找4月或月的所有详细信息。

I am currently having problem getting all the data from the database that match a particular month and show all the data from that month only. here's what I tried.

<?php require('./connect.php'); ?> <?php $name1 = $conn->prepare("SELECT * FROM garden WHERE MONTH(Apr)"); $name1->execute(); while($row = $name1->fetch()) { $t = $row["temp"]; echo $t; echo "sdasds"; } ?>

The database has time column that stores date as 12-Apr-2017 and I want to find all the details of april month or may month.

最满意答案

你没有正确使用MONTH() 。 您需要将列标识符作为参数传递，并将其值与您想要的值进行比较，即4月是第四个月时的值：

SELECT * FROM garden WHERE MONTH(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 4

如果您想要更可读的查询，请使用MONTHNAME()

SELECT * FROM garden WHERE MONTHNAME(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 'April'

当您将日期存储为字符串而不是日期时，您还会注意到我使用STR_TO_DATE() 。 在将来，您应该考虑将日期存储为日期值，因为将它们转换为不同的格式很容易，这使得日期工作变得更加容易。

You're not using MONTH() correctly. You need to pass the column identifier as the parameter and compare its value to the value you want which is 4 as April is the fourth month:

SELECT * FROM garden WHERE MONTH(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 4

If you want a more readable query use MONTHNAME()

SELECT * FROM garden WHERE MONTHNAME(STR_TO_DATE(dateColumn, '%e-%b-%Y')) = 'April'

You will also notice I use STR_TO_DATE() as you stored your dates as strings instead of dates. In the future you should consider storing dates as date values as converting them into a different format is easy and it makes doing date work a lot easier.