Autoincrementing id fields from a post form...

[Rich]

G'day everyone,
I am new to php, so bear with me if I am asking a stupid question.

I have created an insert form using a post action and I am trying to find a way to increment the latest id value in the id field of my mysql table so that the next row will have the correct id value.

Can anyone give me an idea of how to do this?

[ergophobe]

Is it just a straight integer count? In that case set up your DB so that the column is auto-incrementing.

Tom

[Rich]

It is just an integer count...
I have already set the id field to autoincrement but the info won't insert into the db unless the id is specified somehow.

I think I need to preincrement the field, just not sure how.

[Netizen]

Is your table set up something like this:

mysql> create table mytable (id int unsigned not null auto_increment primary key, sometext char(255) not null);
mysql> insert into mytable (sometext) values('test');
mysql> insert into mytable (sometext) values('test again');
mysql> select * from mytable;
+----+------------+
¦ id ¦ sometext ¦
+----+------------+
¦ 1 ¦ test ¦
¦ 2 ¦ test again ¦
+----+------------+

[coopster]

Welcome to WebmasterWorld, Rich!

It's tough to find good details on MySQL's

AUTO_INCREMENT

attribute. However, if you search the MySQL CREATE TABLE [mysql.com] pages of the manual for

AUTO_INCREMENT

you will find your answer:

An integer column may have the additional attribute

AUTO_INCREMENT

. When you insert a value of

NULL

(recommended) or 0 into an indexed

AUTO_INCREMENT

column, the column is set to the next sequence value. Typically this is

value+1

, where

value

is the largest value for the column currently in the table.

AUTO_INCREMENT

sequences begin with 1.

Additionally, if neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified. That is why Netizen's code works as shown.

There is much more information in the link provided, including setting flags that store 0 in AUTO_INCREMENT columns as 0, instead of generating a new sequence value -- a whole new twist beginning with MySQL 4.1.1.

INSERT a NULL value for your AUTO_INCREMENT columns and you'll be good to go.

[Netizen]

Indeed, usually I insert a NULL in an auto_increment field as it makes the code more obvious - shouldn't have taken that shortcut! :-)

[Rich]

OK...Thanks, didn't expect so many responses...

In the case of:

$info_id = null;
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$Phone = $_POST["Phone"];
$Email = $_POST["Email"];
$SiteExist = $_POST["SiteExist"];
$URLExist = $_POST["URLExist"];
$SiteDesign = $_POST["SiteDesign"];
$SiteExample = $_POST["SiteExample"];
$URLExample = $_POST["URLExample"];

$query = "INSERT INTO tbl_info values ('$info_id','$FirstName','$LastName','$Phone','$Email','$SiteExist','$URLExist','$SiteDesign','$SiteExample','$URLExample')";
$result = mysql_query($query);

Where 'info_id' is my id field, have I got the correct value set?
Or is there a better way to do it?
Cheers

[coopster]

Not quite. You can't put quotes around the $info_id because it is going to be cast as a string. Currently, you have...

$query = "INSERT INTO tbl_info values ('$info_id','$FirstName',...

...and this would actually give you a statement that looks like this:

