SQL NULL return gives empty string in php using odbc

Question

I have a database with two identical tables (one containing values, and the other contains values/users). When reading the values I wish to take the two values and place them onto one row. In certain cases (for some columns) there is only a value and not a value/users-value, in these cases I wish for the returned value to be null.

To make things a bit more clear, here is an example;

Value table (Table1)

    |  id  |  CPU  | Memory |  Name  |
    |------+-------+--------+--------|
    |  1   |   45  |   25   |  Comp1 |
    |  2   |   25  |  12.5  |  Comp2 |

Value/Users table (Table2)

    |  id  |  CPU  | Memory |  Name  |
    |------+-------+--------+--------|
    | 1_A  |  22.5 |  12.5  |        |
    | 2_A  |   5   |   2.5  |        |

I do not for instance need to know the value of Name divided by the number of users. In order to differentiate these values from the rest, I have a table named colInfo that specifies which values DO exist in the second table using the variable columnName. (Note: I cannot modify the structure of the database).

When retrieving the data I use the following SQL Query;

SELECT t1.[NAME] as Value, t2.[UserValue]
FROM (SELECT * FROM TABLE1 WHERE ID = 1) t1
left join (SELECT [ID],
CASE(SELECT COUNT(*) FROM colInfo WHERE [columnName] LIKE 'NAME')
    WHEN 0 THEN NULL
           ELSE [NAME]
END as [UserValue] from t2 on LEFT(t2.Id,LEN(t2.Id)-1) = LEFT(t1.Id,LEN(t1.Id)-1)

(The reasoning behind the sub-select and the left join goes along with the fact that I cannot change the database structure)

When running this query in SQL Management Studio, I get the correct result;

    | ID | Value | UserValue |
    |----+-------+-----------|
    | 1  | Comp1 |   NULL    |

But when running it from a php-site using odbc I get, instead of NULL, an empty string. I use the following code;

$query = $this->sqlFactory->getComputerStats($params,$ids);
$result = odbc_exec($this->conn, $query);
for ($j=1; $j<=$no_rows; $j++) { 
  odbc_fetch_row($result, $j);
  if(!is_null(odbc_result($result,"UserValue"))){
    echo odbc_result($result,"UserValue");
  }
}

Using Developer Tools this will give me an empty string. If I instead echo $query and test this in the SQL environment I do get a NULL value.

How come the odbc_result will not parse the result as null?

EDIT: According to the documentation of odbc_result it should return null when the value of the SQL Query returns NULL. As can be seen in the documentation.

Community · Accepted Answer · 2017-05-23 11:57:37Z

1

mainly this has to do with how php sees null. in php null is not a possible value for a datatype, but it itself is a datatype of its own.

so when retrieving data from the database, odbc sends a datatype as well as a value (possibly null) which then gets translated into php datatypes, thus no longer accomodating the notion of the database-null. the next-best thing for a string containing null being an empty string.

i think this post will help: In PHP, what is the differences between NULL and setting a string to equal 2 single quotes

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Jan 10, 2014 at 12:55

Brett Schneider

4,1032 gold badges19 silver badges33 bronze badges

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

5 Comments

Sander Over a year ago

So what can I do to make PHP see my value as null? Looking at the documentation of odbc_result (php.net/manual/en/function.odbc-result.php) it seems that it SHOULD return null for NULL data.

Brett Schneider Over a year ago

unfortunately i have no running development setup that matches yours, so i can only guess. the php driver supports null. now this can be a problem with the microsoft odbc driver. there used to be quite a few issues there regarding empty strings and nulls. for now i suggest fiddling around with SET ANSI_NULLS {ON|OFF} and ´SET ANSI_NULL_DFLT_ON {ON|OFF}` and see how it behaves.

Sander Over a year ago

I am unable to modify the SQL environment, so that is unfortunately not an option.

Brett Schneider Over a year ago

the SET ANIS_NULLS {ON|OFF} and SET ANSI_NULL_DEFLT_ON {ON|OFF} are session parameters and can be set through a query. so basically after opening the odbc session, execute them before executing the actual query.

Sander Over a year ago

it seemed that the easiest way was to introduce a new sql check to see whether the value was set to null in the database, and have this set to 1/0 depending on the result. This since php was unable to process the data correctly.

Collectives™ on Stack Overflow

SQL NULL return gives empty string in php using odbc

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related