Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Kal
    Kal is offline
    Regular Coder
    Join Date
    Dec 2005
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Week beginning count records

    Hi guys,

    i have a table with a large number of records, the two fields i'm interested in date (timestamp) and status, which can either be Pending, Rejected or Approved.

    when i display there records, is there a way in which i can display a count at the top of the screen.

    but i only want to display the number of Pending, Rejected and Approved status's for the current week (Monday to Sunday). Obviously these numbers will change constantly during the day and start fresh every monday.

    any help would be great.

    thanks inadvance.

  • #2
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts
    Providing you are using an SQL then you should use the filtering in the select query
    You can not say you know how to do something, until you can teach it to someone else.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    This boils down to identifying the most recent Monday expressed in either a MySQL datetime format inside a query, or a unix timestamp format in PHP.

    I would probably identify the date in PHP and then use it in the query.

    Finding the Monday would be something like:

    PHP Code:
    //initialize work week number to today's week day number (0 = sunday, 1 = monday, etc)
    $wkWeekDayNbr date('w');
    //initialize work timestamp to the current timestamp
    $wkTS mktime();

    //do this loop until you get to monday (by subtracting 24 hours from the work timestamp each iteration)
    while ($wkWeekDayNbr != 1) {
        
    $wkTS -= 60 60 24//60 seconds, 60 minutes, 24 hours
        
    $wkWeekDayNbr date('w'000mktime(date('m'$wkTS), date('d'$wkTS), date('Y'$wkTS)));
    }

    $queryTS date('Y-m-d 00:00:00'$wkTS); 
    And then the query would use a GROUP BY clause on status to get the counts:
    PHP Code:
    $query "SELECT count(*)
    FROM my_table
    WHERE date_field >= $queryTS
    GROUP BY status_field"

    (note: I did not test any of this code so there may be corrections needed)


  •  

    Posting Permissions

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