Searching MYSQL Database Entries By Time Period

Searching MYSQL Database Entries By Timeframe

With the following form we are trying to filter the results from a period of 1 month. Since the date in the database uses the typical date format 2012-01-13, we can grab the date and month post variable and append the first date of the month and last date of the month. The last date 31 would work for any month because the last 2 digits of the date never exceed 31.

This example uses a table to output data.

<?php

if (count($_POST) > 0) {
$start_date=$_POST['date'].'-'.$_POST['month'].'-00';
$end_date=$_POST['date'].'-'.$_POST['month'].'-31';
} ?>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<select name="date">
<option value="2012">2012</option>
<option value="2011">2011</option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
</select>

<select name="month">
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<input type="submit" name="submit" value="submit" /></form>
<div>

<?php
echo '<table align="center" cellspacing="0" cellpadding="5">
<tr>
<td align="left"><b># Entries</b></td>
<td align="left"><b>Name</b></td>
<td align="left"><b>Username</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Phone Number</b></td>
<td align="left"><b>Date</b></td>

</tr>';
$command= "SELECT member_id, name, username, email_address, phone_number, date FROM tablename WHERE date>='$start_date' AND date<='$end_date' ORDER BY date desc";
$result = mysqli_query($db, $command);
if ($result && mysqli_num_rows($result) > 0) {
$number = 0;
while ($row = mysqli_fetch_assoc($result)) {
$number = $number + 1;

$bg =($bg=='#DCEDE5' ? '#f9f9f9' : '#DCEDE5');
echo '<tr bgcolor="' . $bg . '">
<td align="left">' .$number. '</td>
<td align="left">' .$row["name"]. '</td>
<td align="left">' .$row["username"]. '</td>
<td align="left">' .$row["email_address"]. '</td>
<td align="left">' .$row["phone_number"]. '</td>
<td align="left">' .$row["date"]. '</td>
</tr>';
}
echo '</table>';
}
?></div>