SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    List fields per dates

    Hi there,

    I am working to create a matrix style output similar to https:secure.sgs.gov.sg/apps/fda/sgsBondsForm.jsp

    Notice that after running the query, you have fields across colummn, date across rows (all calender dates) and values in (row,col) format.

    Ii have a database Prices i.e.

    px_history

    filed_name, date, close_px

    Can u help me to provide a mySQL query and any PHP processing code, where I can get prices for different fields for a date range e.g. start, end date.

    Form:
    ------
    start date :_______
    end date L: ________

    final output
    ------------
    e.g. Jan 01, 2009 to Feb 01, 2009

    It should print all dates b/w start and end, i.e.

    Dates Field 1 Field 2
    01/01/2009 close_px <empty if there is no record>
    01/01/2009 close_px close_px
    ...
    ...
    01/02/2009
    .....

    if you notice, we have FIELDS on the Columns and DATES on the Rows and DATA (close_px) on each columns for each field, It is kind of matrix.

    I've trying to get this works for weeks and months, but could not found a good solution.

    Any guidance will be highly appreciated..

    Many Many Many Many Many Many Thanks....

  2. #2
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you have 2 date fields in your database? If so, which field is the start date which is the end date?

    Are you entering the dates into the database using PHP time function?
    :-)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    google cross tab query

    real easy to do in MS Access, a royal pain in other database systems

    it is far better to do this cosmetic re-arranging of data in the front-end application language than in the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •