I'm using the MySQL autoincrement to increase a column . Say I post 8 rows of data and delete rows 4-8. The next autoincrement number is 9 right, yet how do I get that number? i can't count the rows using the mysql_num_rows or the sql statement count() because that would return 3 (1-3). Anybody know how to get that number?
autoincrement and mysql select
I use the same to create ID.
If I read $ID from my table it return the value of the undeleted records. Your last record ID would be the number you are looking for.
// run show status query for table "tbl_name"
$result = mysql_query "SHOW TABLE STATUS like 'tbl_name'";
// get the row - it only returns 1 row
$row = mysql_fetch_assoc($result);
// get the value
$next_auto = $row['Auto_increment'];
echo $next_auto;
Hope this helps
P.S. You shouldn't rely on this value in your code because another client could come along and insert into the table while you are doing stuff
mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query
http://www.php.net/manual/en/function.mysql-insert-id.php
How do you use this in a script? I've seen it mentioned a number of times but haven't seen it in practice.
For example, say there are three tables:
Both Tables 1 and 2 have data inserted into them from a form.
Both use auto_increment on their primary keys _id.
Table 3 has both primary keys from these two tables as foreign keys.
How do you get the primary keys into Table 3 in the same script?
I realize that we probably need to use mysql_insert_id() but I can't work out how?
Thanks for any help.
Oops! I promise to follow the links in the future ...
$dbstuff, etc.;
$sql1 = "insert into table1 values ('', $afield)";
$a = mysql_query($sql1);
$t1_auto_id = mysql_insert_id();
$sql2 = "insert into table2 values ('', $bfield)";
$b = mysql_query($sql2);
$t2_auto_id = mysql_insert_id();
$sql3 = "insert into table3 values($cfield, $t1_auto_id, $t2_auto_id)";
$c = mysql_query($sql3);
Thanks statrat. This makes it a lot easier.