Example table
The example table used in this post was created with the following SQL:
CREATE TABLE `products` (
`product_id` int(10) unsigned NOT NULL auto_increment,
`url` varchar(100) NOT NULL,
`name` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`visible` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`product_id`),
UNIQUE KEY `url` (`url`),
KEY `visible` (`visible`)
)Using DESCRIBE
The SQL query to get the table structure is:
DESCRIBE products;
You can run this from the MySQL CLI; phpMyAdmin; or using a programming language like PHP and then using the functions to retrieve each row from the query.
The resulting data from the MySQL CLI looks like this for the example table above:
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| product_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| url | varchar(100) | NO | UNI | NULL | |
| name | varchar(50) | NO | | NULL | |
| description | varchar(255) | NO | | NULL | |
| price | decimal(10,2) | NO | | NULL | |
| visible | tinyint(1) unsigned | NO | MUL | 1 | |
+-------------+---------------------+------+-----+---------+----------------+Using PHP
If you were using PHP, for example, you could do something like this:
$res = mysql_query('DESCRIBE products');
while($row = mysql_fetch_array($res)) {
echo "{$row['Field']} - {$row['Type']}\n";
}
The output using the example table would be this:
product_id - int(10) unsigned
url - varchar(100)
name - varchar(50)
description - varchar(255)
price - decimal(10,2)
visible - tinyint(1) unsignedQuerying the INFORMATION_SCHEMA
My next MySQL post will look at how to do the same using the INFORMATION_SCHEMA, which provides more information than DESCRIBE, although I have found querying the INFORMATION_SCHEMA can run a little slowly sometimes myself.
