Inserting custom ID as PK.

[cjkeane]

Hi Everyone.

 

I'm trying to generate a non standard PK in the form of 000111-YU, where 0001 refers to the record number, the 11 refers to the year and the -YU is a set of random letters / numbers. The 0001 can increment throughout the year, but once 2012 occurs, the counter needs to be reset back to 0001.

 

I need this number to be generated and inserted into the table, but I also need to have form fields inserted into the db. for eg. FirstName and LastName are names of textboxes in a form.

 

this is what i have so far, but i keep receiving 'error, insert query failed.'

<?php

$db="db";
$link = mysql_connect('localhost', 'root', 'pw');

if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

$query = "INSERT INTO records (IDNumber, FirstName, LastName)
SELECT * FROM (
    SELECT
        # id
        CONCAT(
            # Sequential number, incremented, zero-padded
            LPAD((SUBSTR(COALESCE(MAX(IDNumber), '0'), 1, 4) + 1), 4, '0'),
            # Two-digit year
            DATE_FORMAT(CURRENT_DATE, '%y'),
            # Literal hyphen
            '-',
            # Two random letters (AA-ZZ)
            CHAR(
                FLOOR(65 + RAND() * 26),
                FLOOR(65 + RAND() * 26)
            )
        )
       
    FROM
        records
    WHERE
        IDNumber LIKE CONCAT('____', DATE_FORMAT(CURRENT_DATE, '%y'), '-__')
) ";

mysql_query($query) or die('Error, insert query failed');

?>

 

[Muddy_Funster]

.....jeezus!

 

ok, before we go anywhere with this, add the error report to the update like you did the connection string and let us know what the actual error your getting for now is.

mysql_query($query) or die('Error, insert query failed -- '.mysql_error());

[sasa]

try

<?php
$LName = $_POST['LastName'];
$FName = $_POST['FirstName'];
$db="db";
$link = mysql_connect('localhost', 'root', 'pw');

if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

$query = "INSERT INTO records (IDNumber, FirstName, LastName)
SELECT *, '$LName', '$FName' FROM (
    SELECT
        # id
        CONCAT(
            # Sequential number, incremented, zero-padded
            LPAD((SUBSTR(COALESCE(MAX(IDNumber), '0'), 1, 4) + 1), 4, '0'),
            # Two-digit year
            DATE_FORMAT(CURRENT_DATE, '%y'),
            # Literal hyphen
            '-',
            # Two random letters (AA-ZZ)
            CHAR(
                FLOOR(65 + RAND() * 26),
                FLOOR(65 + RAND() * 26)
            )
        )
       
    FROM
        records
    WHERE
        IDNumber LIKE CONCAT('____', DATE_FORMAT(CURRENT_DATE, '%y'), '-__')
) as a ";

mysql_query($query) or die('Error, insert query failed');

?>

[cjkeane]

I tried to run the code below you suggested and tried troubleshooting it, but the query always seems to fail saying - 'error insert query failed'. i'm really not sure why.

 

try

<?php
$LName = $_POST['LastName'];
$FName = $_POST['FirstName'];
$db="db";
$link = mysql_connect('localhost', 'root', 'pw');

if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

$query = "INSERT INTO records (IDNumber, FirstName, LastName)
SELECT *, '$LName', '$FName' FROM (
    SELECT
        # id
        CONCAT(
            # Sequential number, incremented, zero-padded
            LPAD((SUBSTR(COALESCE(MAX(IDNumber), '0'), 1, 4) + 1), 4, '0'),
            # Two-digit year
            DATE_FORMAT(CURRENT_DATE, '%y'),
            # Literal hyphen
            '-',
            # Two random letters (AA-ZZ)
            CHAR(
                FLOOR(65 + RAND() * 26),
                FLOOR(65 + RAND() * 26)
            )
        )
       
    FROM
        records
    WHERE
        IDNumber LIKE CONCAT('____', DATE_FORMAT(CURRENT_DATE, '%y'), '-__')
) as a ";

mysql_query($query) or die('Error, insert query failed');

?>

 

i have been trying a different approach which seems to work, however the 0 padding doesnt seem to be applied when i join the numbers together. i'd appreciate ay assistance you could provide. thank you.

 

<?php
// connect to the database
$dbcnx = @mysql_connect('localhost', 'root', 'pw'); 
if (!$dbcnx) {
  exit('<p>Unable to connect to the database server at this time.</p>');
}

if (!@mysql_select_db('db')) {
  exit('<p>Unable to locate the database at this time.</p>');
}

$MaxRecordNumber  = mysql_query("SELECT LPAD(MAX(SUBSTRING(IDNumber,1,4)),4,'0') as MaxRecordNumber FROM records");
$MaxRecordNumberResult = mysql_fetch_array($MaxRecordNumber);
$MaxYearInDB  = mysql_query("SELECT MAX(SUBSTRING(IDNumber,5,2)) as MaxYear FROM records");
$MaxYearResult = mysql_fetch_array($MaxYearInDB);
$today  = strftime('%y');
$seed_array = array_merge(range('A','Z'),range(0,9));
$rand_ary = array_rand($seed_array,2);
$rand_str = '';
foreach($rand_ary as $rk)
   $rand_str .= $seed_array[$rk];

if ($today > $MaxYearResult['MaxYear'])
{
       // If its a new year, reset counter to 0001.
$counter = "1";
$formatted = str_pad($counter, 3, 0, STR_PAD_LEFT);
echo $formatted; // this displays 0001 so i know the padding works
echo "<br />";
$NewIDNextYear =  $formatted . $MaxYearResult['MaxYear']+1 . '-' . $rand_str;
echo $NewIDNextYear; // this displays 111-$rand_str but needs to be 000111-$rand_str

} else {
        // otherwise increment the highest number used
$incrementmaxrecordnumber = $MaxRecordNumberResult['MaxRecordNumber']+1;
$NewIDCurrentYear = str_pad($incrementmaxrecordnumber,4,"0",STR_PAD_LEFT) . $MaxYearResult['MaxYear'] . '-' . $rand_str;
echo "<br>";
echo $NewIDCurrentYear; // this displays correctly as 002511-$rand_str

}

?>

 

[sasa]

change

$NewIDNextYear =  $formatted . $MaxYearResult['MaxYear']+1 . '-' . $rand_str;

to

$NewIDNextYear =  $formatted . ($MaxYearResult['MaxYear']+1) . '-' . $rand_str;

[cjkeane]

thanks. it worked!