generate sequential numbers for reference

[buzzby]

i would like to generate a sequential number.

basically i have a form and on that form is a textfield for a reference number. i would like that number generated. ideally i would like it to be in the format of 'A200' 'A201' and so on, or 'X300', 'X301' and so on. all sequential, nothing random.

i am not sure how to go about this. whether it needs to be a database tihng or text file thing. i spoke with another ozzu member about this but i remain perplexed as to how to go about it. i would like to see how it would work. the form needs to be viewable at any stage once it has been submitted. so maybe something along the lines of a guestbook. even tho it is not a guestbook it has many of the guestbook qualities: read submissions and write submissions and i would like to have them ordered by reference number.

any ideas on how to go about this?

[mourisj]

You could use a table keeping the max-values for each reference:

create table maxref (
prefix varchar(10),
max_id int)

where prefix contains the letters (A', 'x', ...) and max_id the highest value used so far for each prefix.

To have a new reference number, you increment the max_id, and append it to the prefix char(s).

update maxref set max_id = max_id+1 where prefix = $prefix;
$maxid = get_max_id($prefix); // define a function which selects the max_id for the $prefix given s a parameter
$new_ref = $prefix . $maxid

[buzzby]

using your example, how do i set the 'A' number to 250 and the 'X' number to 375 and then sequentially update the numbers whenever a prefix is chosen?

[PurpleMonkey]

you could split the string apart if you know how long the string is using the [man]sub_str[/man] function.

[mourisj]

In human terms, your table would be something like:

prefix | max_id
A      |   250
B      |    99
...    |   ...
X      |   375 

With your statement:
$qry = "update maxref set max_id = max_id+1 where prefix = '$prefix'"
you will do:
$result = mysql_query($qry) or die("Increment failed");

If $prefix = 'A', this means:

The result of: select max_id from maxref where prefix = 'A' is 250 (before update
$qry= "update maxref set max_id = max_id+1 where prefix = 'A'"
After update, the result of select max_id from maxref where prefix = 'A' will be 251
The other max_id values will remain unchanged.

I'm afraid I do not get all of your problem! Tell me, if this is not the answer to something you are looking for!

Have a nice day,

JJ Mouris

[mourisj]

You have to split your reference number into an alphanumerical and an numerical part if you do want to have alphanumerical identifiers.

As far as I know, it is not possible to increment alphanumerical identifiers you want to have!

Thats why I would proceed this way.

Another way is also possible.

Let's assume you have all your records with these alphanumerical identifiers in one table.
You would then have a field for the alphanumerical part, say "category" ; this field will contain the 'A', 'B', ...'X' values
Another field would contain the numerical part , say "num_id"; this field will contain the '250', '295', ...

When you want to add a new record in your table, you first calculate the max_value for the numerical part, if the alphanumerical part is known:

select max(num_id) from datatable where category = 'A'

You increment this value, and use it in your insert statement:
insert into datatable values ('A', $new_max_num_id, $data1, $data2, ...)

If this works will depend largely on how your database is made!
If your identifiers are possibly spread over multiple tables, and any of these tables can hold the max value, you will get into trouble with the previous code; in this case, you should prefer the way I described in my previous post.

If your max_value is always supposed to be found in the same table, this way could work.

However, if one day things change and other tables can have the max_value, you will have to change the design in productive use, rewrite your code, fill a table with the max_values, etc. which would be likely to cause you lots of trouble !

HTH

JJ Mouris