CODEWITHSUNDEEP
mysqlselectfetch
Leo
Leo

Reputation: 239

Fetch values from mysql database between year_1 and year_2

Actually I want user to select two years and then I want to fetch those rows from the mysql database which come in between these selected years. For example user select year_1= 2006 and year_2= 2009 Then I want to display all the records between year 2006 and 2009 I guess Code will make it easier to understand

$year_1='2006';
$year_2='2009';    
$sql= "Select names FROM persons WHERE year_1 >= $year_1 and year_1 <= $year_2;

This query is selecting only the names in $year_1 and $year_2 (year 2006 and 2009) but not those in 2007 and 2008; I want to select 2007 and 2008 names also. Tell me how to modify this code?

Upvotes: 0

Views: 461

Answers (4)

hjpotter92
hjpotter92

Reputation: 80639

You want something like:

$sql= "SELECT *
  FROM names
  WHERE year_1 BETWEEN $year_1 AND $year_2";

Upvotes: 1

spencer7593
spencer7593

Reputation: 108410

The simplest explanation is that you have only a single "year" column in your table. (We may be mistakenly implying (from the references to "year_1" and "year_2" in your question) that your table has two separate "year" columns you are wanting to check.)

If you have only one "year" column that you are checking, then it's pretty simple query:

SELECT p.*
  FROM persons p
 WHERE p.year >= $year_1 AND p.year <= $year_2

But your question seems to reference two separate columns: year_1 and year_2, in addition to your two variables $year_1 and $year_2.

So, another possible interpretation of the spec is that the year_1 and year_2 columns are lower and upper bounds of a continuous range, and you want to find rows that satisfy an entire given range of years.

As an example, year_1 might be "birth year" and year_2 might be "death year", and I am wanting to find all rows from the persons table who were "alive" EVERY year in a given range:

SELECT p.*
  FROM persons p
 WHERE p.year_1 <= $year_1 
   AND p.year_2 >= $year_2

(I don't think this is what you're asking, but I'm not sure.)

Another possible interpretation is that I want to find anyone that was "alive" during ANY (but not necessarily all) years in a given range. I'd get that with a query like:

 SELECT p.*
   FROM persons p
  WHERE p.year_1 <= $year_2
     OR p.year_2 >= $year_1

Or, I may be asking a fundamentally different question, looking for an event year during particular range of years.

Like I want to find anyone that was either "born" or that "died" in a given range of years...

  SELECT p.*
    FROM persons p
   WHERE p.year_1 >= $year_1 AND p.year_1 <= $year_2
      OR p.year_2 >= $year_1 AND p.year_2 <= $year_2

But again, it's not clear from your question which (if any) of those result sets you actually want to return.

Upvotes: 0

Gabriel Santos
Gabriel Santos

Reputation: 4974

Try

$year_1 = '2006';
$year_2 = '2009';
$sql = "SELECT names FROM persons WHERE year_1 >= $year_1 AND year_2 <= $year_2";

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

SELECT *
  FROM names
 WHERE (year_1 BETWEEN $year_1 AND $year_2) AND
       (year_2 BETWEEN $year_1 AND $year_2)

Assuming this is how you want to filter it, suppose $year_1 = 2006 and $year_2 = 2009, the filter would look something like:

-- 2006 <-----y1-------------------------y2--> 2009     >> Row returned
-- 2006/y1 <---------------------------------> 2009/y2  >> Row returned
-- <----y1-------2006-------------------y2---> 2009     >> Row filtered out
-- 2006 <---------------y1------2009-----y2-->          >> Row filtered out
-- <-----y1-----------y2------2006-----2009-->          >> Row filtered out
-- <--2006-----2009--------y1-----------y2--->          >> Row filtered out

So only if the range of year_1 to year_2 falls between or on the input years, the row is returned.

Upvotes: 0

Related Questions