www.webdeveloper.com
Results 1 to 3 of 3

Thread: In need of help with a query

  1. #1
    Join Date
    Jun 2010
    Location
    UK
    Posts
    2

    In need of help with a query

    Hi

    I'm working on a client management system and one of the report requirements has got the better of me.

    I have a table which is used to log all meetings with a client, the relevant columns being id, clientid & datetime.

    I need to generate a report which shows years and months with the number of clients who had their 4th meeting in each.

    I know the logic is to get the clientid and date of their 4th meeting then count clientids grouped by year & month of that meeting but have got myself lost in a mess of queries, subqueries and groupings.

    The main issue I have is getting the clientid and date of 4th meeting, some have less than 4 and shouldn't be counted at all, some more and should only be counted on the date of the 4th, I'm beginning to think some kind of loop may be required.

    Any help would be greatly appreciated, thanks, Steve

  2. #2
    Join Date
    Jul 2009
    Posts
    56
    Without seeing your table structure and some sample data...its hard to know what you are trying to accomplish. But I think I get it.

    My suggestion is to use the command GROUP BY and group things by clientID but also do a ORDER BY datetime ASC

    If you want to make a report listing like:

    January
    Bob Smith (131)
    February
    Susie Thompson (243)
    Jack Horton (845)
    March
    ...etc...etc

    Then I would first turn the month listings into an array (because date function requires 2 digits (01, 02, 03, 04, 05...) and a while or for loop only starts with 1 digit (0, 1, 2, 3, 4, 5...). Then do something like

    PHP Code:
    for ($i 0$i count($array), $i++) {
         
    //for visual understanding purposes
       
    $month $array[$i];
        
    $sql "SELECT * FROM meetings WHERE MONTH(mtgdate) = $month GROUP BY clientID ORDER BY mtgdate";
        
    $qry mysql_query($sql);
        
    //----an so forth

    I think that code will work. IF not forgive me. But maybe it will at least point u in the right direction.

    //------END REPLY

  3. #3
    Join Date
    Jun 2010
    Location
    UK
    Posts
    2
    Hi

    Many thanks for that.

    It's not quite what I was looking for, I've been reading MySQL documentation and forums for days but still haven't been able to come up with an SQL only solution, have gained a lot of valuable knowledge for other projects though.

    I've now taken your suggestion and used php to create a couple of loops and bring everything together, it's really not pretty and I'm sure there must be a better way but the job's done and the report is only generated once a month so efficiency is not a huge concern.

    Thanks again for your help

    Steve

Thread Information

Users Browsing this Thread

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

Posting Permissions

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



Recent Articles