Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    As a quick note, I believe kicken intended to 'return $result;' instead of 'return $row;' in his getDateCounts() function.

    Comments on this post

    • kicken agrees : Indeed
    Last edited by Triple_Nothing; October 6th, 2017 at 07:59 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #17
  3. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,099
    Rep Power
    4103
    In addition to the mistake Triple_Nothing pointed out, you'll also want to double check that $row['vacationDay'] is a string in the Y-m-d format so that it matches the key value created by the call to $start->format('Y-m-d'). If they don't match exactly you'll get the zero count.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    Thank you,

    I have been studying what you provided.

    Trying to understand what all of it is doing.

    I am able to get a range of a single day or a whole year etc. by echoing
    Single day example:
    Code:
    $start = new \DateTime('2017-10-05');
    $end = new \DateTime('2017-10-06');
    $interval = new \DateInterval('P1D');
    $lookup = getDateCounts($db, $start, $end);
    
    
    for (; $start < $end; $start->add($interval)){
        $date = $start->format('Y-m-d');
        $count = getDayCount($lookup, $date);
    
    
        echo 'On date '.$date.' ; total='.$count['total'].'; officers='.$count['officers']. '<br/>'.PHP_EOL;
    }
    Fixing the getDateCounts() function to $row instead of $result didnt seem to change my output at all. Not sure I understand. I think its putting the entire output of the query into the array $result, rather than a specific $row? And attaching it to a date

    My results are still coming in as Total = 0 and Officer = 0. I checked the date format from the database is default. And my understanding is that Y-m-d is fine with a leading zero in month or day. So that shouldnt be affecting it.
    When I run the sql as a DB query directly and I substitute :start for 2017-01-01 and :end for 2017-12-31 I get expected return.

    I am sure I am missing something simple?
  6. #19
  7. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,099
    Rep Power
    4103
    Originally Posted by SGC3
    Not sure I understand. I think its putting the entire output of the query into the array $result, rather than a specific $row? And attaching it to a date

    The getDateCounts function takes the query result set and builds an array that is indexed by the date returned in the query. You can use var_dump to look at the array which is returned from the function, it should be something like this:
    Code:
    array(2) {
      ["2017-10-05"]=>
      array(2) {
        ["total"]=>
        string(2) "10"
        ["officers"]=>
        string(1) "5"
      }
      ["2017-11-21"]=>
      array(2) {
        ["total"]=>
        string(1) "6"
        ["officers"]=>
        string(1) "1"
      }
    }



    When looking up the key (date) in the array, the key has to match exactly, so whether or not there is a leading zero does matter in that regard. I can't recall if mysql returns dates with leading zeros or not so that's why I suggested you check. If you need to when generating the array you could convert $row['vacationDay'] to a DateTime object then format it to create the key.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  8. #20
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    If the direct query returns the expected result, your query should be working as you desire. Since you mentioned your echo was supplying the expected dates, but just zero for the counts, then odds are something is just being bound incorrectly, assuming the query is truly the same in both instances. As a check, change
    Code:
    $result[$row['vacationDay']] = $row;
    into
    Code:
    $result[] = $row;
    And then after everything is set, after the foreach(), run a
    Code:
    var_dump($result);
    and see how that looks.

    EDIT: @kicken: Is there a purpose the getDayCount function? Isn't the COUNT() and SUM() of the query doing this?
    Last edited by Triple_Nothing; October 6th, 2017 at 08:12 PM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #21
  11. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,099
    Rep Power
    4103
    Originally Posted by Triple_Nothing
    EDIT: @kicken: Is there a purpose the getDayCount function?
    Yes, the purpose is to check if a result exists for a particular day and if not return an array with zeros for the values. It's necessary because the query will only return a record for days that have been added to the database. If nobody has requested a vacation day for 2017-11-01 for example there will be no row for that day and $result['2017-11-01'] would not exist.

    Comments on this post

    • Triple_Nothing agrees : I always forget that which does not exist...
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    Originally Posted by Triple_Nothing
    If the direct query returns the expected result, your query should be working as you desire. Since you mentioned your echo was supplying the expected dates, but just zero for the counts, then odds are something is just being bound incorrectly, assuming the query is truly the same in both instances. As a check, change
    Code:
    $result[$row['vacationDay']] = $row;
    into
    Code:
    $result[] = $row;
    And then after everything is set, after the foreach(), run a
    Code:
    var_dump($result);
    and see how that looks.
    I get this:
    Code:
    array(0) { }
    or NULL if I put it at the end of the file
  14. #23
  15. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Then you might be putting it in the wrong place. This will output everything that was fetched from the database, and stored into that array. Are you placing it similar to below?
    Code:
        $result = [];
        foreach ($stmt as $row){
            $result[] = $row;
        }
    
        var_dump($result);
    
        return $result;
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  16. #24
  17. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,099
    Rep Power
    4103
    You should probably post a complete snapshot of your current code so we know what you're working with and might be able to find any mistakes.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  18. #25
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    I found the problem.

    My connection to the database wasnt correct.

    changing:

    Code:
    $db = new \PDO('mysql:dbname=dbname=;host=localhost', 'username', 'pass');

    to this
    Code:
    $db = new \PDO('mysql:host=localhost;dbname=dbname', 'username', 'pass');
    Yahoo
    Last edited by SGC3; October 7th, 2017 at 08:28 PM. Reason: Found the problem
  20. #26
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    69
    Rep Power
    2
    I am having some difficulty with the if, ifelse.

    When I echo the result as a test I get the correct result for the correct day. But when I try to add it into the if, ifelse statement it isnt giving the correct result.

    Here is what I have been trying:

    Code:
    $start = new \DateTime('2018-10-05');
    $end = new \DateTime('2018-10-06');
    $interval = new \DateInterval('P1D');
    $lookup = getDateCounts($db, $start, $end);
    
    
    for (; $start < $end; $start->add($interval)){
        $date = $start->format('Y-m-d');
        $count = getDayCount($lookup, $date);
    if ($count['total'] >= 5 ) {
        echo $red;
    }  else {
    	echo $green;	
    }
    }PHP_EOL
    I am sticking with just if and else to begin with, until I get it working. Then I will add the ifelse.
  22. #27
  23. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Since I believe you have 3 goals, your format should be similar to:
    PHP Code:
    if () {
      echo 
    '';
    } elseif () {
      echo 
    '';
    } else {
      echo 
    '';

    If you use an if/elseif, then you are best using your larger counts first. Lets say the largest number is to be total, so we would check that first. The next item would be officers, so we would check that second, if total is not to the max. If neither have met their set value, then we would default to the } else { portion of the code. Does this help?
    PHP Code:
    if ($count['total'] >= 5) {
      echo 
    'Total is equal to or greater than 5.';
    } elseif (
    $count['officers'] >= 3) {
      echo 
    'Officers is equal to or greater than 3.';
    } else {
      echo 
    'Both items are less than the value checked for.';

    If this does not work in an instance, could you run a var_dump() on that array and provide us the values being used?
    Last edited by Triple_Nothing; October 11th, 2017 at 05:06 PM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo