MYSQL - NULL handling issue

PHP
1

Having issues handling null field from mysql database table. I haven’t been successfull with Google
search and implementation so i throug i try this avenue.
Even tho theres a null field in the record i still like to pull the record as I can substitue the null
field with somethihng else later with PHP.
Basically what im looking for is a way to implement a if statement of some sort to accomplish this task.

if null still querie the record instead of hanging.

try{
            $STH = $this->DBH->prepare("SELECT tbl_newproject.prj_name,                                                 
                                               tbl_version.ver_version AS 'Version'
            FROM  tbl_newproject, tbl_version
			
            where tbl_newproject.prj_index = $projectID AND
                  tbl_version.ver_prjIndex = tbl_newproject.inc_version
                   ");
            $STH->execute();
            $results1 = $STH->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) { echo 'table error : ' . $e->getMessage(); }
2

You will need to substitute something else for the NULL in the SQL as PHP has no concept of NULL and soneeds it converted to something else in order to be able to store a value in the first place.

A PHP null value is something completely different with a completely different meaning - about the same amount of difference as between son and sun…

3

Hi Felgall,

thats for the replay back but its not what im looking for…I added php for clarification and looks like i failed.

it has more to do with my mysql statement more specifically the line: tbl_version.ver_prjIndex = tbl_newproject.inc_version

if the criteria isn’t met it hangs but i still need a way to by pass, if no value is found in the table still keep moving…

thanks again

4

Which field contains the null values?

5

Hi spacePhoneix

the null field is tbl_newproject.inc_version.

so, basically
if tbl_newproject.inc_version is null then do the following:
tbl_version.ver_prjIndex = tbl_newproject.inc_version
else dont bother wtih tbl_version.ver_prjIndex = tbl_newproject.inc_version but continue pulling information for tbl_newproject.prj_name and other fields.

after having done some research i came up with something like tis but im still missing something parheaps a return value of some sort, not sure…
( tbl_newproject.inc_version IS NOT NULL or tbl_version.ver_index = tbl_newproject.inc_version.inc_version)

thanks for the help

6

, COALESE(tbl_version.ver_version,0) AS 'Version'

Which fields is the join being done on? The join syntax that you’ve used is out of date

7

@felgall

JFYI, PHP is pretty aware of the concept of NULL.

A PHP null value is pretty similar to SQL null value, meaning “no value is set”.

No special action is required in PHP to handle database nulls, as long as prepared statement for the input values is used: in this case PHP null will be translated into SQL null automatically.

When selecting data, a null from database is always translated into PHP null.

@robin01

To solve your problem you have to use LEFT JOIN. This kind of join is used to let the left-handed table return all the rows, no matter whether there is a counterpart in the right-handed table or not.

2 Likes
8

Hi Colshrapnel,

thanks for the reply and your post worked to solve my issue… thanks again.

9

In PHP null means no value set

In SQL NULL means it has a value but the value is unknown.

Not the same thing at all.

10

My take is a little different, but I agree that they are not the same thing.

In PHP I can do

var_dump($undefined_or_unset_or_nulled)

and will see that for PHP, the variable has a value of NULL

In PHP I can test like

if ($undefined_or_unset_or_nulled == NULL) {

But doing similar in MySQL will not work

SELECT COUNT(id) FROM table WHERE field = NULL

needs to be

SELECT COUNT(id) FROM table WHERE field IS NULL
11

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