[RESOLVED] Compare Form Variables to Parts of MySQL DATE field

[rsmith]

Hey all. I have a form that allows a user to specify a month and year as a range for generating a report. The month and year are in xx and xxxx format respectively so as to coincide with the MySQL DATE field. What I need help with is how do I take the two variables, once passed to the script that will generate the report, and return result records that match the given range. I.E. if $year = MySQL YEAR && $month = MySQL MONTH of the DATE.

The DATE field is populated using the YYYY-MM-DD format generated by the date("Y-m-d"); PHP statement.

I hope this was clear to understand. Thanks in advance!

[devinemke]

SELECT * FROM table WHERE YEAR(date_field) = '$year' AND MONTH(date_field) = '$month'

[rsmith]

Thanks man. I'm going to give this a try when I get home from work and I'll let you know!

[rsmith]

Thanks for your help again. My script is below. So far I get no error codes but i also do not get any results. The script runs through without any invalid syntax. When I print out the mysql_error(); I get no error messages. Am I missing something obvious?

<?
	//Get the variables for the report you're generating
		$sortby = $_GET['sortby'];
		$type = $_GET['type'];
		$mss_year = $_GET['mss_year'];
		$mss_month = $_GET['mss_month'];

	if(!isset($sortby)){
		$sortby = "sid";
	}
?>
<?
    	//Run query for Monthly Service Summary
			if(isset($type)){
				if($type == "mss"){
					if($mss_month == "MONTH"){
						echo "<center>You have not selected a valid month, please go back and try again.<br />";
						echo "<a href='javascript:history.go(-1);'>back</a></center>";
						exit();
				}elseif($mss_year == "YEAR"){
						echo "<center>You have not selected a valid year, please go back and try again.<br />";
						echo "<a href='javascript:history.go(-1);'>back</a></center>";
						exit();
					}
					else{
						$query = "SELECT * FROM service WHERE YEAR(sentered) = '".$mss_year."' AND MONTH(sentered) = '".$mss_month."' ORDER BY $sortby";
						$result = mysql_query($query);

					echo "<table align='center' width='400' cellspacing='5' cellpadding='0' border='0'>";
					echo "<tr><td valign='top' class='formheader' width='50'><a href='report.php?type=".$type."&mss_month=".$mss_month."&mss_year=".$mss_year."&sortby=sid'><b>SID</b></a></td><td valign='top' class='formheader' width='200'><a href='report.php?type=".$type."&mss_month=".$mss_month."&mss_year=".$mss_year."&sortby=make'><b>Manufacturer</b></a></td><td valign='top' class='formheader' width='100'><a href='report.php?type=".$type."&mss_month=".$mss_month."&mss_year=".$mss_year."&sortby=model'><b>Model</b></td><td valign='top' class='formheader' width='50'><a href='report.php?type=".$type."&mss_month=".$mss_month."&mss_year=".$mss_year."&sortby=sentered'><b>Entered</b></a></td></tr>";

					while($svc_info = mysql_fetch_assoc($result)){
						echo "<tr><td>".$svc_info['sid']."</td><td>".$svc_info['make']."</td><td>".$svc_info['model']."</td><td>".$svc_info['sentered']."</td></tr>";

					echo "</table>";
				}
			}
		}
	}
?>

[rsmith]

I got it! Just took some mindless tinkering. Gotta love how walking away from a problem for a few hours gives you new perspective!