How to solve error: Trying to access array offset on value of type null with Prepared Statement PHP

Question

I am trying to display overall service requested. But I keep getting error

Trying to access array offset on value of type null

The SQL statement is working but I am not sure where the mistake is on the PHP side.

$stmt4 = $mysqli->prepare("SELECT ServiceRequested,status, COUNT(ServiceRequested) AS `occurrence` FROM application WHERE status = ? GROUP BY ServiceRequested ORDER BY `occurrence` DESC LIMIT 1");
$stmt4->bind_param('i',$status);
$stmt4->execute();
$MostServiceRequested = $stmt4->get_result()->fetch_row()[0];
    
if ($MostServiceRequested == 'ABC'){
    $ServiceRequested = 'ABC';
} 
elseif ($MostServiceRequested == 'DEF'){
    $ServiceRequested = 'DEF';
} 
elseif ($MostServiceRequested == 'GHI'){
    $ServiceRequested = 'GHI';
} 
else {
    $ServiceRequested = 'None';
}

<h3><?php echo $ServiceRequested; ?></h3>

I have tried removing [0] in $MostServiceRequested = $stmt4->get_result()->fetch_row()[0]; but it shows None even if there is record in the database. Can I know what or how the code needs to be fixed.

Answer 1

If you just want the result from the query or "None" if there are no rows returned, try this...

$sql = <<<_SQL
SELECT ServiceRequested, COUNT(ServiceRequested) AS `occurrence`
FROM application
WHERE status = ?
GROUP BY ServiceRequested
ORDER BY `occurrence` DESC LIMIT 1
_SQL;

$stmt4 = $mysqli->prepare($sql);
$stmt4->bind_param('i', $status);
$stmt4->execute();
$ServiceRequested = $stmt4->get_result()->fetch_row()[0] ?? "None";

You also shouldn't have status in the SELECT clause without including it in the GROUP BY.

The null coalescing operator (??) requires PHP 7.0