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 Add Comment Developer Edit
 [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

Add a Patch

Pull Requests

Add a Pull Request

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-2024 The PHP Group
All rights reserved. 		Last updated: Sun May 05 06:01:33 2024 UTC