Replacing the Null output of a MySQL query with a "String" in a php table

Question

From the research i conducted in SO, this is the only other question similar to what i need, but im afraid that i didnt help me (How to replace null results in sql query result table with a string?).

I have a table that prints the Medical History of a Patient, and the table cells are restored from a database.

A portion of the code is down below:

echo "<tr>";
echo "<th>MRI Onset Localisation</th>";
echo "<th>CNS MRI Lesions Y/N </th>";
echo "<th>CNS MRI Lesions No.</th>";
echo "<th>CNS MRI Location</th>";
echo "<th>Person Signing the form</th>";
echo "<th>Documented at</th>";
echo "</tr>";
echo "<tr>";
echo "<td>" . $row['Onsetlocalisation'] . "</td>";
echo "<td class='tdclass exempt'>" . $row['smoker'] . '<br>' . $row['cigars'] . '<br>' . $row['cigardate'] . "</td>";
echo "<td>" . $row['onsetsymptoms'] . "</td>";
echo "<td class='tdclass exempt'>" . $row['MRIonsetlocalisation'] . "</td>";
echo "<td>" . $row['MRIenhancing'] . "</td>";
echo "<td class='tdclass exempt'>" . $row['MRInum'] . "</td>";
echo "<td>" . $row['MRIenhancinglocation'] . "</td>";
echo "<td class='tdclass exempt'>" . $row['signer'] . "</td>";
echo "<td>" . $row['reg_date'] . "</td>";
echo "</tr>";

And the MySQL Query that returns those fields is

$sql = "SELECT * FROM tblName WHERE somefield = $somevar";

The thing is... Some of those fields are allowed to have NULL as a value, but for the front end of the app, i want the table cells that contain NULL values to print a standard string such as "N/A" or "Empty" but i need it to be done inside of the php table portion of my code, or so i think...

I found numerous questions about replacing NULL with a string inside of the MySQL query, but i don't care about that since the query doesn't appear at the end user outside of the table.

I tried to implement the coalescing operator in the following way, but no nothing was displayed:

echo "<td class='tdclass exempt'>" . $row['MRInum'] ?? "N/A" . "</td>";

Any help is absolutely welcome.

no dice...means what exactly? You got an errror? Or some unexpected result? Please be precise about the problem. It'll need the expression to be in brackets before it'll work as you expect though - demo: sandbox.onlinephpfunctions.com/code/… — ADyson
– ADyson, Commented Nov 9, 2021 at 23:06
I meant that there was no output, but the brackets was what I was missing, now it does exactly what I needed! thank you and apologies for the lack of precision.. I will try to make it better my next time around! If you want to make this an Answer I will mark it as "Accepted" — Elias Varelas
– Elias Varelas, Commented Nov 10, 2021 at 7:12
@Justinas No I haven't, and since the answer by ADyson has solved my problem im moving on to the next one :) Make no mistake, if there is anything new about the project im working on and it hasn't already been posted here, I will come back. Thank you for you input! I really appreciate it! — Elias Varelas
– Elias Varelas, Commented Nov 10, 2021 at 10:33

ADyson · Accepted Answer · 2021-11-10 08:26:55Z

0

You need to put the expression in brackets to make it clear which parts are supposed to be evaluated by the null coalescing operator:

echo "<td class='tdclass exempt'>" . ($row['MRInum'] ?? "N/A") . "</td>";

If you don't, it thinks the "<td class='tdclass exempt'>" is part of the text to be checked for existence - which means it always returns true because that hard-coded text always exists.

answered Nov 10, 2021 at 8:26

ADyson

62.9k16 gold badges87 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LSerni · Accepted Answer · 2021-11-10 09:23:28Z

0

You can do this in two ways.

From SQL:

COALESCE(fieldName, 'N/A') AS fieldName

will replace NULLs with, you guessed it, 'N/A'.

Otherwise, when you fetch the row,

$row = $stmt->fetch(PDO::FETCH_OBJECT);

you can vet it:

$replacements = [
    'MRI_Data' => 'N/A',
    'MRI_Type' => [
        '001' => 'Single Scan',
        '002' => 'Multi Scan',
        '003' => 'Pulsed',
        '(null)' => 'N/A',
    ],
];


foreach ($row as $key => $value) {
    if (array_key_exists($key, $replacements)) {
        if (is_array($replacement[$key])) {
            if (empty($value)) {
                $value = '(null)';
            }
            if (array_key_exists($value, $replacement[$key])) {
                $row[$key] = $replacement[$key][$value];
            }
        } else {
            if (empty($value)) {
                $row[$key] = $replacement[$key];
            }
        }
    }
}

answered Nov 10, 2021 at 9:23

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

1 Comment

Elias Varelas Over a year ago

Thank you for taking the time to answer my question. Even tho this works and does what I need it to do, its unnecessary and I want to keep the code as minimal as possible.. :)

Collectives™ on Stack Overflow

Replacing the Null output of a MySQL query with a "String" in a php table

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related