Finding the primary value and default value

[code]colorTable

(id) color name
(1) red Tom
(2) blue Mary[/code] I have a table like the above.

and I have SQL like the below.

SQL insert into colorTable (name) value('Jane')
The result of the execution of the above SQL code is the table below.

code color name
(1) red Tom
(2) blue Mary
(3) black Jane[/code]
The value of the column (id) is “3” because it is primary.
The value of the column “color” is “black” because it is the default value of the column.

I like to find the (id) value “3” and the defualt value “black” at the same page with the execution page.

The would-be code1 below doesn’t work correctly, but I hope it shows to you what I want.

[code]would-be code1

$query=(“insert into colorTable (name) value(‘Jane’)
select @@identity as maxID”);
$sql=mysql_query($query);
$row=mysql_fetch_assoc($sql);
$maxID= $row[‘maxID’];

echo $maxID
[/code]How can I get the maxID?

The would-be code2 below doesn’t work correctly, but I hope it shows to you what I want.

[code]would-be code2
select color(default) as default_color

echo $default_color
[/code]I hope the would-be code2 above produces “black”.
How can I get the default value of the column “color”?

Use LAST_INSERT_ID()

You need to make second query after insertion to get ID and color of newly added record:

SELECT id, color FROM colorTable WHERE id = LAST_INSERT_ID()

The value of id is 3 because that column is defined auto increment, not because it’s primary :wink:

And you really should start using the mysqli_ functions instead of the deprecated mysql_ ones

The value of id is 3 because that column is defined auto increment, not because it's primaryYes, that’s true. auto increment is the main reason instead of primary. it was my mistake.

you really should start using the mysqli_ functions instead of the deprecated mysql_ onesWhat code in my code above is deprecated mysql_ones?

How can I select the default value and get the default value “black” in “colorTable” ?

All the ones with mysql_

All the ones with mysql

The following is one of my code in the above.

insert into colorTable (name) value('Jane')

How can I change the code above into not deprecated one?

Let’s suppose I don’t know what is the default value( defined as “black”) of the column “color”.
I like to find the default value “black” with SQL in a php page.
What SQL should I use toI get the default value?

http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.