Duplication in serial number while generating tickets

[amith_ami]

I'm trying to generate barcode tickets after successful booking and it's working fine. But yesterday (08-Jan-2017) night at 11.59 pm, one customer booked 2 tickets but the ticket sl.no was duplicate. Tthat is both tickets sl.no was 1. Actually it should be 2. But the next customer's tickets sl.no was correct, it's 3,4,5. Is there any mistake in the code below where the tickets get generated?

for($j=1;$j<=$cat_array[$i];$j++) {
  $sel_max_dt =  mysql_query("select MAX(DT) AS maxdt, MAX(REC_NO) AS maxrec FROM  tkt_barcode");
  //to select max date and max rec no.
  $row_max_dt = mysql_fetch_array($sel_max_dt);
  $dt_max = $row_max_dt['maxdt'];
  //$no_max = $row_max_dt['maxno'];
  $recno = $row_max_dt['maxrec'];
  if($dt_max ==  date("Y-m-d") && $recno == $TxnID) {
    //if same date and same bkid  barcode slno continues.
    $sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE REC_NO=".$TxnID);
    $row_sel_no = mysql_fetch_array($sel_no);   
    $tkt_slno = $row_sel_no['maxno'];
    $tkt_slno = $tkt_slno + 1;
  }
  if($dt_max ==  date("Y-m-d")&& $recno != $TxnID) {
    //if same date and different bkid  barcode slno continues.
    $sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE DT='$dt_max' ");
    $row_sel_no = mysql_fetch_array($sel_no);   
    $tkt_slno = $row_sel_no['maxno'];
    $tkt_slno = $tkt_slno + 1;
  }
  if($dt_max != date("Y-m-d") && $recno != $TxnID) {
    //if not same date start barcode slno from 1.
    $tkt_slno = 1;
  }
  $sel_sl_no = mysql_query("select MAX(SL_NO) AS slno FROM  tkt_barcode WHERE REC_NO=".$TxnID);
  $row_slno = mysql_fetch_array($sel_sl_no);
  $slno = $row_slno['slno'];
  $bartkt_slno = $slno + 1;
  if($i==1 && $j>$count_promo_adult && $arr_edit["PROMO_CODE"]!=""){
    //adult cat and total adult count > total promo adult count
    $disc_adult = $adult_amt;
    $discamt=$adult_amt - $disc_adult ; //  discount amt
    $rate=$adult_amt;  //normal rate
    $net_amt=$disc_adult; //discount rate
    $pro_cd ="";
    //promo code is null when total adult count > total promo adult count
   } //end if
  //string pading digits 
  $len_txn =strlen($TxnID); // bookid length 
  $pad = 6; //str_pad digits
  if($len_txn > 5) {
    if($len_txn==6) { $pad = $pad-1;}// if 6 digit
    if($len_txn==7){ $pad = $pad-2;}// if 7 digit
  }
  $fullbar = $TxnID.str_pad($tkt_slno, $pad, 0, STR_PAD_LEFT).$clnd_date.$counter_no;
  //barcode no
  $fields  = "BARCODE_SLNO,DT,FIN_YEAR,Counter_Code,Branch_cd,REC_NO,BARCODE,TICKET_TYPE,CATG_CD,AMT,DISC_AMT,SL_NO,NET_AMT,BCODE_CATG_SLNO,BCODE_SLNO_GEN,BCODE_SLNO_CATG_TOT,DAY_TYPE,Check_in_date,PROMO_CODE,TAX,S_TAX,conv_base_rate,conv_tax_rate";
  $values = "'$tkt_slno','$booked_date','$fin_year','$counter_no','$branch','$TxnID','$fullbar','$cat_name_tkt','$i','$rate','$discamt','$bartkt_slno','$net_amt','$j','$count','$cat_tot','$type_day','$date_check','$pro_cd','$tax_each','$ser_tax_each','$conv_base','$conv_tax'";
  $sql_tkt = $objA->insert_fields(TABLEPREFIX.'tkt_barcode',$fields,$values);
}//end for loop

[pty]

What probably happened is that your customer created an order, then while the above code was running they created another. As you're using max to work out which number to allocate and you've not yet saved the first record, both have the same id. Without knowing your transaction isolation level I can't say why your sequence looked correct.

Rule of thumb, for automatically generating numbers, use a sequence (known as auto increment in MySQL). Also, if your ids are meant to be unique, consider making that column a primary key; if not use a unique index.

[amith_ami]

@pty, I can't use auto increment for BARCODE_SLNO because it should start from 1 on each day.

[rubberman]

Each day you should create a new table with an auto-increment column, otherwise you will have to change your algorithm and use something else for the SN. There are other ways around this problem, but they are too complex to go into here. I've had to deal with them in the past and none of the solutions are simple and require some serious coding (mostly not complex, but careful).

[amith_ami]

@ rubberman, creating new table each day is not possible, because the users can book the ticket until 9 am. Also daily booking report will get generated at this time.

[pty]

Is reporting the only reason you need this number? If that's the case, remove it from your application and add it to your report. In PostgreSQL you an use a window function for this, but in MySQL do something like this. Imagine you have a table of names:

create table people (id int primary key auto_increment, name varchar(20)) ;

insert into people (name) values ('John'), ('Paul'), ('George'), ('Ringo');

Now, you want them numbered by something other than the id:

select
  (@i := @i+1) as number,
  id,
  name
from people, (select (@i := 0)) j
order by name desc;

    | number | id |   name |
    |--------|----|--------|
    |      1 |  4 |  Ringo |
    |      2 |  2 |   Paul |
    |      3 |  1 |   John |
    |      4 |  3 | George |

There's a better example here.

Providing your query is constructed properly and is only selecting records from a particular day, and your results are ordered, your results will be the same every time. Plus, your application is simpler, you have fewer columns and you won't run into transaction-related bugs.

[amith_ami]

Thanks all, for your help. I found where the issue occurs and added another condition to avoid that and its working fine.

if($dt_max !=  date("Y-m-d")&& $recno == $TxnID) {
    //if different date and same bkid  barcode slno continues.
    $sel_no = mysql_query("select MAX(BARCODE_SLNO) AS maxno FROM  tkt_barcode WHERE DT='$dt_max' ");
    $row_sel_no = mysql_fetch_array($sel_no);   
    $tkt_slno = $row_sel_no['maxno'];
    $tkt_slno = $tkt_slno + 1;
  }

[diafol]

I don.t understand the need for this. Surely you just insert a new ticket and get the last added id? You can take the max id from the previous date away from it to generate the id starting at 1 every day.

[diafol]

Ok, now I have time - to expand...

SELECT id FROM tkt_barcode WHERE DATE(DT) = SUBDATE(CURRENT_DATE, 1) ORDER BY id DESC LIMIT 1

To get max ID from previous day. INSERT your ticket:

INSERT INTO ...

To get barcode for the day - use PHP's mysql_insert_id() (as you're using deprecated code!) to get the id of this inserted record. The barcode is simply this ID minus the max ID from the previous day.

You could update the last insert with the barcode number if you wanted to in order to allow checking later on.

BTW - the SQL may need work as there may not be an entry for the prev day !