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?

    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

        4 months later

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

              Write a Reply...