PHP array_push() doesn't work but array[] does, only without SQL where clause

Question

So I have a very odd problem I came across while using SQL clauses and PHP commands. For some reason my code works fine when using the command "SELECT * FROM tableName WHERE columnName=text"; But doesn't work when using "SELECT * FROM tableName"; I've put the SQL command directly into MySQL, and it returns results. If I implode the row array, I get the results, and if I switch from using array_push($result...) to $result= [] I get the last entry in JSON format, as expected. I'm not sure why I'm getting this behavior, this code I believe worked in a previous version of PHP, or maybe an older version of MySQL. I've done some updates recently and everything is now at the very latest version. Searching found people who had both array_push and array[] not work, or where the command didn't work with the WHERE clause, but I have the opposite issue. Not sure what it could be, I enabled PHP error reporting and SQL error reporting, and I just get the PHP white screen of death when the issue happens.

<?php
error_reporting(-1);
define('HOST','localhost');
define('USER','dbuser');
define('PASS','dbpassword');
define('DB','dbname');

$eventYear = isset($_GET["year"]) ? $_GET["year"] : '';
$eventYear = addslashes($eventYear);
if (empty($_GET["year"])){
$eventYear = 2016;
}

$con = mysqli_connect(HOST,USER,PASS,DB) or die ("Database Connection Failed");

$sql = "SELECT * FROM tableName";

$res = mysqli_query($con,$sql);

if (!$res) {
printf("Error: %s\n", mysqli_error($con));
exit();
}

$result = array();

while($row = mysqli_fetch_array($res)){
array_push($result,
array('teamNumberLabel'=>'Team Number',
'teamNumber'=>$row["teamNumber"],
'teamEventLabel'=>'Team Event Name',
'teamEvent'=>$row["teamEvent"],
'teamDistrictCodeLabel'=>'District Code: ',
'teamDistrictCode'=>$row["teamDistrictCode"],
'teamCityLabel'=>'City: ',
'teamCity'=>$row["teamCity"],
'teamStateProvLabel'=>'State/Province: ',
'teamStateProv'=>$row["teamStateProv"],
'teamCountryLabel'=>'Country: ',
'teamCountry'=>$row["teamCountry"],
'teamNameFullLabel'=>'Sponsors: ',
'teamNameFull'=>$row["teamNameFull"],
'teamNameShortLabel'=>'Nickname: ',
'teamNameShort'=>$row["teamNameShort"],
'teamRobotNameLabel'=>'Name: ',
'teamRobotName'=>$row["teamName"],
'teamRookieYearLabel'=>'Rookie Year: ',
'teamRookieYear'=>$row["teamRookieYear"],
'teamWebsiteLabel'=>'Website: ',
'teamWebsite'=>$row["teamWebsite"]
));
}
header('Content-Type: application/json');
echo json_encode(array("result" => $result));

mysqli_close($con);

?> 

I have a lot of other files with similar code, but they all contain WHERE clauses on the SQL command, so it all works as expected. Just have the problem where nothing works without the WHERE clause included. I've tried placing quotes around the table name, but it shows incorrect SQL syntax, so that isn't it. Also read up on SQL commands and array_push and I can't seem to figure it out.

Update: As Marshall stated, I believe the issue now is data that is not in UTF-8, which causes the JSON encoding to fail and not display any data. The table is hundreds of entries long, so I'm going to filter out non UTF-8 characters before encoding to see if it works then.

Update 2: Found the issue. I have another script that runs through the list of teams for a single event (this one is similar but returns all teams for the entire year), and I went through the list until it failed similar to this one. A Canada event with non-UTF-8 characters is what's causing the scripts to fail. Montréal is showing as Montréal in the database and Québec as Québec. The code worked earlier only because this event didn't exist when the code was initially tested, and the é is an unsupported character. This also explains why the code works with the WHERE clause and the array[] function, because it doesn't hit the malformed data in either case. Properly filtering the data coming in like I should've would have not caused this, but at least now I know the cause, I was really stuck on this one because the code looked correct and the SQL command was correct as well. Thanks for your help everyone.

Update 3: Okay so here's the full fix. I modified everything in PHPMyAdmin to the utf8mb4_unicode_ci collation, and utf8mb4 character set. Then modified the script that fetches the data from the API and enters it into the database. I'm using PDO to connect to the db, so I added the charset to the PDO entry.

$dbh = new PDO("mysql:host=localhost;dbname=dbname;charset=utf8mb4", 'username', 'password');

That got the data going into the database correctly, but the JSON parser still didn't work. Found out I also needed to explicitly tell it to work in the UTF-8MB4 charset. Adding

$con->set_charset("utf8mb4");

to the PHP script before the SQL query fixed that. Now the correct characters go through both where the data enters the database and when it is requested. So the problem was that the scripts were not set to UTF-8, when the data was coming through as such. Hopefully this helps someone with a similar problem.

Answer 1

Why not just let MySQL handle all the data manipulation..

$sql = "SELECT 
    'Team Number' as teamNumberLabel, 
    teamNumber,
    'Team Event Name' as teamEventLabel,
    teamEvent,
    'District Code: ' as teamDistrictCodeLabel,
    teamDistrictCode,
    'City: ' as teamCityLabel,
    teamCity,
    'State/Province: ' as teamStateProvLabel,
    teamStateProv,
    'Country: ' as teamCountryLabel,
    teamCountry,
    'Sponsors: ' as teamNameFullLabel,
    teamNameFull,
    'Nickname: ' as teamNameFull,
    teamNameShort,
    'Name: ' as teamRobotNameLabel,
    teamName as teamRobotName,
    'Rookie Year: ' as teamRookieYearLabel,
    teamRookieYear,
    'Website: ' as teamWebsiteLabel,
    teamWebsite
FROM tableName";

$res = mysqli_query($con,$sql);
$result = mysqli_fetch_all($res, MYSQLI_ASSOC);

print_r($result);

Use mysqli_fetch_all() to get all the results at once and skip the loop.

If you can't use mysqli_fetch_all() see here, and do this instead:

// Use the query from above
$res = mysqli_query($con,$sql);
$result = array();
while($r = mysqli_fetch_assoc($res, MYSQLI_ASSOC)) array_push($result, $r);

Answer 2

Make sure $row contains all required indexes in your array_push function. You should validate the $row.

From the mysqli_fetch_array manual:

Note: This function sets NULL fields to the PHP NULL value.