How can i generate a unique incremental id like an invoice number (PHP)

Question

Please how can I generate a unique id that increments when a new invoice is created. For example, I have an invoice with the id NR20200001 so the Next invoice created the Id should be NR20200002.

This is my code below, I can't seem to increment the unique id I generated, so I just generated it randomly, but I need to generate it incrementally

<?php 
    $qry = "SELECT * from requisitions order by req_id desc";
    $result = mysqli_query($connection, $qry);
    $row    = mysqli_fetch_array($result);
    $lastid = $row['req_id'];

    if($lastid == ""){
        $number = "NR".date("Y").date("s");
    }
    else{
        $length=2;
        $number = substr($lastid,4);
        $number = intval($number);
        $number = "NR".date("Y").date("s").substr(str_shuffle(str_repeat($x='0123456789',ceil($length/strlen($x)))),1,$length);
    }
?>

If you never plan on deleting any records, an auto increment column should be just fine here. — Tim Biegeleisen, Dec 18 '20 at 10:41
[This might be of use](https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix). If you want to keep your format with the current year in front, you would have to create a cronjob to set the current auto_increment column to `0001` every year on january 1st. — Definitely not Rafal, Dec 18 '20 at 10:43
Please can I get an example, I'm kind of new to PHP, programming in general — Temi, Dec 18 '20 at 10:50
Just use an auto_increment field in the database. It guarantees you will get a unique ID back every time you insert a new row. The sequence is irrelevant, all that matters is that you can uniquely identify the record. You don't really need the "NR" text either, although I guess you could prepend that to the ID when showing it on screen, if it's felt to be important in some way (even though it's actually completely meaningless). — ADyson, Dec 18 '20 at 10:53
@Adyson This is shared information, so it conveys meaning. For instance, rival stationery companies allegedly make micro purchases on each others websites, and then study rate of change in invoice numbers to infer how successful they are. — Strawberry, Dec 18 '20 at 11:05
@Strawberry I meant that the "NR" prepended text specifically is effectively meaningless, since it does not change with each invoice number. So OP shouldn't worry about trying to generate numbers with that included, they can just include it for display if someone feels the need. I don't disagree that the changeable part of the ID can convey meaning (accidental or otherwise). Maybe a solution in the situation you've described would be to use GUIDs or something like that for the ID, at least to external viewers? — ADyson, Dec 18 '20 at 11:13
@ADyson *Just use an auto_increment field in the database. It guarantees you will get a unique ID back every time you insert a new row.* - but it does not guarantee adjacency. I'm sure that OP doesnot like this. — Akina, Dec 18 '20 at 11:13
@Akina why would adjacency be necessary or useful, though? In fact see Strawberry's comment above for why it might actually be actively undesirable. If OP wants to count the number of invoices issued, they should do a COUNT query on their table. — ADyson, Dec 18 '20 at 11:14
@ADyson Yep, I agree that there's confusion on the OP's part between data display, and data storage - but there maybe tax implications associated with non consecutive invoices — Strawberry, Dec 18 '20 at 11:15
As I see OP want the column to perform two tasks at the same time - both unique row identifying and invoices enumeration. I understand that this is wrong way - but I don't know does OP undersand this too... — Akina, Dec 18 '20 at 11:16
@Akina maybe not. In which case, we are explaining it to them - or attempting to anyway. No point in giving them a solution which takes the wrong approach. — ADyson, Dec 18 '20 at 11:17

score 0 · Answer 1 · edited Apr 11 '21 at 19:36

Firstly, you should set req_id to primary key and auto increment on database

Secondly, add LIMIT 1 for get only the latest req_id:

$qry = "SELECT * from requisitions order by req_id desc LIMIT 1";

Next, change condition follow as below:

<?php 
$qry = "SELECT * from requisitions order by req_id desc LIMIT 1";
$result = mysqli_query($connection, $qry);
$row    = mysqli_fetch_array($result);

if($row['req_id'] == "" || $row['req_id'] == null)
{
  $lastid = 0;
}
else
{
  $lastid = $row['req_id'];
}

  $nextid = sprintf("%04d", $lastid+1);  //set to 4 digit
  $number = "NR".date("Y").$nextid;

?>

Note: Do not delete record from table because it will make duplicate invoice number. You should add field flag for active/inactive.

Thanks, it worked but after submitting the data, it only accepted the int values, like after submitting let's say NR20200023, my requisition table only accepted 23 — Temi, Dec 24 '20 at 07:58

score -1 · Answer 2 · answered Dec 19 '20 at 08:20

-1

Please check code for increment number

6) ? $lastid : substr('00000000' . $lastid, -6); $getValue = "NR".date("Y").date("s").$getValue; } ?>

answered Dec 19 '20 at 08:20

Amol Kharate

49
4

How can i generate a unique incremental id like an invoice number (PHP)

2 Answers2