INSERT INTO tbl_info values ('null', 'Rich',...

...notice that 'null' has quotation marks around it? That's not good, that means it is being cast as a string. Although this actually would work on your numeric value, the first time you tried this on a character value, you would end up with the word null in your column as opposed to a

NULL

value. Two very different values! [mysql.com]
What you really want the statement to look like would be:

INSERT INTO tbl_info values (NULL, 'Rich',...

To accomplish this, remove the quotation marks:

$query = "INSERT INTO tbl_info values ($info_id,'$FirstName',...

And for the record, NULL is case-insensitive in both PHP [php.net] and MySQL [mysql.com].

[Rich]

OK...
I have adjusted my code to:

$info_id = NULL;
$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$Phone = $_POST["Phone"];
$Email = $_POST["Email"];
$SiteExist = $_POST["SiteExist"];
$URLExist = $_POST["URLExist"];
$SiteDesign = $_POST["SiteDesign"];
$SiteExample = $_POST["SiteExample"];
$URLExample = $_POST["URLExample"];

$query = "INSERT INTO tbl_info values ($info_id,'$FirstName','$LastName','$Phone','$Email','$SiteExist','$URLExist','$SiteDesign','$SiteExample','$URLExample')";
$result = mysql_query($query);

But it is still not working...at this point I have to say that I am quite confused as to why...

[lorax]

>> Where 'info_id' is my id field, have I got the correct value set?

If info_id is the auto_increment field you shouldn't have to even include it in the INSERT query. Take it out and then try it.

[jatar_k]

or just fire it blank

$query = "INSERT INTO tbl_info values ('', '$FirstName', '$LastName', '$Phone', '$Email', '$SiteExist', '$URLExist', '$SiteDesign', '$SiteExample', '$URLExample')";

[Rich]

Tried both of those options.
'' worked for the first insert but not the second.
leaving the field out entirely didn't work.

Is this an unusual issue or am I just missing something important?

[bobnew32]

I put in NULL for my ids and it increments it for me.

[Netizen]

I think you might need to cut and paste the results of a

show create table <your table name>

for us here.

[Rich]

I'll do one better than that...I got it working:

$FirstName = $_POST["FirstName"];
$LastName = $_POST["LastName"];
$Phone = $_POST["Phone"];
$Email = $_POST["Email"];
$SiteExist = $_POST["SiteExist"];
$URLExist = $_POST["URLExist"];
$SiteDesign = $_POST["SiteDesign"];
$SiteExample = $_POST["SiteExample"];
$URLExample = $_POST["URLExample"];

$query = "INSERT INTO tbl_info(info_id, FirstName, LastName, Phone, Email, SiteExist, URLExist, SiteDesign, SiteExample, URLExample) VALUES('','$FirstName','$LastName','$Phone','$Email','$SiteExist','$URLExist','$SiteDesign','$SiteExample','$URLExample')";
$result = mysql_query($query);

Cheers...and thanks for all the assistance and advice.

[lorax]

Strange but at least it's working now. If you get some time in the future it might be worth some investigation to determine why it wouldn't work before.

[coopster]

I must apologize as it was my mistake here, Rich. I even questioned myself as I was stating, "just remove the quotation marks" ... I should have followed my intuition and double-checked. I went through this long ago! I had to dig through some old maintenance scripts, but I found my comments. I do the same thing bobnew32 does, insert the word

NULL

without any quotation marks. What should have been stated was:

To accomplish this, insert the word

NULL

without quotation marks wherever you want a null value inserted:

$query = "INSERT INTO tbl_info values (NULL,'$FirstName',...

You could also have done this:

$info_id = 'NULL'; // Notice the quotation marks? Makes it the word NULL! 
$query = "INSERT INTO tbl_info values ($info_id,'$FirstName',...

...or as lorax suggested, leave it out entirely (requires a column list):

$query = "INSERT INTO tbl_info (FirstName,LastName, ...) 
 VALUES ('$FirstName','$LastName',...

Now let me explain. PHP will indeed treat your $info_id variable as null. If you print it out, nothing is displayed or printed to output, just the null value. However, and this is the key, MySQL expects the actual word

NULL

, not the value null. Big difference, and if you were to print out the sql statements you will see the differences.

In regards to the empty string (''), that is not a NULL value. It will indeed work on your numeric values, but I would recommend getting into the habit of using the word NULL as described. No offense meant to anyone here, and if after reading the reasoning and links stated in msg #8 you disagree, hey, that's your prerogative and I respect it. I'm merely sharing what knowledge I have on this subject. Regards, coopster.

[lorax]

>> It will indeed work on your numeric values, but I would recommend getting into the habit of using the word NULL as described.

Why? Not to be argumentative but rather to understand your thinking.

Re: offense - none taken - I'm open to learning. :)

[coopster]

Thanks, lorax, I appreciate that.

I'm somewhat of a purist I guess. The reason for my statement is because of the differences between NULL and empty strings (''). They are not the same thing and return quite different results in queries, comparisons, WHERE clauses, JOIN conditions, etc. In SQL, the

NULL

value is always false in comparison to any other value, even

NULL

!

The reason the empty string works on the numeric value in this case is because it is defined in MySQL as

AUTO_INCREMENT

. If it were just a plain old integer or character field, you will get much different results:

DROP TABLE IF EXISTS t1; 
--  
--  
CREATE TABLE t1 ( 
 t1_id SMALLINT UNSIGNED AUTO_INCREMENT, 
 mynbr SMALLINT UNSIGNED, 
 text1 CHAR(10), 
 text2 CHAR(10), 
 PRIMARY KEY (t1_id) 
); 
--  
--  
INSERT INTO t1 
 (t1_id, mynbr, text1, text2) 
 VALUES 
 (NULL, NULL, 'First', NULL), 
 ('', '', 'Second', ''), 
 ('Yuck', 'NULL', 'Third', 'NULL') 
; 
--  
--  
SELECT * FROM t1; 
+-------+-------+--------+-------+ 
¦ t1_id ¦ mynbr ¦ text1  ¦ text2 ¦ 
+-------+-------+--------+-------+ 
¦ 1     ¦ NULL  ¦ First  ¦ NULL  ¦ 
¦ 2     ¦ 0     ¦ Second ¦       ¦ 
¦ 3     ¦ 0     ¦ Third  ¦ NULL  ¦ 
+-------+-------+--------+-------+ 
3 rows in set (0.00 sec) 
--  
--  
SELECT * FROM t1 WHERE mynbr IS NULL; 
+-------+-------+-------+-------+ 
¦ t1_id ¦ mynbr ¦ text1 ¦ text2 ¦ 
+-------+-------+-------+-------+ 
¦ 1     ¦ NULL  ¦ First ¦ NULL  ¦ 
+-------+-------+-------+-------+ 
1 row in set (0.01 sec) 
--  
--  
SELECT * FROM t1 WHERE text2 IS NULL; 
+-------+-------+-------+-------+ 
¦ t1_id ¦ mynbr ¦ text1 ¦ text2 ¦ 
+-------+-------+-------+-------+ 
¦ 1     ¦ NULL  ¦ First ¦ NULL  ¦ 
+-------+-------+-------+-------+ 
1 row in set (0.00 sec) 

Notice that none of the empty strings were returned? That's the pure difference and the reason for my explanation. If I really wanted every row returned where the

text2

column does not have a value, I have to state it that way:

SELECT * FROM t1 WHERE text2 = ''; 
+-------+-------+--------+-------+ 
¦ t1_id ¦ mynbr ¦ text1  ¦ text2 ¦ 
+-------+-------+--------+-------+ 
¦ 2     ¦ 0     ¦ Second ¦       ¦ 
+-------+-------+--------+-------+ 
1 row in set (0.01 sec) 
--  
--  
Not Quite! Try again, but with a more definitive query: 
--  
--  
SELECT * FROM t1 WHERE text2 = ''OR text2 IS NULL; 
+-------+-------+--------+-------+ 
¦ t1_id ¦ mynbr ¦ text1  ¦ text2 ¦ 
+-------+-------+--------+-------+ 
¦ 1     ¦ NULL  ¦ First  ¦ NULL  ¦ 
¦ 2     ¦ 0     ¦ Second ¦       ¦ 
+-------+-------+--------+-------+ 
2 rows in set (0.00 sec) 

I wish I had time to show you a *real world* example of how this may apply, but hopefully this leads you into a different way of seeing things.

[Rich]

Thanks Coopster,
Your explanation was very clear, if not exhaustive ;), it makes allot more sense now.
Cheers

[lorax]

Ok, it seems that what you're driving at is to use NULL on text and varchar fields - which makes sense. For some reason I read your earlier post as suggesting we use NULL for Auto_Increment fields. Thank you for the explanation.

[coopster]

Not quite. I'm stating that when you want a null value in a column, make sure you specify

NULL

. There is a big difference between null values and empty strings. The example above is simply showing how different the values are in a few different column types, text/character being one of them. I didn't even get into date and time column types.

And yes, I am suggesting you use

NULL

for

AUTO-INCREMENT

columns, for the MySQL database at least. The reason I recommend this practice is because the authorities have so instructed us:

An integer column may have the additional attribute

AUTO_INCREMENT

. When you insert a value of

NULL

(recommended) or 0 into an indexed

AUTO_INCREMENT

column, the column is set to the next sequence value.

This recommendation is in a paragraph buried in the MySQL CREATE TABLE [mysql.com] manual pages. Recommendations are there for a reason is merely my point.

This may really come into play in future enhancements of the database. It is already starting. Read on in the same paragraph and you'll notice that:

As of MySQL 4.1.1, specifying the

NO_AUTO_VALUE_ON_ZERO

flag for the

--sql-mode

server option or the

sql_mode

server variable allows you to store 0 in

AUTO_INCREMENT

columns as 0, instead of generating a new sequence value.

Wow. Whole new ball game, eh?

[lorax]

>> Whole new ball game, eh?

Ah.. Sounds like MySQL is growing up. In actuality it sounds like they're setting the stage for MySQL to become a much more robust transactional server on a par with Oracle and the like. The fact that they're making steps towards stricter syntax is evidence they're laying the framework for something a bit more complex than we've seen thus far.