Page 1 of 1

Problem returning records in current year

Posted: Fri Feb 18, 2011 4:09 pm
by cjkeane
Hi everyone.

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";
?>
I'd really appreciated anyone's assistance. Thanks.

Re: Problem returning records in current year

Posted: Fri Feb 18, 2011 5:20 pm
by McInfo
The clauses must be in this order: SELECT...FROM...WHERE...GROUP BY...ORDER BY...

Re: Problem returning records in current year

Posted: Sat Feb 19, 2011 7:51 pm
by cjkeane
Thanks. I knew there was a simple answer. It's fixed now!