Currently i am generating Bill No in my php mysql code with following code and its working fine (billno is field and iap3 is table name in mysql. its auto increment bill no on the basis of previously added value in database table)
"But I want that it should generate Bill No in following order and as fellow
B-0000-0001 (B represent company, then next 2 zero show month of current date(for example as today month is 09), then next 2 zero show year of current date ( for exampe 23) and in the end 0001 increment per patient. when month and year changed then this last 0001 should reset to 0001 again. How can i do this ??? Help from this precitegous forum required to solved my querry "
OK, what have you tried so far, and what went wrong?
I’d expect you could do something like this pseudo-code:
select bilno from iap3 where bilno like 'B-2309-%' order by bilno desc limit 1
retrieve the row
if there isn't a row: bill number is "00001"
otherwise, bill number is one higher than the one you retrieved.
You’ll need to consider some sort of locking method to make sure that two people don’t end up with the same bill number. Obviously you’ll need to pass in the current month and year as parameters into your query, rather than hard-coding 2309 as I have in the pseudo-code.
I can’t help thinking that there is a better way of doing this, though.
My opinion is that the second part of the bill number should be YYMM rather than MMYY, though you might have some reason to use the order that you stated. I can’t think of why it would be better to sort by month then year, rather than by year then month.
There will be a time between you selecting the most recently used bill number, and then storing the new bill. In that time, another user could have attempted the same operation. It might only be a short time, but it’ll be there. It’s something you need to consider from the start, rather than trying to fix later.
Using a unique constraint on the column will prevent duplicates from being saved, of course. But that will be an unpleasant user experience which would be better to avoid in the first place.
Is it your intention to do these steps?
Generate the next bill number based on the database
Present that to the user in a form, for them to fill in more details and submit?
Submit form and store in database.
If so, that’s an even longer time period when you’re relying on no-one else trying to create a new bill. I was thinking that you’d have two queries, one to find the last bill number and the next to store the new one, with no waiting for the user between the two, and there would still be the chance of duplicates - albeit with the second one being rejected - even in that short time if you don’t do some kind of transaction or other locking. Once there’s a wait for a user in there, even more risk that they’ll get a phone call or get distracted. Does the user need to know the bill number before it is stored?
select bilno from iap3 where bilno like ‘B-2309-%’ order by bilno desc limit 1
retrieve the row
if there isn’t a row: bill number is “00001”
otherwise, bill number is one higher than the one you retrieved.
2309 representing year and month, when month and year of current date will changed, then how can we reflect that change in that querry ?
Hi @itsumarnazir!! First you need to modify your PHP code to include the current month and year in the bill number. You also need to reset the last four digits to 0001 when the month or year changes.
Such as:
<?php // Get the current month and year as two-digit strings $month = date(“m”); // e.g. 09 $year = date(“y”); // e.g. 23
// Get the last bill number from the database $query = “SELECT bilno FROM iap3 ORDER BY bilno DESC”; $result = mysqli_query($conn,$query); $row = mysqli_fetch_array($result); $lastid = $row[‘bilno’];
// Check if the last bill number is empty or has a different month or year if(empty($lastid) || substr($lastid, 2, 2) != $month || substr($lastid, 4, 2) != $year) { // Start a new sequence with 0001 $number = “B-$month$year-0001”; } else { // Increment the last four digits by one $idd = substr($lastid, 7); // e.g. 0001 $id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002 $number = “B-$month$year-$id”; } ?>
Thanks alot Lara. its working fine when I entered first record. But It do not increment to 1 . for example my first record bil no is “B-0923-0001” and it enter successfully in database table. But when I tried to open form again for 2nd entery then bill no is still “B-0923-0001” instead of “B-0923-0002”
can u look at it as well please. will be extremely thankful to you for your precious time and effort
<?php
$month = date("m");
$year = date("y");
// e.g. 23
// Get the last bill number from the database
$query = "SELECT bilno FROM iap2 ORDER BY bilno DESC";
$result = mysqli_query($conn,$query);
$row = mysqli_fetch_array($result);
$lastid = $row['bilno'];
// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || substr($lastid, 2, 2) != $month || substr($lastid, 4, 2) != $year)
{
$number = "B-$month$year-0001"; }
else {
$idd = substr($lastid, 7);
$id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT);
$number = "B-$month$year-$id"; }
?>
Use mysqli_fetch_assoc() instead of mysqli_fetch_array() in your code.
Here is how you can modify your code:
<?php // Get the current month and year as two-digit strings $month = date(“m”); // e.g. 09 $year = date(“y”); // e.g. 23
// Get the last bill number from the database $query = “SELECT bilno FROM iap2 ORDER BY bilno DESC”; $result = mysqli_query($conn,$query); // Use mysqli_fetch_assoc() to get an associative array of the fetched row $row = mysqli_fetch_assoc($result); // Use $row[‘bilno’] to get the last bill number $lastid = $row[‘bilno’];
// Check if the last bill number is empty or has a different month or year if(empty($lastid) || substr($lastid, 2, 2) != $month || substr($lastid, 4, 2) != $year) { // Start a new sequence with 0001 $number = “B-$month$year-0001”; } else { // Increment the last four digits by one $idd = substr($lastid, 7); // e.g. 0001 $id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002 $number = “B-$month$year-$id”; } ?>
As @Drummin said above, this code needs to be run when you store the user-submitted data, not when you draw the form for them to fill in. At the end of the process, you display the bill number to the user, once it’s all been stored. That way they don’t need to care whether it has changed from the initial display.
Respected Lara I am facing one issue with that code, Instead of B, if i used two latter (for example “BA” instead of only B) then its stop incrementing the number for next entry.
If the Company Initial(s) are going to be varied then your code needs to use dynamic variables instead of hard coded numbers. Say in your “companies” table you store the company initials and so during this process you query for those initials and set them to a variable $CI.
You can then get the length of those initials and set that to a variable to be used in your code.
$CIcnt = strlen($CI);
Then you can calculate the total offset by adding in the known number of dates and dash spaces.
$offset = $CIcnt + 6;
Then you need to change the IF condition where you check for the month and year so it accounts for the length of the initials. So for the month the offset is $CIcnt + 1 for the leading dash, and for the year the offset it would be $CIcnt + 3 to account for the dash and month.
Now the month and year will be recognized regardless of the initials used and a new set of numbers will start when the month and year is not found.
Then when building the numbers you would use the dynamic variables $CI and $offset instead of hard coded values.
<?php
$CI = "BAP"; //Example only
$CIcnt = strlen($CI);
$offset = $CIcnt + 6;
// Get the current month and year as two-digit strings
$month = date("m"); // e.g. 09
$year = date("y"); // e.g. 23
// Get the last bill number from the database
$query = "SELECT bilno FROM iap2 ORDER BY bilno DESC";
$result = mysqli_query($conn,$query);
// Use mysqli_fetch_assoc() to get an associative array of the fetched row
$row = mysqli_fetch_assoc($result);
// Use $row[‘bilno’] to get the last bill number
$lastid = $row['bilno'];
// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || (substr($lastid, $CIcnt + 1, 2) != $month) || (substr($lastid, $CIcnt + 3, 2) != $year)) {
// Start a new sequence with 0001
$number = "$CI-$month$year-0001";
} else {
// Increment the last four digits by one
$idd = substr($lastid, $offset); // e.g. 0001
echo $idd;
$id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT); // e.g. 0002
$number = "$CI-$month$year-$id";
}
echo $number;
?>
thanks alot for detailed answer, It greatly help me to increase my knowledge But currently I am using fixed Company Initials which have only 2 fixed initials. I will thankful to you if u help me to fix this code with fixed intials in below mentioned code
<?php // Get the current month and year as two-digit strings
$month = date("m");
$year = date("y");
// e.g. 23
// Get the last bill number from the database
$query = "SELECT patientno FROM iap2 ORDER BY patientno DESC";
$result = mysqli_query($conn,$query);
// Use mysqli_fetch_assoc() to get an associative array of the fetched row
$row = mysqli_fetch_assoc($result);
// Use $row['patientno'] to get the last bill number
$lastid = $row['patientno'];
// Check if the last bill number is empty or has a different month or year
if(empty($lastid) || substr($lastid, 2, 2) != $month || substr($lastid, 4, 2) != $year) {
// Start a new sequence with 0001
$number = "Ls-$month$year-0001";
}
else
{
// Increment the last four digits by one
$idd = substr($lastid, 8);
// e.g. 0001
$id = str_pad($idd + 1, 4, 0, STR_PAD_LEFT);
// e.g. 0002
$number = "Ls-$month$year-$id";
}
?>
As the code you posted uses hard coded values I suggest you use the code I posted and set the company initials as $CI. If you really are insisting on using that version I will modify it but my version will allow for the initial changes should the need arise.