create custom autonumber in mysql using php possibly?
Moderator: General Moderators
create custom autonumber in mysql using php possibly?
hi everyone.
i need to create a custom autonumber with 6 numbers, a dash followed by 2 random letters like so:
003011-AB
The first four numbers (34) refer to the autonumber field
the last two number (11) refer to the current year
and the two letters need to be random.
how can i accomplish this? any help would be appreciated.
thank you.
i need to create a custom autonumber with 6 numbers, a dash followed by 2 random letters like so:
003011-AB
The first four numbers (34) refer to the autonumber field
the last two number (11) refer to the current year
and the two letters need to be random.
how can i accomplish this? any help would be appreciated.
thank you.
Re: create custom autonumber in mysql using php possibly?
you could make a range, select 4 numbers from it, and combine everything together...
that should output 4 random numbers followed by the 11 for the year then a dash and 2 random letters.
Code: Select all
$numbers = range(1,100);
$rand = array_rand($numbers, 4);
$letters = array('A', 'B', 'C', etc...);
$randl = array_rand($letters, 2);
echo $rand. "11-" . $randl;
Re: create custom autonumber in mysql using php possibly?
i'm using the below code to generate an id number, the current year and a set of two letters/numbers which i'll insert as an auto number into a table later on.
<?php
$string= array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "1", '2', "3", "4", "5", "6", "7", "8", "9", "0");
$today = date("y");
$counter = "0000";
echo $counter+1;
echo "$today-";
echo $string[rand(0,36)];
echo $string[rand(0,36)];
?>
this code returns '111-YE' where YE is the set of two random letters/numbers.
i need it to be displayed as '000111-YE'.
I then insert that value as the autonumber data into the table.
Before inserting a new record into that table, i need to check the autonumber field to see what the last id number was, for example: 00111-YE.
I need to increment the number from 00111-YE to 00211-TY for the next entry and randomize the last two letters.
if the year changes, then the autonumber needs to reset back to 0001. so for 2012, the whole number would become:
000112-AS (0001 = autonumber, 12 = current year, AS = random two letters)
i have figured out how to get the current value for the year and the random two letters/numbers, i just need some help figuring out how to add the additional 000's at the front, then check the db to increase the number.
thanks.
<?php
$string= array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "1", '2', "3", "4", "5", "6", "7", "8", "9", "0");
$today = date("y");
$counter = "0000";
echo $counter+1;
echo "$today-";
echo $string[rand(0,36)];
echo $string[rand(0,36)];
?>
this code returns '111-YE' where YE is the set of two random letters/numbers.
i need it to be displayed as '000111-YE'.
I then insert that value as the autonumber data into the table.
Before inserting a new record into that table, i need to check the autonumber field to see what the last id number was, for example: 00111-YE.
I need to increment the number from 00111-YE to 00211-TY for the next entry and randomize the last two letters.
if the year changes, then the autonumber needs to reset back to 0001. so for 2012, the whole number would become:
000112-AS (0001 = autonumber, 12 = current year, AS = random two letters)
i have figured out how to get the current value for the year and the random two letters/numbers, i just need some help figuring out how to add the additional 000's at the front, then check the db to increase the number.
thanks.
Re: create custom autonumber in mysql using php possibly?
well, I got it all to echo out right so here's this, play with it and enjoy.
Code: Select all
<?php
$year = date("y");
$string = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
$flip = array_flip($string);
$rand1 = array_rand($flip);
$rand2 = array_rand($flip);
$counter = '0000';
$new = $counter + '1';
if($new <= '9') {
echo "000" . $new . $year . "-" . $rand1 . $rand2;
}
elseif($new >= '10') {
echo "00" . $new . $year . "-" . $rand1 . $rand2;
}
elseif($new >= '100') {
echo "0" . $new . $year . "-" . $rand1 . $rand2; }
elseif($new >= '1000') { echo $new . $year . "-" . $rand1 . $rand2; }
?>Re: create custom autonumber in mysql using php possibly?
as for your database question, I would do something like this...
Code: Select all
$query = mysql_query("SELECT autonumber FROM table ORDER BY table-id DESC LIMIT 1");
while($row = mysql_fetch_array($query)) {
$counter = $row['autonumber'];
}
$autonumber = substr($counter,0,4);
if(//blah blah blah
Re: create custom autonumber in mysql using php possibly?
I will need to insert this as the autonumber id number. How would i check to see of that number exists in the db (000111-) then make sure it increases to 000211-
plus once it changes to 2012, i'd need the counter to reset to 000112-
the combination of letters after the dash doesn't matter, but the counter 0001 should always increase until the next year occurs.
thx.
plus once it changes to 2012, i'd need the counter to reset to 000112-
the combination of letters after the dash doesn't matter, but the counter 0001 should always increase until the next year occurs.
thx.
danwguy wrote:well, I got it all to echo out right so here's this, play with it and enjoy.Code: Select all
<?php $year = date("y"); $string = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"); $flip = array_flip($string); $rand1 = array_rand($flip); $rand2 = array_rand($flip); $counter = '0000'; $new = $counter + '1'; if($new <= '9') { echo "000" . $new . $year . "-" . $rand1 . $rand2; } elseif($new >= '10') { echo "00" . $new . $year . "-" . $rand1 . $rand2; } elseif($new >= '100') { echo "0" . $new . $year . "-" . $rand1 . $rand2; } elseif($new >= '1000') { echo $new . $year . "-" . $rand1 . $rand2; } ?>
Re: create custom autonumber in mysql using php possibly?
I would use something like this...
Sorry need to take a quick break in my thought process to ask why you HAVE to have those leading 0's cant you just have $number +1; and not deal with those preceeding 0's?
Code: Select all
$query = mysql_query("SELECT autonumber FROM table ORDER BY table-id DESC LIMIT 1");
while($row = mysql_fetch_array($query)) {
$counter = $row['autonumber'];
}
$autonumber = substr($counter,0,4);
//this is where I get a little flustered in looking for the easiest solution
if($autonumberRe: create custom autonumber in mysql using php possibly?
i need to have those 0's because thats how my old numbering system was on paper.
000111-BY
i'm trying to recreate this number as the autonumber in a mysql db.
000111-BY
i'm trying to recreate this number as the autonumber in a mysql db.
Re: create custom autonumber in mysql using php possibly?
The only thing I can think of then is to have a whole crap ton of if and elseif statements, i.e.
and so on and so forth until you check the different possibilites. Wouldn't be that many if you do the && statements right.
I'm kinda at a loss for an easier solution with those leading 0's
Code: Select all
$year = date("y");
$string = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z");
$flip = array_flip($string);
$rand1 = array_rand($flip);
$rand2 = array_rand($flip);
$query = mysql_query("SELECT autonumber FROM table ORDER BY table-id DESC LIMIT 1");
while($row = mysql_fetch_array($query)) {
$counter = $row['autonumber'];
}
$autonumber = substr($counter,4,1);
if($uatonumber < '9' && substr($counter,3,1) == '0' && substr($counter,2,1) == '0' && substr($counter,1,1) == '0') {
$lnumber = $autonumber + '1';
$newauto = "000" . $lnumber . $year . "-" . $rand1 . $rand2;
}elseif(//start writing I'm kinda at a loss for an easier solution with those leading 0's
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: create custom autonumber in mysql using php possibly?
this code returns '111-YE' where YE is the set of two random letters/numbers.
i need it to be displayed as '000111-YE'.
Code: Select all
echo str_pad('111-YE', 9, "0", STR_PAD_LEFT);
// outputs 000111-YERe: create custom autonumber in mysql using php possibly?
I was just thinking about something to that effect on my lunch break. I didn't quite have the same thing, in fact mine was much more complicated and a lot more coding, but good call man.
Re: create custom autonumber in mysql using php possibly?
If the sequential number ($n) is known, the generation of the id can be reduced to a simple sprintf() statement.
The problem then is accurately determining the next sequential number. If you rely on a SELECT query to retrieve the last id, and update the number in PHP, there is a risk of having two instances of the script simultaneously inserting a new row with the same number.
To avoid that, I recommend employing SQL to maintain the id. As an example, consider a MySQL table with this structure:
Because of the custom format of the id, MySQL cannot increment the id itself as it would a simple integer. Notice that id is a PRIMARY KEY, but is not an AUTO_INCREMENT field.
The new id can be created in the INSERT query, which greatly reduces the chance that two rows will be created with the same sequence number.
Starting from the outside, the query is a standard INSERT...SELECT statement.
The outer SELECT reads the fields from a sub-query. That might seem unusual, but I found that it was necessary to make this query work. Without having the inner SELECT be a sub-query, the first row of a new year would have an empty string in the id field. That might be a MySQL bug because the the SELECT correctly returns a new id when it is not part of the INSERT query.
I'll skip the selected fields for a moment and jump down to the WHERE clause. The query is selecting rows from the example table where the current two-digit year appears in the correct position in the id. An underscore is a placeholder for a single unknown character. CONCAT() joins strings together into a single string.
From the rows that match the WHERE condition, the MAX() function selects the alphabetically-greatest id. Because our convention is to increment the first four numbers of the id, MAX() returns the last id to be inserted. The returned value will be NULL if there are no rows for the current year, so COALESCE() is used to ensure that a valid value is submitted to SUBSTR(), which then returns the first four characters of what it was given. In other words, SUBSTR() returns the latest sequence number (zero if there is none). The addition operation increments the number by one, then the new number is left-padded with zeros by LPAD() to bring the length of the sequence number back to four digits. The four digits are concatenated with the two-digit year, then a hypen and two random, uppercase letters. The RAND() function generates a random decimal >= 0 and < 1; so to map the number to the alphabet, the range is expanded by multiplying the number by 26 letters, then shifted by adding 65 (the ASCII value of 'A'). FLOOR() converts the decimal to an integer, and CHAR() converts the integer to its ASCII character equivalent.
In addition to the generated id, a literal string "New Name" is "selected" so it will be inserted into the name field of the new row.
Code: Select all
$n = 1;
$id = sprintf(
'%04u%02u-%c%c', // Format string
$n, // Sequence number
date('y'), // Two-digit year
mt_rand(65, 90), // Random ASCII number (65 = A, 90 = Z)
mt_rand(65, 90) // Random ASCII number (65 = A, 90 = Z)
);
echo $id;To avoid that, I recommend employing SQL to maintain the id. As an example, consider a MySQL table with this structure:
Code: Select all
DESCRIBE `example`
# +-------+-------------+------+-----+---------+-------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+-------------+------+-----+---------+-------+
# | id | varchar(9) | NO | PRI | | |
# | name | varchar(10) | NO | | | |
# +-------+-------------+------+-----+---------+-------+The new id can be created in the INSERT query, which greatly reduces the chance that two rows will be created with the same sequence number.
Code: Select all
INSERT INTO `example` (`id`, `name`)
SELECT * FROM (
SELECT
# id
CONCAT(
# Sequential number, incremented, zero-padded
LPAD((SUBSTR(COALESCE(MAX(`id`), '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)
)
),
# name
'New Name'
FROM
`example`
WHERE
`id` LIKE CONCAT('____', DATE_FORMAT(CURRENT_DATE, '%y'), '-__')
) `s`The outer SELECT reads the fields from a sub-query. That might seem unusual, but I found that it was necessary to make this query work. Without having the inner SELECT be a sub-query, the first row of a new year would have an empty string in the id field. That might be a MySQL bug because the the SELECT correctly returns a new id when it is not part of the INSERT query.
I'll skip the selected fields for a moment and jump down to the WHERE clause. The query is selecting rows from the example table where the current two-digit year appears in the correct position in the id. An underscore is a placeholder for a single unknown character. CONCAT() joins strings together into a single string.
From the rows that match the WHERE condition, the MAX() function selects the alphabetically-greatest id. Because our convention is to increment the first four numbers of the id, MAX() returns the last id to be inserted. The returned value will be NULL if there are no rows for the current year, so COALESCE() is used to ensure that a valid value is submitted to SUBSTR(), which then returns the first four characters of what it was given. In other words, SUBSTR() returns the latest sequence number (zero if there is none). The addition operation increments the number by one, then the new number is left-padded with zeros by LPAD() to bring the length of the sequence number back to four digits. The four digits are concatenated with the two-digit year, then a hypen and two random, uppercase letters. The RAND() function generates a random decimal >= 0 and < 1; so to map the number to the alphabet, the range is expanded by multiplying the number by 26 letters, then shifted by adding 65 (the ASCII value of 'A'). FLOOR() converts the decimal to an integer, and CHAR() converts the integer to its ASCII character equivalent.
In addition to the generated id, a literal string "New Name" is "selected" so it will be inserted into the name field of the new row.
Re: create custom autonumber in mysql using php possibly?
Thanks for your detailed response.
My question is, why do i need both an ID and a name field? I need the custom number to be inserted into the ID PK field.
My question is, why do i need both an ID and a name field? I need the custom number to be inserted into the ID PK field.
Re: create custom autonumber in mysql using php possibly?
The name field is just a placeholder for other fields that might be in your table.
Re: create custom autonumber in mysql using php possibly?
I've tried the following code and I'm receiving the error message:
Error, insert query failed
I'm not sure what I'm doing wrong.
Error, insert query failed
I'm not sure what I'm doing wrong.
Code: Select all
<?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)
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');
?>