How can I reference an EMPTY database table cell?

PHP
1

This is a weird one. I’m not a PHP/MySQL expert, but this seems so simple.

The switch below works. If the value for $Content is ‘Hello World’, it displays “Content 1.” If $Content = ‘’, it displays “No Content” (and echoes “Hello World.”

$Content = 'Hello World';
// $Content = '';
switch ($Content)
{
 case '':
 echo 'No Content 1';
 echo $Content;
 break;
 default:
 echo 'Content 1';
 echo $Content;
 break;
}

But if I delete the first two lines and “Hello World” in my database table, something goes wrong. Actually, it works. The problem is when I DELETE the content, leaving an empty database table cell. It still displays “Content 1,” though it doesn’t echo a value for $Content (because there is none).

I checked the database table. There’s NOTHING in the cell - not a 0, NULL or space.

The value for var_dump is (with content) string(53) "Hello World " and (without content) string(42) " ". Notice that, in each case, there’s a space before the second quote.

I Google’d for help and found the following solution:

if ($Content != 0) {
 echo "true";
 echo $Content;
} else {
echo "false";
echo $Content;
}

But the results are similar. I inserted a 0 in the cell, then NULL, modifying my script accordingly. But I still get the same result.

2

You could use is_null() http://php.net/manual/en/function.is-null.php to detect if it’s getting a null value from the database

3

that would detect if it were getting a PHP null value from the database. To check for a database NULL value would require that it test in the database call itself and then assign a different value to pass back since database NULL values do not have a PHP equivalent.

4

Entirely wrong. Run this test code.

<?php

$db = new PDO('sqlite::memory:');

$db->exec('CREATE TABLE testing (id INTEGER PRIMARY KEY, field1 TEXT, field2 TEXT, field3 TEXT )');
$db->exec("INSERT INTO testing (id, field1, field2, field3 ) VALUES (1, 'test', '', null )");
$result = $db->query('SELECT * FROM testing')->fetch(PDO::FETCH_ASSOC);

echo $result['field2'] === null ? 'yes' : 'no'; // no.
echo "<br>";
echo $result['field3'] === null ? 'yes' : 'no'; // yes.
echo "<br>";
echo $result['field2'] == null ? 'yes' : 'no'; // yes. Loose comparison is evil. Don't use it.

@OP - We need to see your fetching routine. I’ll hazard a guess that’s part of the problem.

5

What does that test code prove - you put a PHP null into the database and then read it back - there is nothing in your code that references an SQL NULL (unknown) value.

As I said and your code demonstrates - that detects a PHP null value but would not cater for if you had a NULL value in the database itself as that is something entirely different (since a PHP null is a known value whereas an SQL NULL is an unknown value).

As long as the value in the database is known then that code will work (even if it is null) whereas if the value is unknown (ie NULL) then it will not work as you need special SQL code for handling unknown values.

6

Your turn to write demonstration code then because I have no idea what you are going on about - SQL has two forms of null? Never ran across that in 14 years.

7

No - SQL has one form of NULL - the example code you presented never referenced it. From what you are saying you have never used it - which some people consider to be a good idea.

If you leave a value out completely when inserting a value into a database and that value in the database will be NULL - that is unknown.

All NULL processing must be done within the SQL itself as no programming language has a data type that will allow the NULL to be extracted without converting it in the SQL into something else first.

An SQL NULL indicates an unknown value - it doesn’t know if the value is 5, ‘bread’, null, undefined or false - it could be any of those because it iis unknown.

Commonly you would use fieldname IS NULL in the where clause to select entries. To substitute a different value for NULL when you read the value back so that the programming language can process it you might use coalesce(fieldname, 10) to substitute 10 for any NULL or alternatively isnull(fieldname, 10) as fieldname - you can of course use whatever value you want instead of 10 - whatever value you ave decided to use to represent unknown alues.

1 Like
8

Ok, that would explain why I haven’t ran across it. I was taught that allowing a database to get into that state was just shy of outright corrupting it. I have seen some of the workaround code for dealing with databases I didn’t design myself put together originally during amateur hour.

9

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