I'm trying to find all records in the current year. my query returns the error:
SELECT Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Year(DateRecorded)=year(now())' at line 3
Code: Select all
<?php
/* db setup*/
$db="db";
$link = mysql_connect('localhost', 'root', 'pw');
if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query("SELECT IDNumber, CompanyName, CompanyBranch, CompanyReferenceNumber, Status, AssistanceStatus, AdministrationStatus, BilledStatus, NegotiationsStatus, DatabaseStatus, TransitFileStatus, CaseOwnerSelect, DateRecorded, DateClosed, year(DateRecorded) as Year FROM records
GROUP BY MegaCaseNumber, CompanyName, CompanyBranch, CompanyReferenceNumber, Status, AssistanceStatus, AdministrationStatus, BilledStatus, NegotiationsStatus, DatabaseStatus, TransitFileStatus, CaseOwnerSelect, DateRecorded, DateClosed
ORDER BY MegaCaseNumber WHERE Year(DateRecorded)=year(now())")
or die("SELECT Error: ".mysql_error());
//$num_rows = mysql_num_rows($result);
//print "There are $num_rows records.<br>";
$numofrows = mysql_num_rows($result);
?>
<?php
echo "<TABLE width=\"100%\" BORDER=\"0\" cellpadding=\"2\" cellspacing=\"2\">\n";
echo "<TR ><TD nowrap>ID #</TD><TD nowrap>Status</TD><TD nowrap>Beneficiary</TD><TD nowrap>Principal</TD><TD nowrap>Principal Branch</TD><TD nowrap>Principal Ref</TD><TD nowrap>Assistance</TD><TD nowrap>Administration</TD><TD nowrap>Billed</TD><TD nowrap>Negotiation</TD><TD nowrap>Database</TD><TD nowrap>Transit</TD><TD nowrap>Ownership</TD><TD nowrap>Recorded</TD><TD nowrap>Closed</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR >\n";
} else { //if there isn't a remainder we will do the else
echo "<TR > \n";
}
echo "<TD nowrap><a href=\"edit.php?id=".$row['IDNumber']."\">".$row['MIDNumber']."</a></TD><TD nowrap>".$row['Status']."</TD><TD nowrap>".$row['Beneficiary']."</TD><TD nowrap>".$row['CompanyName']."</TD><TD nowrap>".$row['CompanyBranch']."</TD><TD nowrap>".$row['CompanyReferenceNumber']."</TD><TD nowrap>".$row['AssistanceStatus']."</TD><TD nowrap>".$row['AdministrationStatus']."</TD><TD nowrap>".$row['BilledStatus']."</TD><TD nowrap>".$row['NegotiationsStatus']."</TD><TD nowrap>".$row['DatabaseStatus']."</TD><TD nowrap>".$row['TransitFileStatus']."</TD><TD nowrap>".$row['CaseOwnerSelect']."</TD><TD nowrap>".$row['DateRecorded']."</TD><TD nowrap>".$row['DateClosed']."</TD>\n";
echo "</TR>\n";
}
//close table
echo "</TABLE>\n";
?>