Auto generated sequence number staring from 001 (

Question

I need Auto generated sequence number staring from 001 ONLY FOR 3 DIGITS in PHP / MYSQL

ex: 001 next will be 002 and next will be 003 ... 009 .. 010 ..119..120..etc but number generated should be in sequence wise not like 001 then 120 or 008 etc , i need in sequence number.

actually i have product tracking system . where i need mixer of my main product id + auto sequence number ( only 3 digit can change if wanted ) hence my final product id becomes :

111-001 , 111-002 , 111-003 , 111-004 ....etc

Note: this auto sequence number will be insert in my mysql database ( after ADD button follows by Update query ( hence my auto sequence will be enter in database with Update query ))

Answer 1

This should work for you with slight modifications to fit ur requirement

 <?php
$connection = mysql_connect("localhost","root","")
or die("no connection");
$db_select=mysql_select_db("test",$connection)
or die("no connection to db");
$query3 = "SELECT * FROM simple";
$result3 = mysql_query($query3);
$count = mysql_num_rows($result3);
if($count == 0)
{
$seq = 1;
$ref = 111;
$a = sprintf("%04d", $seq);
$emp_id = $ref.'-'.$a;
$query= "INSERT INTO simple (emp_id) VALUES ('$emp_id')";
$result=mysql_query($query)or die(mysql_error());
}
else
{
    $query2 = "SELECT * FROM simple ORDER BY id DESC LIMIT 1";
    $result2 = mysql_query($query2);
    $row = mysql_fetch_array($result2);
    $last_id = $row['emp_id'];
    $rest = substr("$last_id", -4);  
    $insert_id = "$rest" + 1;
    echo $ars = sprintf("%04d", $insert_id);
    $ref = 111;
    $emp_id = $ref.'-'.$ars;
    $query= "INSERT INTO simple (emp_id) VALUES ('$emp_id')";
    $result=mysql_query($query)or die(mysql_error());
}

Answer 2

Try this code:

$maxDigit=5; //maximum digit of ur number
$currentNumber=0020;  //example this is your last number in database all we need to do is remove the zero before the number . i dont know how to remove it
$count=strlen($currentNumber); //count the digit of number. example we already removed the zero before the number. the answer here is 2
$numZero="";
for($count;$count<=$maxDigit;$count++)
     $numZero=$numZero+"0";

$newNum=$newNum+$currentNumber;

---

Output :00020

Answer 3

Just add the length 3 and zerofill to your id column.

ALTER TABLE  `YOUR_TABLE` CHANGE  
`id_column`  `id_column` INT( 3 ) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT