blocks|key|662046|text|SELECT+*+FROM+projects+WHERE+YEAR(Date)+=+2011+AND+MONTH(Date)+=+5|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|662047|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5
</code></pre>

blocks|key|447625|text|逻辑将是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447626|SELECT+*+FROM+objects+WHERE+Date+LIKE+'$_POST[period]-%25';|code-block|syntax|javascript|447627|LIKE运算符将选择所有以$_POST['period']开头、后面跟着破折号和日期的行|offset|length|style|CODE|447628|http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html+-一些附加信息|447629|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html^0|0|0|0|4|D|G|0|0|1O|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@$I|14|J|15|1|16]]|A|$]]|$1|O|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

The logic will be:

<pre><code>SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%';
</code></pre>

The <code>LIKE</code> operator will select all rows that start with <code>$_POST['period']</code> followed by dash and the day of the mont

<a href="http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html</a> - Some additional information

blocks|key|662096|text|$q="SELECT+*+FROM+projects+WHERE+YEAR(date)+=+2012+AND+MONTH(date)+=+1;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|662097|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;
</code></pre>

blocks|key|447639|text|你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447640|$q="SELECT+*+FROM+projects+WHERE+Year(Date)+=+'$year'+and+Month(Date)+=+'$month'";|code-block|syntax|javascript|447641|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can do like this:

<pre><code>$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";
</code></pre>

blocks|key|662138|text|从date列中获取月份和年值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|662139|select+year(Date)+as+"year",+month(Date)+as+"month"+from+Projects|code-block|syntax|javascript|662140|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

to get the month and year values from the date column

<pre><code>select year(Date) as "year", month(Date) as "month" from Projects
</code></pre>

blocks|key|662191|text|在这里，您可以在mySQL中按月和日期查找记录|type|unstyled|depth|inlineStyleRanges|entityRanges|data|662192|这是您的POST+value+$_POST['period']="2012-02";|offset|length|style|CODE|662193|只是，用破折号$Period+=+explode('-',$_POST['period']);分解值|662194|从分解值中获取数组：|662195|Array+++++(+++++++++[0]+=>+2012+++++++++[1]+=>+02+++++)|662196|Put+value+in+SQL查询：|662197|SELECT+*+FROM+projects+WHERE+YEAR(Date)+=+'".$Period[0]."'+AND+Month(Date)+=+'".$Period[0]."';|662198|按月、年获取结果。|662199|entityMap^0|0|F|R|0|7|14|0|0|0|1J|0|0|0|2M|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@$D|14|E|15|F|G]]|9|@]|A|$]]|$1|N|3|O|5|6|7|16|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1A|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|U|$]]

Here, FIND record by MONTH and DATE in mySQL

Here is your POST value <code>$_POST['period']="2012-02";</code>

Just, explode value by dash <code>$Period = explode('-',$_POST['period']);</code>

Get array from explode value : 

<code>Array
 (
 [0] =&gt; 2012
 [1] =&gt; 02
 )</code>

Put value in SQL Query:

<code>SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';</code>

Get Result by MONTH and YEAR.

blocks|key|447804|text|是的，这是有效的，但它只返回一行，而只使用选定的列检索几行。与SELECT+Title类似，Date+FROM+mytable+WHERE+YEAR(Date)=2017+AND+MONTH(Date)=09仅返回一行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447805|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Yeah this is working, but it returns one row only while there is several rows to retrieve using selected columns only.
Like SELECT Title,Date FROM mytable WHERE YEAR(Date)=2017 AND MONTH(Date)=09
returns only one row.

blocks|key|447844|text|下面是我使用的一个查询，它将一段时间内的每条记录作为总和返回。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447845|代码如下：|447846|$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();
?>|code-block|syntax|javascript|447847|这将列出每个emp_nr及其累积的每月小时数之和。|447848|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is a query that I use and it will return each record within a period as a sum.

Here is the code:

<pre><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 "&lt;table border='1'&gt;
&lt;tr&gt;
&lt;th&gt;Mitarbeiter NR&lt;/th&gt;
&lt;th&gt;Stunden im Monat&lt;/th&gt;
&lt;/tr&gt;";

while($row = mysqli_fetch_array($result))
{
$emp_nr=$row['emp_nr'];
$az=$row['SUM(az)'];
echo "&lt;tr&gt;";
echo "&lt;td&gt;" . $emp_nr . "&lt;/td&gt;";
echo "&lt;td&gt;" . $az . "&lt;/td&gt;";
echo "&lt;/tr&gt;";
}
echo "&lt;/table&gt;";
$conn-&gt;close();
?&gt;
</code></pre>

This lists each emp_nr and the sum of the monthly hours that they have accumulated.

blocks|key|447858|text|假设您有一个数据库字段created_at，您可以从其中获取时间戳的值。您想要从created_at日期开始按年和月进行搜索。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|447859|YEAR(date(created_at))=2019+AND+MONTH(date(created_at))=2|code-block|syntax|javascript|447860|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Suppose you have a database field created_at Where you take value from timestamp.
You want to search by Year &amp; Month from created_at date.

<pre><code>YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2
</code></pre>

blocks|key|662296|text|可以通过将$q更改为以下内容来完成此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|662297|$q="SELECT+*+FROM+projects+WHERE+YEAR(date)+=+$year_v+AND+MONTH(date)+=+$month_v;|code-block|syntax|javascript|662298|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

you can do it by changing $q to this:
<pre><code>$q=&quot;SELECT * FROM projects WHERE YEAR(date) = $year_v AND MONTH(date) = $month_v;
</code></pre>

blocks|key|662333|text|从表中获取特定的月份和年份数据|type|unstyled|depth|inlineStyleRanges|entityRanges|data|662334|SELECT+*+FROM+table+WHERE+DATE_FORMAT(column_name,'%25Y-%25m')+=+'2021-06'|code-block|syntax|javascript|662335|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To get the specific month and year data from the table
<pre class="lang-sql prettyprint-override"><code>SELECT * FROM table WHERE DATE_FORMAT(column_name,'%Y-%m') = '2021-06'
</code></pre>

blocks|key|447961|text|你可以使用mysql的DATE_FORMAT函数，只考虑日期的year%2Bmonth部分：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|447962|select+*+from+table+where+DATE_FORMAT(column,+'%25Y%25m')+=+'202105';|code-block|syntax|javascript|447963|entityMap|0|LINK|mutability|MUTABLE|url|https://www.w3schools.com/sql/func_mysql_date_format.asp^0|B|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use mysql <a href="https://www.w3schools.com/sql/func_mysql_date_format.asp" rel="nofollow noreferrer">DATE_FORMAT</a> function consider just the year+month part of the date:
<pre><code>select * from table where DATE_FORMAT(column, '%Y%m') = '202105';
</code></pre>

I have a column in my mySQL DB that has some rows. One of this row is a DATE, like this: <code>2012-02-01</code>

What I want to achieve is to do a SELECT with PHP based only on the year and month.

The logic of the SELECT will be the following:

<pre><code>$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";
</code></pre>

The specific month and year will be be passed from a <code>$_POST</code> variable, like this <code>$_POST['period']="2012-02";</code>

How can I do it?

Select mySQL based only on month and year

SQL数据库

数据库

我的mySQL数据库中有一列，其中有一些行。这一行中有一行是日期，如下所示：2012-02-01我想要实现的是用PHP只根据年份和月份进行选择。SELECT的逻辑如下：$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MON...

问仅根据月份和年份选择mySQL
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅根据月份和年份选择mySQLEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅根据月份和年份选择mySQL
EN