php 仅根据月份和年份选择mySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9104704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select mySQL based only on month and year
提问by DiegoP.
I have a column in my mySQL DB that has some rows. One of this row is a DATE, like this: 2012-02-01
我的 mySQL 数据库中有一个列有一些行。其中一行是 DATE，如下所示：2012-02-01
What I want to achieve is to do a SELECT with PHP based only on the year and month.
我想要实现的是仅基于年和月使用 PHP 执行 SELECT。
The logic of the SELECT will be the following:
SELECT 的逻辑如下：
$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";
The specific month and year will be be passed from a $_POSTvariable, like this $_POST['period']="2012-02";
特定的月份和年份将从$_POST变量中传递，像这样$_POST['period']="2012-02";
How can I do it?
我该怎么做？
回答by Rick Kuipers
SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5
回答by ypercube??
If you have
如果你有
$_POST['period'] = "2012-02";
First, find the first day of the month:
首先，找到一个月的第一天：
$first_day = $_POST['period'] . "-01"
Then this query will use an index on Dateif you have one:
那么这个查询将使用一个索引，Date如果你有一个：
$q = "
SELECT *
FROM projects
WHERE Date BETWEEN '$first_day'
AND LAST_DAY( '$first_day' )
" ;
One could also use inclusive-exclusive intervals, which work pretty good (you don't have to worry if the column is DATE, DATETIMEor TIMESTAMP, nor about the precision:
还可以使用包含-排除间隔，它工作得很好（您不必担心列是否为DATE、DATETIME或TIMESTAMP，也不必担心精度：
$q = "
SELECT *
FROM projects
WHERE Date >= '$first_day'
AND Date < '$first_day' + INTERVAL 1 MONTH
" ;
Security warning:
安全警告：
You should properly escape these values or use prepared statements. In short, use whatever method is recommended these days in PHP, to avoid any SQL injection issues.
您应该正确地转义这些值或使用准备好的语句。简而言之，使用目前在 PHP 中推荐的任何方法，以避免任何 SQL 注入问题。
回答by aefxx
Here you go. Leave the computing to PHP and save your DB some work. This way you can make effective use of an index on the Datecolumn.
干得好。将计算留给 PHP 并为您的数据库节省一些工作。这样您就可以有效地利用Date列上的索引。
<?php
$date = $_POST['period'];
$start = strtotime($date);
$end = strtotime($date . ' 1 month - 1 second');
$query = sprintf(
'SELECT *
FROM projects
WHERE Date BETWEEN FROM_UNIXTIME(%u) AND FROM_UNIXTIME(%u)',
$start,
$end
);
EDIT
Forgot the Unix timestamp conversion.
编辑
忘记了 Unix 时间戳转换。
回答by Arjan
$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;
回答by nazmulhaqued
Suppose you have a database field created_at Where you take value from timestamp. You want to search by Year & Month from created_at date.
假设您有一个数据库字段 created_at 从时间戳中获取值。您想从 created_at 日期开始按年和月进行搜索。
YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2
回答by chintan mahant
Here, FIND record by MONTH and DATE in mySQL
在这里，在 mySQL 中按 MONTH 和 DATE 查找记录
Here is your POST value $_POST['period']="2012-02";
这是您的 POST 值 $_POST['period']="2012-02";
Just, explode value by dash $Period = explode('-',$_POST['period']);
只是，破折号爆炸价值 $Period = explode('-',$_POST['period']);
Get array from explode value :
从爆炸值获取数组：
Array
(
[0] => 2012
[1] => 02
)
Array
(
[0] => 2012
[1] => 02
)
Put value in SQL Query:
将值放入 SQL 查询：
SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';
SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';
Get Result by MONTH and YEAR.
按月和年获取结果。
回答by Andreas Helgegren
You can do like this:
你可以这样做：
$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";
回答by PAULDAWG
to get the month and year values from the date column
从日期列中获取月份和年份值
select year(Date) as "year", month(Date) as "month" from Projects
回答by Hristo Petev
The logic will be:
逻辑将是：
SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%';
The LIKEoperator will select all rows that start with $_POST['period']followed by dash and the day of the mont
该LIKE运营商将选择与开始的所有行$_POST['period']的山的天，随后为破折号和
http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html- Some additional information
http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html- 一些附加信息
回答by Frank Casser
Here is a query that I use and it will return each record within a period as a sum.
这是我使用的一个查询，它将以总和形式返回一段时间内的每条记录。
Here is the code:
这是代码：
$result = mysqli_query($conn,"SELECT emp_nr, SUM(az)
FROM az_konto
WHERE date BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59'
GROUP BY emp_nr ASC");
echo "<table border='1'>
<tr>
<th>Mitarbeiter NR</th>
<th>Stunden im Monat</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
$emp_nr=$row['emp_nr'];
$az=$row['SUM(az)'];
echo "<tr>";
echo "<td>" . $emp_nr . "</td>";
echo "<td>" . $az . "</td>";
echo "</tr>";
}
echo "</table>";
$conn->close();
?>
This lists each emp_nr and the sum of the monthly hours that they have accumulated.
这列出了每个 emp_nr 以及它们累积的每月小时数的总和。
