Page 1 of 1

How to read a DEFAULT value

Posted: Sun Jun 03, 2007 1:33 pm
by WaldoMonster
How can you read the default value of specified field in MySQL?
For example when you set de default value to 3, how can you later read this default value?

Code: Select all

ALTER TABLE `configuration` CHANGE `id` `id` INT( 10 ) NOT NULL DEFAULT '3';

Posted: Sun Jun 03, 2007 2:07 pm
by bdlang
I'm not sure what you mean, are you asking how to retrieve all records with a value of 3?

Code: Select all

SELECT column FROM table WHERE id = 3;

Posted: Sun Jun 03, 2007 2:07 pm
by WaldoMonster
http://dev.mysql.com/doc/refman/4.1/en/ ... on_default
Could be a solution but I'm still using MySQL 4.0.27.
DEFAULT() was added in MySQL 4.1.0.
Is there an alternative?

Posted: Sun Jun 03, 2007 2:10 pm
by feyd
SHOW CREATE TABLE should give you the information.

Posted: Sun Jun 03, 2007 2:14 pm
by WaldoMonster
bdlang wrote:I'm not sure what you mean, are you asking how to retrieve all records with a value of 3?

Code: Select all

SELECT column FROM table WHERE id = 3;
Sorry, but I don't want to read the values stored in a field, but the DEFAULT setting of a field.

Posted: Sun Jun 03, 2007 2:31 pm
by WaldoMonster
feyd wrote:SHOW CREATE TABLE should give you the information.
Thanks, this will indeed give the needed information.
I have to filter it out somehow.

Does MySQL have a version checking?
Than I can still use DEFAULT() when the version is equal or greater than 4.1.0?
Or even better is it possible to check if a specified function is supported?

Posted: Sun Jun 03, 2007 2:36 pm
by John Cartwright

Code: Select all

mysql_get_client_info()

//or

mysql_query("SELECT VERSION() as mysql_version");

Posted: Sun Jun 03, 2007 2:40 pm
by bdlang
WaldoMonster wrote: Sorry, but I don't want to read the values stored in a field, but the DEFAULT setting of a field.
Ok, SHOW CREATE TABLE tablename (as feyd has already mentioned) or DESC tablename will show you the value; are you trying to retrieve this via PHP? Run the query and retrieve the Default field using mysql_result().

Posted: Sun Jun 03, 2007 3:20 pm
by WaldoMonster
Thanks for all the fast and valuable feedback.
Here I have a working solution for php:

Code: Select all

$default = '';
$query = mysql_query('DESC configuration');
while($result = mysql_fetch_array($query))
    {
    if ($result['Field'] == 'id')
        $default = $result['Default'];
    }

Posted: Tue Jun 05, 2007 8:20 am
by dibyendrah
WaldoMonster wrote:Thanks for all the fast and valuable feedback.
Here I have a working solution for php:

Code: Select all

$default = '';
$query = mysql_query('DESC configuration');
while($result = mysql_fetch_array($query))
    {
    if ($result['Field'] == 'id')
        $default = $result['Default'];
    }
I wondered from the query "DESC configuration". What does it return ?

Posted: Tue Jun 05, 2007 1:33 pm
by bdlang
dibyendrah wrote: I wondered from the query "DESC configuration". What does it return ?
In this case, `configuration` is the name of the table, not some magic MySQL 'configuration' keyword, in case that's what you're wondering. DESC is short for DESCRIBE, which returns general column information from said table.

MySQL Manual : DESCRIBE syntax

Posted: Wed Jun 06, 2007 4:03 am
by WaldoMonster
Thanks for the link bdlang,
There was a user comment that described that it is possible to retrieve a specific column.
Than the loop is not needed any more.
My example table configuration will return:

Code: Select all

mysql> DESCRIBE configuration;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| name       | char(35) | No   |     |         |                |
| color      | char(35) | No   |     |         |                |
| id         | int(10)  |      |     | 3       |                |
+------------+----------+------+-----+---------+----------------+
With a specified column id:

Code: Select all

mysql> DESCRIBE configuration id; 
+------------+----------+------+-----+---------+----------------+ 
| Field      | Type     | Null | Key | Default | Extra          | 
+------------+----------+------+-----+---------+----------------+ 
| id         | int(10)  |      |     | 3       |                | 
+------------+----------+------+-----+---------+----------------+
Here is working php script:

Code: Select all

$query = mysql_query('DESCRIBE configuration id');
$default = mysql_fetch_array($query);
$default = $default['Default'];

Posted: Wed Jun 06, 2007 7:43 am
by bdlang
Ah. I'm sorry I didn't post it sooner; if you notice, my previous post recommends using mysql_result(), I'd use that over retrieving a two index array (0, 'Default'). A bit more efficient. :)

Posted: Wed Jun 06, 2007 9:13 am
by WaldoMonster
bdlang wrote:Ah. I'm sorry I didn't post it sooner; if you notice, my previous post recommends using mysql_result(), I'd use that over retrieving a two index array (0, 'Default'). A bit more efficient. :)
Thanks, It will also make the code one line shorter :wink:

Posted: Wed Jun 06, 2007 2:35 pm
by Weirdan
with mysql >5.0 you can use the information_schema database:

Code: Select all

select COLUMN_DEFAULT 
from INFORMATION_SCHEMA.COLUMNS 
where 
   TABLE_SCHEMA=database() 
   and TABLE_NAME='$table_name' 
   and COLUMN_NAME='$column_name'