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.

[_"`fetch_row()` returns an enumerated array representing the fetched row **or `null` if there are no more rows** in result set."_](https://www.php.net/manual/mysqli-result.fetch-row.php#refsect1-mysqli-result.fetch-row-returnvalues) — Phil, Nov 09 '21 at 02:53
so how do I change the code so that even if record is empty.....it will show none rather than error ? — Laan, Nov 09 '21 at 02:58
Do you really want `$MostServiceRequested` to be "None" if the result is anything other than "ABC". What if your query returns "DEF"? — Phil, Nov 09 '21 at 03:09
I have a few if else other than ABC ...but i removed in the code......so if there is more conditions then how would the code be like ? — Laan, Nov 09 '21 at 03:12
What exactly are the conditions? Do you want to assign the result from the query (provided there is one) no matter what it is or are there particular `ServiceRequested` results you want to filter out? — Phil, Nov 09 '21 at 03:14
So **only** "ABC", "DEF" or "GHI"? If it returns "XYZ" then the result should be "None"? — Phil, Nov 09 '21 at 03:18
yes correct....but since it is based on select options from html form....it will either be these 3 options only... if empty then it will show none — Laan, Nov 09 '21 at 03:19

Phil · Accepted Answer · 2021-11-09T03:22:13.643

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

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

1 Answers1