counting the records in a mysql dbase

mhoward

I have a large table into which data is inserted when a user puts in a request via my form.

I want to assign each user a unique ID when they click submit and their data is put into the database. Until now I have been using 'auto increment' and LAST_INSERT_ID but it's not really what I want.

What I want to do is query the database for the last number in the ID field. I then want to assign the ID "RQSTLASTENTRY+1" for each entry so they will show as RQST0001, RQST0002 etc...

Does that make sense? How can I achieve this?

raymie

In my opinion auto increment is the better option but

select max(id) from table;

mhoward

but if I use the auto increment, I can't use letters as part of the ID can I?

raymie

No I do not think so, but you could split the key field and have an alpha key with a numeric key which autoincrements to prevent duplicates (I am not saying that this is actually a good idea however).

mhoward

Thanks for your help on this. In the end I created another field in my table called 'ref'. I left the 'id' column as autoincrement and then used

$last =  'SELECT MAX(id) from table'

to get the highest entry.
I then used

$calcref = "$last + 1";
$newref = "STRVL$calcref";
$sql = mysql_query("INSERT INTO table VALUES (LAST_INSERT_ID(), '$newref' etc...

tomhath

You were better off staying with the autoincrement. What you have can get into what's called a "race condition".

http://www.awprofessional.com/catalog/article.asp?product_id=%7B2771D4C6-05B6-4204-877F-604CECFA4534%7D

Raymie's first post was right, use two columns and concatenate them when you need them together, but keep the autoincrement column as the key.