MYSQL Query woes

Posted on 2011-10-17
Last Modified: 2013-12-12
I am at my wits end and could really do with some expert advise. Let me try to describe my issue.

I am trying to generate a PHP table that displays period data. Each year has 13 periods, each period having 4 weeks.

Hence we have a period table with fields


and a period data table with fields

periodnumber (references previous table)
weeknumber (a number from 1 to 4)

Period refererences another table which has the perdio name, financial year etc.

However I want to generate a query that will generate a table with the current period (weeks 1 to however many there are) and the last period (4 weeks)

I have been able to generate a routine that can easily pull off the latest period data with the following

FROM mfugi_kpi_perioddata
WHERE period1.periodnumber =
FROM mfugi_kpi_period

this simply takes the latest period from the period table, and uses that as a reference to pull off the latest period data. However, I have no clue how to pull of the period before, and also clearly distinguish these results for easy display later on a PHP generated table. I have tried the IN statement just before the nested query, but am told my version of MYSQL does not allow it.

Any pointers would be appreciated. The query above is the closest I have come, I have had many complicated abortive attempts not worth posting.
Question by:McDeathUK
    LVL 3

    Expert Comment

    may you write more clearly your problem what you want to get from tables. Is that the current period or some other range? Is there needed to group bay something or special ordering?
    LVL 142

    Accepted Solution

    what about:
    SELECT *
    FROM mfugi_kpi_perioddata
    WHERE period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period )
       OR period1.periodnumber = ( SELECT MAX(id) FROM mfugi_kpi_period WHERE id < ( SELECT MAX(id) FROM mfugi_kpi_period ) )

    Open in new window


    Author Closing Comment


    Works like a charm! Thank you so much. I have only done basic testing, but I setup three periods, added a load of data.. and this query successfuly brough off the latest period and data, and the previous data and data.

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    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 …

    761 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

    15 Experts available now in Live!

    Get 1:1 Help Now