Solved

MySql Query rows for this year

Posted on 2014-12-21
9
46 Views
Last Modified: 2016-05-25
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
0
Comment
Question by:jporter80
  • 4
  • 3
9 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
There is nothing in your query where you filter on year so

WHERE
....
YEAR(referrals.entered)=2014
....

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Or did you mean for this to be a generic sql for whatever the current year is?

WHERE
....
YEAR(referrals.entered)=YEAR(CURDATE())
....

Open in new window

0
 

Accepted Solution

by:
jporter80 earned 0 total points
Comment Utility
Well maybe i answered my own question... but i just did it this way unless you think it could be better:

$currentyear = date('Y');
$startyear = strtotime("01 January ".$currentyear);
$endyear = strtotime("31 December ".$currentyear);

$numberreferrals = mysqli_query($mysqliglobal,"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' AND referrals.entered > $startyear AND referrals.entered < $endyear ORDER BY referrals.entered DESC");
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
I think it's easier going the sql route, and just pass in the year from php using
$year = date("Y");
...
WHERE
....
YEAR(referrals.entered)=$year
....

Open in new window



Doing a date > and date < is just wasted code.
Plus the way you have it it would be
year > 2014 and year < 2014 = nothing
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jporter80
Comment Utility
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 <
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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 <=
0
 
LVL 9

Expert Comment

by:Brian Tao
Comment Utility
Just a reminder: use >= and <= otherwise you'll miss the data from the first and the last day of the year!
0
 

Author Comment

by:jporter80
Comment Utility
ahhh.. thanks for the catch
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now