Results 1 to 7 of 7

Thread: Mysql complex ordering

  1. #1
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Mysql complex ordering

    Ok, so it might not be complex to those in the know, but it's certainly more than what I'm used to!

    Ok so table 'programme' has 4 related entries in a sub table 'followup'. Each of the followup entries has a date specified for them, 1 week, 1 month, 3 months and 1 year from the date of the initially created programme entry. The followup table also has a 'completed' tinyint(1) to show whether the relative entry has been completed by staff.

    This data is to be presented in the following way (this part I'm ok with)...

    Code:
    programme name | 1 week date | 1 month date | 3 month date | 1 year date
    ------------------------------------------------------------------------
    Woop           | 01/03/2008  | 23/03/2008   | 23/05/2008   | 23/02/2008
    Blah           | 02/03/2008  | 24/03/2008   | 24/05/2008   | 24/02/2008
    Poop           | 10/03/2008  | 03/04/2008   | 03/06/2008   | 03/03/2008
    where each of the dates is retrieved from the sub table. Now the tricky bit, is I would like to order it by the lowest incomplete followup date. So assuming none of the follow ups have been completed, Woop will appear first. However, if Woop's 1 week followup is completed, Blah will be top of the list (because it has the lowest incomplete followup date). If each entry has its 1 week followup complete and Woop has its 1 month follow up complete, Blah will appear first. etc etc.

    Hopefully that makes sense. Is that possible?

    I was thinking of just using a single sub entry to hold all 4 followup dates, but what like to see if this way is possible first. Cheers.

  2. #2
    Senior Member manwithnoname's Avatar
    Join Date
    Dec 2005
    Posts
    1,050
    Thanks
    17
    Thanked
    26 times in 25 posts

    Re: Mysql complex ordering

    I might not fully understand your data but it appears the dates should not be store in the same row (the way the data is presented to a user does not have to be reflected in the underlying tables - you just need to be able to extract it in the required format). If you add the completed field to the table designs it become more apparent these 2 items of data should be grouped also consider if more dates were required say 6 month date or 2 year date could these changes be easily accommodated?

    Would this work: a table, I'll call it program_dates, with the following fields:

    program name (char)
    type (char)
    prog_date (date)
    complete (char Y or N) -- you mention tinyint(1) if this allows qualifier of TRUE or FALSE use that if not read something = 'Y or 'N' is more read able than = 0 or = 1.

    so each program would take 4 entries
    Code:
    programme_name | type         | prog_date  |complete
    ----------------------------------------------------------------
    Woop           | 1 week date  | 01/03/2008 | N
    Woop           | 1 month date | 23/03/2008 | N
    Woop           | 3 month date | 23/05/2008 | N
    Woop           | 1 year date  | 23/02/2008 | N
    Blah           | 1 week date  | 02/03/2008 | N
    etc
    so to list the un-complete ( complete ='N') order by date

    Code:
    select   programme_name, 
             type ,
             prog_date  
    from     program_dates
    where    complete ='N'
    order by prog_date

    The last thing to do is extract the data in your required format - can mysql do a pivot?
    If not you can join table programme_name to itself

    Code:
    select   d1weekdate.programme_name, 
             d1weekdate.prog_date,  /* '1 week date'  */
             d1monthdate.prog_date  /* '1 month date' */
             d3monthdate.prog_date  /* '3 month date' */
             d1yeardate.prog_date  /* '1 year date' */
    from     program_dates d1weekdate, 
             program_dates d1monthdate,
             program_dates d3monthdate,
             program_dates d1yeardate,
    where    d1weekdate.programme_name = d1monthdate.programme_name /* table join */
    and      d1weekdate.programme_name = d3monthdate.programme_name /* table join */
    and      d1weekdate.programme_name = d1yeardate.programme_name  /* table join */
    and      d1weekdate.type = '1 week date'
    and      d1monthdate.type = '1 month date'
    and      d3monthdate.type = '3 month date'
    and      d1yeardate.type = '1 year date'
    Last edited by manwithnoname; 01-03-2008 at 12:26 AM. Reason: grammar

  3. Received thanks from:

    Raz316 (02-03-2008)

  4. #3
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: Mysql complex ordering

    nice one, thanks for responding. actual displaying of the data is fine, though the issue is being able to order them by the lowest open date from the sub program_dates table

  5. #4
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: Mysql complex ordering

    I think it will probably be easiest if I do the ordering via a php array now. It makes a fair bit of sense seeing as I'm presenting the final table form from an array anyway.

    Cheers though!

  6. #5
    Senior Member manwithnoname's Avatar
    Join Date
    Dec 2005
    Posts
    1,050
    Thanks
    17
    Thanked
    26 times in 25 posts

    Re: Mysql complex ordering

    May be I shouldn't try and get you to redesign a database, was stuck in job mode when i posted above.

    In general if you need to sort a multiple fields within a row of a table (or fields from multiple tables for that matter) you need to build up the list using the union statement then order the list

    It's not mysql syntax, and I'm not sure how you are represented something that is complete.

    Code:
    select   programme name,
             1_week_date,
             'This is a 1 week date' date_type
    from     followup
    where    completed = false /* check */
    union
    select   programme name,
             1_month_date,
             'This is a 1 month date' date_type
    from     followup
    where    completed = false /* check */
    union
    select   programme name,
             3_month_date,
             'This is a 3 month date' date_type
    from     followup
    where    completed = false /* check */
    union
    select   programme name,
             1_year_date,
             'This is a 1 year date' date_type 
    from     followup
    where    completed = false /* check */
    order by 2 asc /* sort by the 2nd field, date in this case, in ascending order*/
    The 3rd field, I have give the alias 'date_type', might not be needed, it indicates which of the four types of date the row refers to, the results would look like this:

    Code:
    Woop           | 01/03/2008 | This is a 1 week date
    Blah           | 02/03/2008 | This is a 1 week date
    Poop           | 10/03/2008 | This is a 1 week date
    Woop           | 23/03/2008 | This is a 1 month date
    Blah           | 24/03/2008 | This is a 1 month date
    Poop           | 03/04/2008 | This is a 1 month date
    You mention using php - go with what you know you might just be wasting your time using any part of the above.

  7. Received thanks from:

    Raz316 (03-03-2008)

  8. #6
    Funking Prink! Raz316's Avatar
    Join Date
    Jul 2003
    Location
    Deal, Kent, UK
    Posts
    2,978
    Thanks
    130
    Thanked
    62 times in 52 posts

    Re: Mysql complex ordering

    thanks once again for taking the time to reply. I hadn't even considered union and that looks like it can do what I was originally hoping for. The concept of selecting from the same table using different alias' was something I wasn't aware of either, so thanks for that too!

  9. #7
    Pup
    Pup is offline
    woof
    Join Date
    Apr 2006
    Location
    West London
    Posts
    265
    Thanks
    18
    Thanked
    19 times in 15 posts
    • Pup's system
      • Motherboard:
      • Asus P8Z68-V Pro
      • CPU:
      • i7-2600K
      • Memory:
      • 8GiB Corsair Vengeance LP DDR3 1600MHz
      • Storage:
      • 2 x 1TB Samsung Spinpoint F3
      • Graphics card(s):
      • 2 x MSI GTX 560 Ti Twin Frozr II 1GiB
      • PSU:
      • Corsair HX850W
      • Case:
      • Antec P182
      • Operating System:
      • Windows 7 Professional x64
      • Monitor(s):
      • HP LP2475w & Iiyama Prolite E435s
      • Internet:
      • Virgin Media 20mb

    Re: Mysql complex ordering

    Maybe I have misunderstood how your datatables are setup, or there is something I would realise If i had the same structure setup to test with, but surely all you need to do is order by your flags for each date and then the date.

    So if you had a (0/1)flag associated with each date, then :

    Order By 1wflag, 1mflag, 3mflag, 1yflag, 1wdate

    or if your flags are combined into a single incrementing tinyint, then just :

    Order By flag, 1wdate

    Dont shoot me down in flames, but please correct me if I have misunderstood

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help connecting to a remote MySQL server...
    By chicken in forum Software
    Replies: 10
    Last Post: 27-11-2007, 11:52 AM
  2. MySQL extension for PHP 5.2.3 not working
    By Jerrythafast in forum Help! Quick Relief From Tech Headaches
    Replies: 18
    Last Post: 13-06-2007, 08:03 PM
  3. Making PHP and MySQL Talk?
    By Dav0s in forum Software
    Replies: 4
    Last Post: 18-09-2005, 10:53 PM
  4. mySQL problems
    By Vini in forum Software
    Replies: 2
    Last Post: 22-08-2005, 01:01 PM

Posting Permissions

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