Return 2 dates from single combo box to query MYSQL db

Question

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?

Here is my code:

<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>

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

Answer 1

Two options could be:

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];

OR

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') 

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

(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)

Answer 2

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>'; 
}

}

In the HTML Form leave it simple

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