Cohesive Web

In MySql database the auto increment value of a field is a very important feature that we use frequently for our projects. Value of a auto increment field is a auto generated number. This number generates when you insert a row in the table. Most of the cases we make data type of that field as integer and as primary key. In many of my projects I have used this field value as the primary ID and mapped other data stored in other tables.

In PHP a new value of this type of field can be accessed after inserting a record in the table, using the function mysql_insert_id(). But you can many such cases where you need to access this value before inserting the record. Earlier I used a technique to get that value. Execute a simple sql query to get that value. The sql is:
[code lang=”sql”]
SELECT MAX(prodId) FROM ProductTable
[/code]
Where prodId is the field name with auto increment and is the ProductTable table name. Now add 1 with returned value and you will get the new value for that field. However this process may produce incorrect result if you do delete operation upon that table. Let me explain a little more.

Say currently the ProductTable contains 100 records. So the possible next record Id will be 101. Now if you delete record no. 21 or 49 or 65 or 78 you will still have the next record Id as 101. But say if you delete record 100. Logically you should get the next record Id as 100. Your sql query will give the value as 99 and adding 1 with it will produce 100. Seem no problem, right? Wrong, just insert a record and you will find the prodId for the record is 101. How this happened? MySql database stores 1 as starting value when you create the table. Now every time you insert a record the auto increment value get incremented by 1.This way it produces a unique number every time you insert a record in the table. Generally table in MySql database don’t reuse the deleted Id. As a result the above said process will not work in case deletion of record.

Well here is a solution to overcome this problem. I searched a lot to find out some method to find out the auto increment value. I found out a sql statement produces information related to the table. Within those information I found out the auto increment value. The sql statement is:
[code lang=”sql”]
SHOW TABLE STATUS LIKE ‘ ProductTable’
[/code]

So I wrote down a function in PHP to get the auto increment value of a table without inserting a record.
[code lang=”php-brief”]
function FnGetAutoIncermentValueForTable(TableName)
{
$Sql = “SHOW TABLE STATUS LIKE ‘”.$TableName.”‘”;
$result = mysql_query($Sql);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
return $row[‘Auto_increment’];
}
[/code]

However I hadn’t found out any method in PEAR::DB classes to get the auto increment value for a table. It don’t have any method like mysql_insert_id(). So I had created another function to use with DB classes.
[code lang=”php-brief”]
function FnGetAutoIncrementValue($pArgumentsInArray=NULL)
{
$Sql = “SHOW TABLE STATUS LIKE ‘”.$this->table.”‘”;
$RowResult = $this->db->getRow($Sql, $this->fetchmode);
return $RowResult[‘Auto_increment’];
}
[/code]

I will be very happy have your opinion or suggestion regarding this. Till next post 🙂