从单个组合框返回2个日期以查询MYSQL数据库 [英] Return 2 dates from single combo box to query MYSQL db

问题描述

所以我想让用户从组合框中选择一个星期"，并让它向SQL语句返回2个日期(该周的开始和结束)...这将在该周内提供数据时期.我只需要1个组合框，因为此数据将仅在数周内可用.我该怎么办?

So I want to have a user select a 'week' from a combo box, and have that return 2 dates (start and finish of that week) to an SQL statement..... which would give data within that week period. I only need 1 combo box, as this data will only be available for weeks.. how do i do it?

这是我的代码:

<form id="weekselectform" name="weekselectform" method="post" action="">
<div id="weekselect">
<table>
<tr>
<td>Select Week:

<select name="dateentryweek" id="entryweek1">
<option value=""></option><option value="2013-07-01">Week 1</option><option value="2013-07-08">Week 2</option><option value="2013-07-15">Week 3</option><option value="2013-07-22">Week 4</option>
<option value="2013-07-29">Week 5</option><option value="2013-08-05">Week 6</option><option value="2013-08-12">Week 7</option><option value="2013-08-19">Week 8</option>
<option value="2013-08-26">Week 9</option><option value="2013-09-02">Week 10</option><option value="2013-09-09">Week 11</option><option value="2013-09-16">Week 12</option>
<option value="2013-09-23">Week 13</option><option value="2013-09-30">Week 14</option><option value="2013-10-07">Week 15</option><option value="2013-10-14">Week 16</option>
<option value="2013-10-21">Week 17</option><option value="2013-10-28">Week 18</option><option value="2013-11-04">Week 19</option><option value="2013-11-11">Week 20</option>
<option value="2013-11-18">Week 21</option><option value="2013-11-25">Week 22</option><option value="2013-12-02">Week 23</option><option value="2013-12-09">Week 24</option>
<option value="2013-12-16">Week 25</option><option value="2013-12-23">Week 26</option><option value="2013-12-30">Week 27</option><option value="2014-01-06">Week 28</option>
<option value="2014-01-13">Week 29</option><option value="2014-01-20">Week 30</option><option value="2014-01-27">Week 31</option><option value="2014-02-03">Week 32</option>
<option value="2014-02-10">Week 33</option><option value="2014-02-17">Week 34</option><option value="2014-02-24">Week 35</option><option value="2014-03-03">Week 36</option>
<option value="2014-03-10">Week 37</option><option value="2014-03-17">Week 38</option><option value="2014-03-24">Week 39</option><option value="2014-03-31">Week 40</option>
<option value="2014-04-07">Week 41</option><option value="2014-04-14">Week 42</option><option value="2014-04-21">Week 43</option><option value="2014-04-28">Week 44</option>
<option value="2014-05-05">Week 45</option><option value="2014-05-12">Week 46</option><option value="2014-05-19">Week 47</option><option value="2014-05-26">Week 48</option>
<option value="2014-06-02">Week 49</option><option value="2014-06-09">Week 50</option><option value="2014-06-16">Week 51</option><option value="2014-06-23">Week 52</option>
</select>
</td>
<td><input type="submit" value="GO" class="button black" id="weekbutton2" /></td>
</tr>
</table>
</div>
</form>

该内容提交到页面，最初是由php用2个组合框抓取的:

That submits to the page and was originally grabbed with 2 combo boxes, by php:

$inputweek11 = $_POST[entryweek1];
$inputweek22 = $_POST[entryweek2];

$result1=mysql_query("  
SELECT Supervisor,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 2 THEN 1 END) `Monday`,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 3 THEN 1 END) `Tuesday`,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 4 THEN 1 END) `Wednesday`,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 5 THEN 1 END) `Thursday`,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 6 THEN 1 END) `Friday`,
COUNT(CASE WHEN DAYOFWEEK(workdate) = 7 THEN 1 END) `Saturday`
FROM payroll.employeedatanew_copy
JOIN payroll.employeehours ON employeedatanew_copy.`ID Number` = employeehours.employeeid
WHERE employeehours.workdate BETWEEN '$inputweek11' AND '$inputweek22'
GROUP BY supervisor
");

推荐答案

两个选项可能是:

您可以使日期选择值包括一个开始和结束的定界符，您可以将它们分解为一个数组，例如.

You could make the date select values include the start and finish with a delimiter that you can explode them into an array on, eg.

 <option value="2013-12-02:2013-12-02">Week 5</option>


 $dates = explode(':', $_POST['week']);

 $start_date = $dates[0];

 $end_date = $dates[1];

或

鉴于您的星期可能总是与您可以使用php或mysql date函数将开始日期添加所需天数的长度相同，以便生成结束日期.例如. http://us3.php.net/manual/en/datetime.add.php 或 http: //dev.mysql.com/doc/refman/5.5/zh-CN/date-and-time-functions.html#function_date-add 这第二个选项可能是两者中较好的一个.在php中，如果您有星期开始日期，并且想要结束日期，则可以

Given that your weeks will probably always be the same length you could use either php or mysql date functions to add the required number of days to the start date in order to generate the finish date. eg. http://us3.php.net/manual/en/datetime.add.php or http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add This second option is probably the better of the two, eg. in php if you have the week start date and want the end date you can do

$date = new DateTime($_POST[entryweek1]);
$date->add(new DateInterval('P7D'));
$finish_date = $date->format('Y-m-d') 

或者，如果您未在脚本的该部分中使用其他地方，则不需要在php中生成它，您可以在mysql中执行以下操作:

Or you don't event need to generate it in php if you are not using anywhere else in that part of the script, you can just do something like the following in mysql:

SELECT * from supervisor ... where workdate BETWEEN '2013-10-01' AND DATE_ADD('2013-10-01', INTERVAL 7 DAY);

(不管最后选择哪个选项，请确保在清理$ _POST输入之前像上面一样将其输入到sql中-即使它来自选择列表，也不能保证有人不会手动将一些数据输入到帖子中以执行sql注入攻击)

(regardless of which option you choose in the end, make sure you sanitize the $_POST input before you feed it into your sql as you do above - even though it is coming from a select list you can't guarantee that someone won't manually feed some data into the post to perform an sql injection attack)

这篇关于从单个组合框返回2个日期以查询MYSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！