Forum Moderators: coopster
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?
Tom
I think I need to preincrement the field, just not sure how.
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 ¦
+----+------------+
It's tough to find good details on MySQL's
AUTO_INCREMENTAUTO_INCREMENTAn integer column may have the additional attribute. When you insert a value ofAUTO_INCREMENT(recommended) or 0 into an indexedNULLcolumn, the column is set to the next sequence value. Typically this isAUTO_INCREMENT, wherevalue+1is the largest value for the column currently in the table.valuesequences begin with 1.AUTO_INCREMENT
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.
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
$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 NULLINSERT INTO tbl_info values (NULL, 'Rich',...
$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].
$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...
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.
$query = "INSERT INTO tbl_info values ('', '$FirstName', '$LastName', '$Phone', '$Email', '$SiteExist', '$URLExist', '$SiteDesign', '$SiteExample', '$URLExample')";
Is this an unusual issue or am I just missing something important?
show create table <your table name>
for us here.
$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.
NULLTo accomplish this, insert the word
NULL$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
NULLIn 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.
Why? Not to be argumentative but rather to understand your thinking.
Re: offense - none taken - I'm open to learning. :)
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
NULLNULLThe reason the empty string works on the numeric value in this case is because it is defined in MySQL as
AUTO_INCREMENT
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)
text2SELECT * 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)
NULLAnd yes, I am suggesting you use
NULLAUTO-INCREMENTAn integer column may have the additional attribute. When you insert a value ofAUTO_INCREMENT(recommended) or 0 into an indexedNULLcolumn, the column is set to the next sequence value.AUTO_INCREMENT
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 theflag for theNO_AUTO_VALUE_ON_ZEROserver option or the--sql-modeserver variable allows you to store 0 insql_modecolumns as 0, instead of generating a new sequence value.AUTO_INCREMENT
Wow. 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.
