How to make booking ID by PHP with MySQL Database?

Question

I have case like this:

JS100829 0001
JS100829 0002

JS=code
10=year
08=month
29=date
0001=the sequence of today first entry
0002=the sequence of today second entry

I need generate this code. Anyone can help me? thanks.

Answer 1

Not knowing where the "sequence counter" is maintained, this is my best shot (verbosity on purpose):

$code = 'JS';
$date = date('ymd');
$seq  = 1;
$id = sprintf('%s%s %04d', $code, $date, $seq);

Answer 2

Keep the value of $sequence in database or a config file. Keep incrementing it everytime an ID is generated. A database table with columns:

• Date: date ( also primary key)
• LatestID: int

Then, do something like this:

$sequence = mysql_fetch_array(mysql_query("select LatestID from seqtable where Date = CURDATE()"));
$sequence = $sequence + 1;

$code = "JS";
$mycode = $code . date("ymd") . str_pad($sequence, 4, '0', STR_PAD_LEFT);

mysql_query("insert into seqtable set LatestID = $sequence, Date = CURDATE ON DUPLICATE KEY UPDATE LatestID = $sequence");

Answer 3

For example, you have a database for these booking entries, this is my best guess:

//sequences
$datenow = date("Y-m-d H:i:s");
//i suppose u has load mysql db somewhere before this code
$q = mysql_query("SELECT COUNT(*) FROM yourtablename WHERE bookingdate = '$datenow'");
//how many squences today
$sequencedtoday = mysql_result($q, 0, 0);
//generate code:
$code = 'JS';
$ymd = date('ymd');
$squence = $squencedtoday+1;
$squence = str_pad($squence,4,0,STR_PAD_LEFT);
//return
echo $code.$ymd.' '.$squence;
//return: JS100829 0001

Answer 4

In case you're using the MyISAM or the BDB engine you can create a complex auto_increment sequence.
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html says:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as

 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.

This is useful when you want to put data into ordered groups.

demo:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('
  CREATE TEMPORARY TABLE soTest (
    `code` char(2),
    `date` DATE,
    `seq` int auto_increment,
    primary key(`date`, `seq`)
  ) engine=MyISAM
');
insertTestdata($pdo);
showData($pdo);

function insertTestdata($pdo) {
  $stmt = $pdo->prepare('INSERT INTO soTest (`code`,`date`) VALUES (?,?)');
  $rs = array(
    array('JS', '2010-08-29'), array('JS', '2010-08-29'), array('TA', '2010-08-29'),
    array('JS', '2010-08-30'), array('TA', '2010-08-31'), array('TA', '2010-08-31')
  );
  foreach($rs as $r ) {
    $stmt->execute($r);
  }
}

function showData($pdo) {
  $query = "SELECT code,Date_format(`date`, '%y%m%d') as dt, seq FROM soTest";
  foreach( $pdo->query($query, PDO::FETCH_ASSOC) as $row ) {
    printf("%s%s %04d\n", $row['code'],$row['dt'],$row['seq']);
  }
}

prints

JS100829 0001
JS100829 0002
TA100829 0003
JS100830 0001
TA100831 0001
TA100831 0002

Keep in mind:

When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.