I have a form that selects a date range and pulls information out of a MYSQL database.
The only field used in the database is 'date_created'. The format of the data in this field is for example, 2002-11-29 12:33:43. I use date_format to change the format to year-month, 2007-08 for example.
The first part of the form just sets the range of dates, based on what has been entered into the record_created field. The second part queries the database and displays the data. I have been able to successfully display a list of all the dates using GROUP BY to display unique dates and the totals for each of those. However, when I use the WHERE clause to limit by dates and then select from the drop down menus, it only displays the drop down menus for date ranges and no data. Running the same query from the command line results in errors as well.
When I echo the query, it looks like it is doing what I want it to.
I have used this process with some other pages that call up different fields and it works fine. There is something in the query statement (aliases, GROUP BY ?) that is fouling it up. A spare pair of eyes would be most helpful.
<center> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<center><h2>SEIC Registrants</h2></center>
<form name="f" method="POST" action="seicregistrants1.php">
<center><table cellpadding="10" border="0">
<tr><td>
<b>Beginning</b><br />
<select name="beginning">
<?php
$beginning = $_POST["beginning"];
$ending = $_POST["ending"];
//###########################
// Get range of dates covered
//###########################
mysql_connect("myserver.com", "username", "password") or
die ("Could not connect to mysql database");
mysql_select_db("iii") or
die ("Could not connect to statistics database");
$query = "SELECT distinct date_format(record_created, '%Y-%m') AS dateformatted FROM main WHERE record_created > '' ORDER BY record_created asc";
$result = mysql_query($query) or
die ( mysql_error() );
if ($beginning != "")
{
echo "<option value=\"$beginning\">$beginning</option>\n";
}
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "<option
value=\"$row[dateformatted]\">$row[dateformatted]</option>\n";
if ($beginning == "")
{
$beginning = $row[dateformatted];
}
}
?>
</select>
</td>
<td>
<b>Ending</b><br />
<select name="ending">
<?php
$query = "SELECT distinct date_format(record_created, '%Y-%m') AS dateformatted FROM main WHERE record_created > '' ORDER BY record_created DESC";
$result = mysql_query($query) or
die (mysql_error(mysql_error()));
if ($ending != "")
{
echo "<option value=\"$ending\">$ending</option>\n";
}
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "<option
value=\"$row[dateformatted]\">$row[dateformatted]</option>\n";
if ($ending == "")
{
$ending = $row[dateformatted];
}
}
?>
</select>
</td>
<td><input type="submit" value="submit" /></td>
</tr></table></center>
</form>
<center><table cellpadding="10" border="1">
<tr><td<b>Month</b></td><td><b>Registrants</b></td>
</tr>
//###########################
// Perform query and display data
//###########################
<?php
mysql_connect("myserver.com", "username", "password") or
die ("Could not connect to mysql database");
mysql_select_db("iii") or
die ("Could not connect to user database");
$query = "SELECT DATE_FORMAT(record_created, '%Y-%m') AS date, COUNT(record_created) AS cc FROM main WHERE date >= '$beginning' AND date <= '$ending' GROUP BY date ORDER BY date DESC";
echo $query;
$result = mysql_query($query) or
die ( mysql_error() );
$numrows = mysql_num_rows($result);
$totalcount = 0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<tr>";
echo "<td>" . $row[date] . "</td>";
echo "<td>" . $row[cc] . "</td>";
echo "</tr>";
$totalcount += $row[cc];
}
echo "<tr><td><b>Total</b></td><td><center><b>" . $totalcount . "</b></center></td></tr>";
?>
</table></center>
</body>
</html>
