How to make booking ID by PHP with MySQL Database?

I have case like this:

JS100829 0001
JS100829 0002

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

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

---

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

---

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.

---

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);

---

Are you looking for something like this

    JS=code
    10=year
    08=month
    29=date
    0001=the sequence of today first entry
    0002=the sequence of today second entry
    */
    $js="JS";
    $date=date('ymd');
    for($i=1;$i<=10;$i++)
    {
      $entry=str_pad($i,4,0,STR_PAD_LEFT);
      echo $js.$date.$entry."<br>";
    }
//output
/*
JS1008290001
JS1008290002
JS1008290003
JS1008290004
JS1008290005
JS1008290006
JS1008290007
JS1008290008
JS1008290009
JS1008290010
*/

---

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");