Confused about differing PHP results on Local Vs Host servers

I have a script for a site which displays an events calendar. It pulls the data from a database, builds a multidimensional array, grouping the events by year, then displays them on the page as nested lists from the array.
I use a for loop to iterate through years, then run an sql query to fetch events for each year, build an array of events for the year, then add the year to a parent array of years.
There is an if ($sql->rowCount()) condition to avoid adding empty years with no events.

It all works perfectly on my local environment running on WAMP.
But I noticed on the host server, it is adding an empty year to the list, so you see the heading “Events in 2018” followed by nothing, because there are no events in that year. But the row count clause should stop that entry in the array ever existing.

This is not the real script, but a cut down and modified version for debugging.

    // connect
    require_once "include/conn/ro.php" ;
    
	echo '<p>PHP version: '.phpversion().'</p><hr>';
	
    $nyears = 2 ; // How many years ahead to check
    $years = NULL;
    $thisyear = date('Y');
    $today = date('Y-m-d');
    $lastweek = date('Y-m-d', strtotime('-1 week'));
    $nextyear = $thisyear + 1;
    $maxyear = $thisyear + $nyears ;
    $dayone = '-01-01' ;
    
    try{
		$fields = 'name, dtstart, dtend' ; // Name, date start and date end
		$sql = $db->prepare("SELECT $fields FROM events WHERE Status != 'f' AND dtend BETWEEN :start AND :end ORDER BY dtstart, tmstart" );
		
		for( $y = $thisyear, $n = $nextyear; $y < $maxyear; $y++,  $n++ ) { // Group events by year
			
			if( $y == $thisyear ) { $start = $lastweek; }
			else{ $start = $y.$dayone; }
			$end = $n.$dayone;
			
			if($sql->execute([':start' => $start, ':end' => $end])) { echo "<p>Query Good!</p>\n" ; }
			else { echo "<p>Query Bad!</p>\n" ; }
			echo '<p>Query Start date: '.$start.'. Query End Date: '.$end.'</p>' ;
			echo '<p>Row Count: '.$sql->rowCount().'</p>' ;
			if ($sql->rowCount()) {
			
				while($row = $sql->fetch()) {
					$temp['name'] = htmlspecialchars($row['name']) ;
					$temp['start'] = $row['dtstart'] ;
					$temp['end'] = $row['dtend'] ;
					
					$ytemp[] = $temp;
					$temp = NULL;
				} // end while
				$years[$y] = $ytemp;
				$ytemp = NULL;
				echo '<p>This year '.$y.'</p><p>next year '.$n.'</p><hr>';
			} // end if count
			
		} // End for
    }
    catch(PDOException $e) {echo "DB Error: ".$e;}
	
	echo "<pre>\n".var_dump($years)."\n</pre>\n";    // See the array

By running this version I see the difference, but don’t understand why it’s different.
Locally the result is like this, as I would expect.

PHP version: 5.6.25


Query Good!

Query Start date: 2017-05-06. Query End Date: 2018-01-01

Row Count: 4

This year 2017

next year 2018


Query Good!

Query Start date: 2018-01-01. Query End Date: 2019-01-01

Row Count: 0

D:\Site\public_html\test-events.php:49:
array (size=1)
2017 =>
array (size=4)
0 =>
array (size=3)
‘name’ => string ‘Event A’ (length=19)
‘start’ => string ‘2017-07-01’ (length=10)
‘end’ => string ‘2017-07-02’ (length=10)
1 =>
array (size=3)
‘name’ => string ‘Event B’ (length=9)
‘start’ => string ‘2017-08-18’ (length=10)
‘end’ => string ‘2017-08-28’ (length=10)
2 =>
array (size=3)
‘name’ => string ‘Event C’ (length=35)
‘start’ => string ‘2017-09-16’ (length=10)
‘end’ => string ‘2017-09-17’ (length=10)
3 =>
array (size=3)
‘name’ => string ‘Event D’ (length=28)
‘start’ => string ‘2017-11-05’ (length=10)
‘end’ => string ‘2017-11-05’ (length=10)

^ There are 4 events in 2017 and none in 2018.

Then on the host:-

PHP version: 5.6.30


Query Good!

Query Start date: 2017-05-06. Query End Date: 2018-01-01

Row Count: 4

This year 2017

next year 2018


Query Good!

Query Start date: 2018-01-01. Query End Date: 2019-01-01

Row Count: 4

This year 2018

next year 2019


array(2) { [2017]=> array(4) { [0]=> array(3) { ["name"]=> string(19) "Event A" ["start"]=> string(10) "2017-07-01" ["end"]=> string(10) "2017-07-02" } [1]=> array(3) { ["name"]=> string(9) "Event B" ["start"]=> string(10) "2017-08-18" ["end"]=> string(10) "2017-08-28" } [2]=> array(3) { ["name"]=> string(35) "Event C" ["start"]=> string(10) "2017-09-16" ["end"]=> string(10) "2017-09-17" } [3]=> array(3) { ["name"]=> string(28) "Event D" ["start"]=> string(10) "2017-11-05" ["end"]=> string(10) "2017-11-05" } } [2018]=> NULL }

-----

For some reason the row count is still set at 4, it has not been reset to 0 when no events were found for 2018.
So 2018 is in the array, but has a NULL value.
Why is that?
Yes the PHP version is slightly different, 5.6.25 Vs 5.6.30
Another strange anomaly, on the host, the var dump is not within the <pre> tags.

That seems to suggest it’s not the same script. I don’t see how it can do the var_dump() without the <pre>.

Might it be the different versions - 5.6.25 to 5.6.30 ?

The PHP 5 ChangeLog under 5.6.26 has
http://php.net/ChangeLog-5.php

PDO:
Fixed bug #60665 (call to empty() on NULL result using PDO::FETCH_LAZY returns false).

The fixed bug is
https://bugs.php.net/bug.php?id=60665

When fetching rows from a MySQL database using PDO with the default fetch method
FETCH_LAZY, the empty() function returns false on NULL values in the database.
This behavior does not exist when using other fetch methods (such as FETCH_OBJ or
FETCH_ASSOC).

Or maybe it’s the “not guaranteed”?
http://php.net/manual/en/pdostatement.rowcount.php

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

They are definitely the same. Locally the var dump appears within the tags as expected.
On the host the var dump appears before the pair of tags.

... // ^ Rest of dump ^
  }
  [2018]=>
  NULL
}
<pre>

</pre>

:confused:

I’m using FETCH_ASSOC not FETCH_LAZY.

I did see that in the PHP docs. But since both use MySQL, I would assume they would behave the same.
The other thing is, the first query does get the number of affected rows. But the second one, where the expected value is 0 does not and the value of 4 from the previous query is not overwritten by anything, as if the query did not happen. That’s why I put in the “Query Good!” test, to confirm it did run.

For now I have fixed it by adding a counter to the while loop so it only add the year to the array if it’s above 0. A workaround, but does the job.

But I’m still curious why this happened. If the count does not work on SELECT, how come I get 4 on the first query?
The second one with no rows, I would expect to overwrite it with either 0 or NULL, but it stays at 4.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.