How to generate a ticket id with correct length by padding middle with 0

Question

I'm trying to generate a ticket id value with this format: yymm###### to be used in my database table. This expresses a 2-digit year, 2-digit month, and a 6-digit numeric id that is unique to the year-month prefix.

If there are no other tickets id's for the current month and year (April of 2017 at time of posting), the the numeric portion of the value should be default to 000000. The correct generated ticket id, in this case, should be: 1704000000.

If there are 1 or more pre-existing ticket id's in the current month - year, then the new ticket id should be 1 more than the highest ticket id.

I am currently using php to increment the ticket id like this:

$tkid = $rowt["TICKET_ID"] + 1;

But using this code, generates a ticket id like this: 17041

I have already put my ticket_id attributes to unsigned zerofill and type int 6.

Due to a design reason, I do not want to use auto increment.

Code:

$sqlt = "SELECT TICKET_ID FROM group where groupid = '$tid' ";
$ticket = mysqli_query($conn , $sqlt);
$rowcount = mysqli_num_rows($ticket);
if ($rowcount == 0)
    echo "No records found";
else 
    {
    $rowt = mysqli_fetch_assoc($ticket);
    }
$tkid = $rowt["TICKET_ID"] + 1;

<input type="text" readonly="readonly" value="<?php echo date('ym'). $tkid ;?>" name="TICKET_ID" >

first will get 000000.But if I register second customer, it will get 000001 — Qiu Xue, Apr 29 '17 at 05:58
Because after I register the first customer, the ticket id will updated to 000001. — Qiu Xue, Apr 29 '17 at 06:00

score 4 · Answer 1 · answered Apr 29 '17 at 06:12

4

Try changing this:

<?php echo date('ym'). $tkid ;?>

to this:

<?php echo date('ym') . str_pad($tkid, 6, '0', STR_PAD_LEFT); ?>

See str_pad().

answered Apr 29 '17 at 06:12

Joe Black

867
1
9
10

mickmackusa · Accepted Answer · 2017-04-29T08:37:44.733

If this was my project, I'd let mysql do all the work (and I'd use a prepared statement for security reasons).

To eliminate any KEYWORD / tablename clashes, I recommend backticking your group table.

My query will increment the highest TICKET_ID value for the selected groupid (and left-pad it with zeros if you are still using this code at the start of the next millenium!). If there are no rows for the selected groupid, then a new [yymm000000] value will be delivered in the resultset.

if($stmt=$conn->prepare(
      "(SELECT LPAD(`TICKET_ID`+1,10,'0') FROM `group`
        WHERE `groupid`=? ORDER BY `TICKET_ID` DESC LIMIT 1)
      UNION
       (SELECT CONCAT(DATE_FORMAT(CURDATE(),'%y%m'),'000000'))
      LIMIT 1;"
    )
){
    $stmt->bind_param("s",$tid);
    $stmt->execute();
    $stmt->bind_result($new_tkid);
    $stmt->fetch();
    echo "<input type=\"text\" name=\"TICKET_ID\" value=\"$new_tkid\" readonly=\"readonly\">";
    $stmt->free_result();
}else{
    echo "Query Syntax Error"; // echo mysqli_error($conn);
}

Not only does this provide essential security to your query, it puts all data manipulation in one place rather than mixing manipulation with echoing.

Functions used:

score 0 · Answer 3 · answered Apr 29 '17 at 08:07

0

Format request using printf

<?php printf('%s%06d', date('ym'), $tkid); ?>

Seriously, printf is a workhorse

answered Apr 29 '17 at 08:07

Jim U

3,318
1
14
24

How to generate a ticket id with correct length by padding middle with 0

3 Answers3

Related