#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0

    Mysql query to display records from march 2013 to may 2013


    Hi experts,

    I am struggling for a query to retreive data from the mysql database.

    I have a table with the below records. I am looking for a query that displays ALL THE RECORDS from march 2013 to may 2013.

    Code:
    p_month    p_year
    
    march        2013
    march        2013
    april          2013
    may          2013
    june          2013
    february     2012
    may          2013
    march        2013
    april 2        2013
    augest       2012

    If I write an mysql query to print all the records from march 2013 to june 2013 then output should be

    Code:
    march    2013
    march    2013
    march    2013
    april       2013
    april       2013
    april       2013
    may       2013
    If I write an mysql query to print all the records from april 2013 to june 2013 then output should be

    Code:
    april       2013
    april       2013
    april       2013
    may       2013
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by giridhar276
    I am looking for a query that displays ALL THE RECORDS from march 2013 to may 2013.
    because of the choice you made in how to represent the data, you have almost no flexibility in writing a simple query
    Code:
    SELECT p_month
         , p_year
      FROM daTable
     WHERE p_year = 2013
       AND p_month IN ( 'march','april','may')

    Comments on this post

    • aitken325i agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    3
    Rep Power
    0
    Thanks r937..

    Here the problem is I am going to pass any two months from the web interface to the backend mysql database. The months can be any two months depending on selection.

    If i pass january and march 2013 then all the months in between january and march( including jan and march ) has to be displayed..


    Thanks once again....
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    Originally Posted by giridhar276
    Here the problem is I am going to pass any two months from the web interface to the backend mysql database.
    The problem is what r937 already said: Your way of storing time information is totally inappropriate.

    MySQL doesn't know that your "march" comes after "january", so every time you wanna select a time range, you first have to build a complete list of month names -- which is a disaster. Things get even worse if your range covers multiple years. Then you'll end up with monstrosities like

    Code:
    WHERE
    	(
    		p_month IN ('october', 'november', 'december')
    		AND p_year = 2011
    	)
    	OR p_year IN (2012, 2013)
    	OR (
    		p_month IN ('january', 'february', 'march', 'april')
    		AND p_year = 2014	
    	)
    Simply use DATEs with the day set to the first of the month. Then you can use the standard comparison functions without any need for weird workarounds.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo