CODEWITHSUNDEEP
phpsqlsql-serverodbc
Sander
Sander

Reputation: 1314

SQL NULL return gives empty string in php using odbc

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.

Upvotes: 0

Views: 3574

Answers (1)

Brett Schneider
Brett Schneider

Reputation: 4103

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

Upvotes: 1

Related Questions