Problem returning records in current year

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Problem returning records in current year

Post 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.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Problem returning records in current year

Post by McInfo »

The clauses must be in this order: SELECT...FROM...WHERE...GROUP BY...ORDER BY...
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: Problem returning records in current year

Post by cjkeane »

Thanks. I knew there was a simple answer. It's fixed now!
Post Reply