SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Query problem! help needed

    Hi there people!

    i have 2 tables that hold data for a event schedule

    table(months) stores the months of the year on these fields
    m_id | m_month | m_status
    1 | january | online
    2 | february | online
    etc...

    the m_status is either online or offline


    table(events) stores the events
    ev_id | ev_m_id | ev_day | ev_place | ev_status
    1 | 1 | 1 | here | online
    1 | 1 | 6 | there | online
    1 | 2 | 3 | here | online
    1 | 2 | 7 | here | online

    ev_m_id is the id of the month

    and what i need to do is: to get the last 3 scheduled events.

    and i did the following:

    SELECT m_id, m_status FROM months WHERE m_id<'13' AND m_status='online' ORDER BY m_id DESC LIMIT 2;

    this gets me the last 2 online months....

    the result of this query is stored in a PHP variable to be used in the next query.

    and

    SELECT ev_day FROM events WHERE ev_m_id='$m_id' AND ev_day<'32' ORDER BY ev_day DESC LIMIT 3;

    and this gets me the last 3 scheduled events on the months that i got before...

    but if there are eg. 3 events in january and 2 in february i get 5 events and i just want to get the last 3, eg. the 2 from february and the last one in january...

    hope anyone can help me with this

    thnx in advance
    Last edited by zbing; Jul 25, 2003 at 07:08.
    zbing

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT ev_dia FROM ag_eventos WHERE ev_day<'32' ORDER BY ev_m_id, ev_dia DESC LIMIT 3;

  3. #3
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your swift reply sylow but i think i didnt make myself as clear as i should have.

    the result from the first query gets me the latest online months and is stored in a PHP variable ($m_id) that i used to get just the relevant (online) results in the secon query.

    my problem is that if both latest months have 3 scheduled events i get 6 events in the second query, because (i think) its getting me the results from the first month that's online and the results from the second month that's online.

    and what i needed was just the latest 3 events.

    eg. if august is online and has 2 events and
    july is online and has 4 events, i need the 2 events from august and the last one in july.

    eg. if august is online and has 3 events and
    i need the 3 events from august.

    just the last 3.

    i dont if this is possible but is there a way of "grouping" the results from the second query so that i can apply the LIMIT to all of them, instead of LIMITing the reslts from month1 and month2 separately?
    zbing

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hmmm, this thread seems familiar

    i just answered a question remarkably like it over here --
    http://www.sitepointforums.com/showt...hreadid=120139

    rudy

  5. #5
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Closing this thread. Please don't double post again.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com


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
  •