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