php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #50147 DEFAULT NULL Values in mysql_query('SHOW COLUMNS FROM `xx`');
Submitted: 2009-11-11 10:35 UTC Modified: 2009-11-12 11:13 UTC
From: w at lder dot de Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 5.2.11 OS: Linux 2.6.16.21-0.25-default
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: w at lder dot de
New email:
PHP Version: OS:

 

 [2009-11-11 10:35 UTC] w at lder dot de 
Description:
------------
The attached Code results a wrong result.
MySQL:
Server Version: 5.0.26
MySQL-Client-Version: 5.0.26



Reproduce code:
---------------
<?php
/* 
MySQL:
CREATE TABLE IF NOT EXISTS `test` (
  `hereIsDefaultNULL` int(255) default NULL,
  `defaultNULLvarchar` varchar(255) default NULL
)
*/
mysql_connect('localhost','user','');
mysql_select_db('test');
$columns_res = mysql_query('SHOW COLUMNS FROM `test`');
while ($fieldRow = mysql_fetch_assoc($columns_res)) {
	print_r($fieldRow)	;	
}
?>

Expected result:
----------------
Array
(
    [Field] => hereIsDefaultNULL
    [Type] => int(255)
    [Null] => YES
    [Key] => 
    [Default] => NULL
    [Extra] => 
)
Array
(
    [Field] => defaultNULLvarchar
    [Type] => varchar(255)
    [Null] => YES
    [Key] => 
    [Default] => NULL
    [Extra] => 
)


Actual result:
--------------
Array
(
    [Field] => hereIsDefaultNULL
    [Type] => int(255)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)
Array
(
    [Field] => defaultNULLvarchar
    [Type] => varchar(255)
    [Null] => YES
    [Key] => 
    [Default] => 
    [Extra] => 
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-11-11 10:44 UTC] uw@php.net 
ext/mysql returns whatever MySQL delivers. Please verify that the MySQL prompt returns the result you want. If not, this is a MySQL bug not a PHP bug.

Please run on the command line:

mysql -u<user> -p<password> <database>...
mysql> SHOW COLUMNS FROM <mytable>
 [2009-11-11 11:28 UTC] w at lder dot de 
Here is the result:
mysql> SHOW COLUMNS FROM test;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| hereIsDefaultNULL  | int(255)     | YES  |     | NULL    |       |
| defaultNULLvarchar | varchar(255) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
 [2009-11-12 08:38 UTC] jani@php.net 
Replace print_r() with var_dump() and prepare for surprise.
 [2009-11-12 09:08 UTC] w at lder dot de 
OK, but how can I get the value NULL?
I think it is an difference between "NULL" and "".
Any idea?

Another Example:
<?php
/* 
Mysql:
  CREATE TABLE IF NOT EXISTS `test` (
  `hereIsDefaultNULL` int(255) default NULL,
  `defaultEmptyvarchar` varchar(255) NOT NULL default ''
)
*/
mysql_connect('localhost','root','');
mysql_select_db('test');
$columns_res = mysql_query('SHOW COLUMNS FROM `test`');
while ($fieldRow = mysql_fetch_assoc($columns_res)) {
	if ($fieldRow['Default'] == NULL) {
  		echo $fieldRow['Field'] . ' is NULL </br>';
  	}
}
?>
Expected result:
----------------
hereIsDefaultNULL is NULL

Actual result:
--------------
hereIsDefaultNULL is NULL 
defaultEmptyvarchar is NULL
 [2009-11-12 09:33 UTC] uw@php.net 
Bogus

Why would one return NULL for meta data. NULL = undefined, "NULL" = defined.
 [2009-11-12 10:00 UTC] jani@php.net 
Please, ask further question on how to use PHP elsewhere. There are no bugs in this in either PHP or MySQL.
 [2009-11-12 10:06 UTC] uw@php.net 
Wait, ... I didn't get you changed table structure. Can you provide a new var_dump but keep in mind:

[2009-11-12 11:05] <Jani_> '' == NULL (true), NULL == NULL (true).

Ulf
 [2009-11-12 10:27 UTC] w at lder dot de 
Here the var_dump:
array(6) {
  ["Field"]=>
  string(17) "hereIsDefaultNULL"
  ["Type"]=>
  string(8) "int(255)"
  ["Null"]=>
  string(3) "YES"
  ["Key"]=>
  string(0) ""
  ["Default"]=>
  NULL
  ["Extra"]=>
  string(0) ""
}
array(6) {
  ["Field"]=>
  string(19) "defaultEmptyvarchar"
  ["Type"]=>
  string(12) "varchar(255)"
  ["Null"]=>
  string(2) "NO"
  ["Key"]=>
  string(0) ""
  ["Default"]=>
  string(0) ""
  ["Extra"]=>
  string(0) ""
}

Yes, I know that "'' == NULL (true)", but in mySQL :

mysql> SELECT '' IS NULL;
+------------+
| '' IS NULL |
+------------+
|          0 |
+------------+

mysql> SELECT NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+
 [2009-11-12 10:43 UTC] jani@php.net 
But in PHP ('' == NULL) is true and ('' === NULL) isn't. :)
 [2009-11-12 11:13 UTC] w at lder dot de 
Hi Jani,

right, thank you.

If someone has a similar problem here my workaround to solve the problem.

The goal is to compare an line in a sql Create Table Statement with the actually value in the Database
<?php
$str = '`hereIsDefaultNULL` int(255) default NULL';

mysql_connect('localhost','root','');
mysql_select_db('test');
$columns_res = mysql_query('SHOW COLUMNS FROM `test`');
while ($fieldRow = mysql_fetch_assoc($columns_res)) {
	if ($fieldRow['Default'] === NULL) {
  		$fieldRow['Default'] = 'NULL';
  	} 
	$strToCompare=  '`' . $fieldRow['Field'] . '` ' . $fieldRow['Type'] . ' default ' . $fieldRow['Default'];
	
	if ($str == $strToCompare) {
		echo 'OK';
	}
}
?>
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Thu Jul 03 11:01:34 2025 UTC