"INSERT" question

[Piyer]

Tonight, while working on the coding for a new database, a thought struck me about another one that I've been using (call it Program A) that I think might have a flawed INSERT command. For the new db I'd taken the framework from an old one (call it Program B for clarity), complete with auto_increment in the data and so, although there is only 1 item in the table, its ID number is 93. Based on the code (see below) I used in Program A, the INSERT would go into #2 and not #94, and so several dozen records latter 93 would either be over written or cause an error (not sure which), as it is assuming that I'll never delete a whole row from the table.

So, my question is, what is the right way to get the value for $dragon_id -- namely, the next "auto_increment" number?

//get the information
$dragons_query = "SELECT dragon_id FROM clutch_dragons";
$dragons_result = mysql_query($dragons_query) or die(database_error("02010"));
$num_dragons = mysql_num_rows($dragons_result);
$dragon_id = $num_dragons + 1;

$addquery = "INSERT INTO clutch_dragons VALUES (".$dragon_id.", ".$eggs_id.", ".$clutch_id.", ".$weyrs_id.", '".$dragon_color."', '".$dragon_name."', '".$dragon_rider."', '".$dragon_status."', '".$owner_name."', ".$dragon_loc_now.")";
$addresult = mysql_query($addquery) or die(database_error("02012".mysql_error()));
echo "<p>Updated! I hope!</p>"; 

[Ron]

ALTER TABLE tbl_name AUTO_INCREMENT = 94

Hope this helps in your situation

I also read:

Lets say you had 1,2,3,4 as the values of your 4 records in the autoinc column. When you insert another row, you can put 17 (for example) as the value of the autoinc field. The next row you insert will have a value of 18.

Now, lets say you add another row and put the value at 16. The next value will be 19, since 17 and 18 already exist.

Source: http://dev.mysql.com/doc/mysql/en/ex...INCREMENT.html

Personally I wouldn't use an auto_increment value for anything other than its intended use which is to ensure that every row has a unique field for the primary index key.

Wait a sec.... I just reread your question.

YOU don't set the auto-increment field, you set the value to null and MySQL will create the value for you.

To get the maximum number in any record in a column, you'd use
Select max(fieldname) from tablename;
But you really don't want to do this.

Unless you need to set these IDs to a particular thing for a particular reason, use the alter table command once to set everything larger than 93, then set the dragonid to null, and do your insert. You'll get uniq numbers.

To count how many dragonids you have, do
Select count(*) from dragontablename;

[Ron]

restructured my responses

[Piyer]

Let's see if I've got this straight...

Instead of using this:

//get the information
$dragons_query = "SELECT dragon_id FROM clutch_dragons";
$dragons_result = mysql_query($dragons_query) or die(database_error("02010"));
$num_dragons = mysql_num_rows($dragons_result);
$dragon_id = $num_dragons + 1;

$addquery = "INSERT INTO clutch_dragons VALUES (".$dragon_id.", ".$eggs_id.", ".$clutch_id.", ".$weyrs_id.", '".$dragon_color."', '".$dragon_name."', '".$dragon_rider."', '".$dragon_status."', '".$owner_name."', ".$dragon_loc_now.")";
$addresult = mysql_query($addquery) or die(database_error("02012".mysql_error()));
echo "<p>Updated! I hope!</p>"; 

I should just use this:

$addquery = "INSERT INTO clutch_dragons VALUES ('NULL', ".$eggs_id.", ".$clutch_id.", ".$weyrs_id.", '".$dragon_color."', '".$dragon_name."', '".$dragon_rider."', '".$dragon_status."', '".$owner_name."', ".$dragon_loc_now.")";
$addresult = mysql_query($addquery) or die(database_error("02012".mysql_error()));
echo "<p>Updated! I hope!</p>"; 

[Piyer]

We seem to be overlapping each other in replies. *g*

[Ron]

Exactly -- well, I'm not sure you have the syntax for NULL correct. You need to drop the quotes.

Since you already have a 93, I'd perform the alter table command listed above first, but only once.

[Piyer]

Okay, let's review...

Assuming that the table has ID's 1-12, 14, 19, 22-30
And assuming that the auto_increment already reads 31

Then, all I need do is use this code alone:

$addquery = "INSERT INTO clutch_dragons VALUES (NULL, ".$eggs_id.", ".$clutch_id.", ".$weyrs_id.", '".$dragon_color."', '".$dragon_name."', '".$dragon_rider."', '".$dragon_status."', '".$owner_name."', ".$dragon_loc_now.")";
$addresult = mysql_query($addquery) or die(database_error("02012".mysql_error()));
echo "<p>Updated! I hope!</p>"; 

and the new ID will be 31 rather than the 24 it probably would have been under my original code. Right?

[edit: yeah, prolly no ' ' around the NULL]

[Ron]

Yup.

[Piyer]

Originally posted by Ron
Yup.

Cool! Thanks for the help, Ron.

[jason]

To get the value of the last auto increment value created in a database, use the PHP command mysql_insert_id() or the following SQL query after your INSERT query:

SELECT LAST_INSERT_ID() FROM clutch_dragons;

The PHP function will not work correctly if your using BIGINT for the column type of the auto_increment column in MySQL. Otherwise it will return an integer value equal to the value of the auto_increment id just created. The query version of the function will return a 1 row x 1 column result set wich will contain the same value as mysql_insert_id(), but which will work with BIGINT columns.

--Jason