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

所以我想让用户从组合框中选择一个"周"，并将2个日期（该周的开始和结束）返回到SQL语句。。。。。这将给出该周内的数据。我只需要一个组合框，因为这些数据只在几周内可用。。我该怎么做？

这是我的代码：

<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个组合框抓取

$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
");

两个选项可以是：

你可以让日期选择值包括开始和结束，并使用一个分隔符，你可以将它们分解成一个数组，例如

 <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日期函数将所需的天数添加到开始日期，以便生成完成日期。例如。http://us3.php.net/manual/en/datetime.add.php或http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-添加第二个选项可能是两者中更好的，例如，在php中，如果你有周开始日期，并且想要结束日期，你可以进行

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

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

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

（无论您最终选择哪个选项，请确保在将$_POST输入输入输入到sql之前对其进行消毒，如上所述-即使它来自选择列表，您也不能保证有人不会手动将一些数据输入到帖子中以执行sql注入攻击）

if(isset($_POST['submit'])){
$sql = "select week('2013-07-01')";
if (!($stmt = $mysqli->prepare($sql))) {
    echo "Prepare Failed (" . $mysqli->errno . ") " . $mysqli->error;
}else if (!$stmt->execute()) {
    echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$week1 = NULL;
if (!$stmt->bind_result($week1)) {
    echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
$stmt->fetch();
$stmt->close();
$week = $week1 + $_POST['week'] - 1;
$year = date("Y");
if($week >52)
{
    $week = $week - 51; 
    ++$year ;
}
echo '<p>Calendar week '.$week.'</p>';
$start_date = $year.$week.' Monday';
$end_date = $year.$week.' Friday';
$day1    = NULL; $day5 = NULL; 
$sql = "SELECT STR_TO_DATE('$start_date', '%X%V %W') as Day1, STR_TO_DATE('$end_date', '%X%V %W') as Day5 ";
if (!($stmt = $mysqli->prepare($sql))) {
    echo "Prepare Failed (" . $mysqli->errno . ") " . $mysqli->error;
}
else if (!$stmt->execute()) {
    echo "Execute failed: (" . $mysqli->errno . ") " . $mysqli->error;
}else if (!$stmt->bind_result($day1, $day5)) {
    echo "Binding output parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}else{
    $stmt->fetch();
    echo '<p>For Week '.$_POST['week'].'</p><p>Monday: '.$day1 . ' Friday: '. $day5. '</p>'; 
}

}

在HTML表单中，让它简单

 <label for="week">Select Week</label>
 <select name="week">
   <?php    for($i=0;$i<52;){
    echo '<option value="'.++$i.'">Week'.$i.'</option>';
    }
?>
  </select>