How to read a DEFAULT value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

How to read a DEFAULT value

Post 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';
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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;
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SHOW CREATE TABLE should give you the information.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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.
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

mysql_get_client_info()

//or

mysql_query("SELECT VERSION() as mysql_version");
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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().
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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'];
    }
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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 ?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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'];
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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. :)
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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'
Post Reply