Check if field of table has NOT NULL property set

I want to check whether a field in a table has been defined with NOT NULL.

So Lets say I have the following code to create an sql query:

$sql = "CREATE TABLE `testdb`.`test` (`a` INT NULL, `b` INT NOT NULL DEFAULT \开发者_开发技巧'2\');";

Now I want to write some code in php that can query the table as to whether or not field b is defined as NOT NULL or not. I suppose the more general question here is how to query a table for the properties of its fields.

You can use the IS_NULLABLE column from the information schema

INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

SELECT column_name, 
       is_nullable 
FROM   information_schema.columns 
WHERE  table_schema = 'testdb' 
       AND table_name = 'test' 

Here you go:

$table = 'my_table';
$result = mysql_query('DESCRIBE `' . $table . '`');

while($item = mysql_fetch_assoc()){
    print_r($item); // this will output the properties of the table
}

In $item you'll now have a property called Null which is either NO or YES.