jporter80
asked on
MySql Query rows for this year
I have a query that i need to get only the rows that are for the current year.
The column that has the unix time is referrals.entered
here is the current query:
SELECT referrals.*, to_person.fname as to_fname, to_person.lname as to_lname, from_person.fname as from_fname, from_person.lname as from_lname FROM referrals LEFT JOIN person as from_person ON (from_person.ID = referrals.from_ID) LEFT JOIN person as to_person ON (to_person.ID = referrals.to_ID) LEFT JOIN chapter_relation as from_relation ON (from_person.ID = from_relation.person_ID) LEFT JOIN chapter_relation as to_relation ON (to_person.ID = to_relation.person_ID) WHERE from_relation.chapter_ID = to_relation.chapter_ID AND from_relation.chapter_ID = '$chapterid' ORDER BY referrals.entered DESC
The column that has the unix time is referrals.entered
here is the current query:
SELECT referrals.*, to_person.fname as to_fname, to_person.lname as to_lname, from_person.fname as from_fname, from_person.lname as from_lname FROM referrals LEFT JOIN person as from_person ON (from_person.ID = referrals.from_ID) LEFT JOIN person as to_person ON (to_person.ID = referrals.to_ID) LEFT JOIN chapter_relation as from_relation ON (from_person.ID = from_relation.person_ID) LEFT JOIN chapter_relation as to_relation ON (to_person.ID = to_relation.person_ID) WHERE from_relation.chapter_ID = to_relation.chapter_ID AND from_relation.chapter_ID = '$chapterid' ORDER BY referrals.entered DESC
Or did you mean for this to be a generic sql for whatever the current year is?
WHERE
....
YEAR(referrals.entered)=YEAR(CURDATE())
....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think it's easier going the sql route, and just pass in the year from php using
Doing a date > and date < is just wasted code.
Plus the way you have it it would be
year > 2014 and year < 2014 = nothing
$year = date("Y");
...
WHERE
....
YEAR(referrals.entered)=$year
....
Doing a date > and date < is just wasted code.
Plus the way you have it it would be
year > 2014 and year < 2014 = nothing
ASKER
im using unix time so the variables declared before query are getting unix time of the beginning of the current year and the end of the current year. Now im querying everything in between with > or <
Ahh true - thought you were querying against the year, still it would be easier and less code to just query on the year as exampled here http:#a40512263
edit.
Your code is still > than the 1st of Jan and < 31st Dec which means the 2nd Jan til the 30th Dec
Should >= and <=
edit.
Your code is still > than the 1st of Jan and < 31st Dec which means the 2nd Jan til the 30th Dec
Should >= and <=
Just a reminder: use >= and <= otherwise you'll miss the data from the first and the last day of the year!
ASKER
ahhh.. thanks for the catch
Open in new window