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" >
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:
Format request using printf
<?php printf('%s%06d', date('ym'), $tkid); ?>
Seriously, printf is a workhorse