autoincrement and mysql select

Hylian

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?

netrepsa

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.

nnichols

// 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

statrat

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

dartcol

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 ...

statrat

$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);

dartcol

Thanks statrat. This makes it a lot easier. 🙂