2

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

  • something something for loop – Jamie Taylor Nov 29 '13 at 10:25
  • why can't you define a composite primary key on productId and the number you are talking about? If you can, it won't be three digits but the combination looks like 111-1, 111-2, ..., 111-101, etc – Ravinder Reddy Nov 29 '13 at 10:29
  • bro i dont know it should be in loop or not , as i am bit confuse,, as after ADD button is fire my auto sequence is generated ( thats 001) and follow by my Update query its updated in my table .. again when next ADD button query is fire then this time 002 number is generated and so on and on.. –  Nov 29 '13 at 10:29
  • 1
    @ ravinder , actually it my client requirement 111-001 , 111-002 .. else what i had done was i was taking last inserted id and 111-1 , 111-2 ,111-4 etc , but client told he want all in 3 digit with sequence number wise –  Nov 29 '13 at 10:31
  • AFAIK we can't auto generate whole numbers with leading zeros and can't retain on storage. – Ravinder Reddy Nov 29 '13 at 10:35
  • [This answer should help you](http://stackoverflow.com/questions/5256469/what-is-the-benefit-of-zerofill-in-mysql) – Ravinder Reddy Nov 29 '13 at 10:39
  • ravinder thanks , i think this is what redreggae told be down , but as said i have already started using my primary id to other files as parameter ( foreign key ) . hence ? now should i delete all and start entry again ? –  Nov 29 '13 at 10:48

3 Answers3

2

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
bitWorking
  • 12,485
  • 1
  • 32
  • 38
  • and what if my id is primary coloumn , as i have already taken that id as parameter to other a.php file , and a.php included all this id as foreign key –  Nov 29 '13 at 10:41
  • it should be no problem. you can query `SELECT * FROM test WHERE id_test =3` and it returns the id `003` – bitWorking Nov 29 '13 at 10:44
  • so zerofill will only affect the display, not the old queries that you already have. if you alter the table in the way I did, you can keep your old id's. – bitWorking Nov 29 '13 at 10:51
  • what you say after adding Attributes as "UNSIGNED ZEROFILL" to, primary id , and lenght value as 3 , then what will be after 098 , 099 it will be 100 or 0100 , if it will be 100 then its ok and if it will be 0100 then ? the use as i need only 3 digit –  Nov 29 '13 at 10:56
  • it will be `100` because the length of integer is 3. – bitWorking Nov 29 '13 at 10:58
  • ohh then it is gr8 let me check right now , strange i haven't check this ? , yes because i am working live on my project hence need full confident on this before implementing anything or testing any –  Nov 29 '13 at 11:00
  • @ redreggae , dude one more thing i forget to say that my primary id type i have set as smallint(6) , so now ? should i do to set 3 –  Nov 29 '13 at 11:01
  • smallint is ok. an unsigned smallint can take values from 0-65535. the length value only affects the display not the memory!! so just alter to `smallint(3)`. – bitWorking Nov 29 '13 at 11:13
  • GR8 , NOW ALL SEEM TO FINE ... gr8 dude , thanks so much its save my time else i was searching for code.. :) –  Nov 29 '13 at 11:22
0

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());
}
praveen
  • 286
  • 1
  • 8
  • Why are you adding an undefined constant "E" and the year to the initial count? – John V. Nov 29 '13 at 10:35
  • Praveen , i think it not what i want , its simple 001 ,002 ,003 sequence number with each after ADD button query is fired –  Nov 29 '13 at 10:37
  • 1
    [Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). – zessx Nov 29 '13 at 10:39
  • This is to just give an idea hence i didn't focus much on the mysql execute statements – praveen Nov 29 '13 at 10:42
  • Even for exemple, use at least `mysqli_` functions, it doesn't take more efforts, nor time. – zessx Nov 29 '13 at 10:44
0

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

Allen Esteban
  • 1
  • 1