Solved

MySql Query rows for this year

Posted on 2014-12-21
9
52 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40512250
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
ID: 40512252
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
ID: 40512259
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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 58

Expert Comment

by:Gary
ID: 40512263
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
 

Author Comment

by:jporter80
ID: 40512265
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
ID: 40512268
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
ID: 40512269
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
ID: 40512274
ahhh.. thanks for the catch
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

739 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