Create unique value

[scholzr]

I am trying to create a unique index value when uploading records to my table. In the past, I have used the autoincrement option in my MySQL database, but for this table I want to create the index value based on several differant variables.

So far this is what I have:

[PHP]$lotid=$nameid_ $date_$recordnu mer[/PHP]

What I want to do is say user 014 is uploadin thier first record of the day on 2006-12-01 the script would be (and I know that this is not necessarily what the code would look like, but you get the idea):

[PHP]$nameid=014
$date=2006-12-01
$recordnumber=0 001

$lotid=014_2006-12-01_0001[/PHP]

However, if they have already posted a record on that day and recordnumber 0001 is used, I want $recordnumber to echo 0002 and so on.

Any ideas?

[ronverdonk]

Get the highest stored sequence number by performing a SELECT LIKE and, depending on the retrieved value, increment the retrieved record number or, if there is none, set it to 001.

SELECT SUBSTR(lotid,16) AS seq 
       FROM a 
       WHERE lotid like '2006-10-25_014_%' 
       ORDER BY seq DESC 
       LIMIT 1;

Ronald :cool: