Autoincrement with Prefix

Hi, I have a table with some fields and 2 of them are:
ID int(10) PK AI
RefNum varchar(50)
I want the reference number to start with the prefix RE-001 and I want everytime i insert a new record to increment it accordingly
ID          RefNum
1           RE-001
2           RE-002
3           RE-003
Below I am inserting my insert statement which I made with dreamweaver
Also at the page the admin enters the information I want the textbox of the reference number to be filled automatically with the next available Ref Num
here is the textbox (note that is read only as I dont want it to be changed)
<input name="refNumber" type="text" class="style811" id="refNumber" size="50" maxlength="50" readonly="readonly" />
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) { 
  $insertSQL = sprintf("INSERT INTO listings (id, datePosted, featured, listingType, refNum, contactName, contactPhone, contactMobile, contactEmail, region, area, address, price, priceRequested, beds, baths, parking, propType, coveredArea, pool, furnished, deeds, postedBy, deliveryDate, mapLocation, briefComments, comments, image1, image2, image3, image4, image5, image6, image7, image8) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", 
                       GetSQLValueString($_POST['id'], "int"), 
                       GetSQLValueString($_POST['datePosted'], "text"), 
                       GetSQLValueString(isset($_POST['featured']) ? "true" : "", "defined","'Yes'","'No'"), 
                       GetSQLValueString($_POST['listingType'], "text"), 
                       GetSQLValueString($_POST['refNumber'], "text"), 
                       GetSQLValueString($_POST['contactName'], "text"), 
                       GetSQLValueString($_POST['contactPhone'], "text"), 
                       GetSQLValueString($_POST['contactMobile'], "text"), 
                       GetSQLValueString($_POST['contactEmail'], "text"), 
                       GetSQLValueString($_POST['region'], "text"), 
                       GetSQLValueString($_POST['area'], "text"), 
                       GetSQLValueString($_POST['address'], "text"), 
                       GetSQLValueString($_POST['price'], "text"), 
                       GetSQLValueString($_POST['sellerPrice'], "text"), 
                       GetSQLValueString($_POST['bedrooms'], "text"), 
                       GetSQLValueString($_POST['bathrooms'], "text"), 
                       GetSQLValueString($_POST['parking'], "text"), 
                       GetSQLValueString($_POST['propertyType'], "text"), 
                       GetSQLValueString($_POST['coveredArea'], "text"), 
                       GetSQLValueString($_POST['pool'], "text"), 
                       GetSQLValueString($_POST['furnished'], "text"), 
                       GetSQLValueString($_POST['deeds'], "text"), 
                       GetSQLValueString($_POST['postedBy'], "text"), 
                       GetSQLValueString($_POST['deliveryDate'], "text"), 
                       GetSQLValueString($_POST['maplocation'], "text"), 
                       GetSQLValueString($_POST['commentsBrief'], "text"), 
                       GetSQLValueString($_POST['comments'], "text"), 
                       GetSQLValueString($_POST['image1'], "text"), 
                       GetSQLValueString($_POST['image2'], "text"), 
                       GetSQLValueString($_POST['image6'], "text"), 
                       GetSQLValueString($_POST['image6'], "text"), 
                       GetSQLValueString($_POST['image5'], "text"), 
                       GetSQLValueString($_POST['image6'], "text"), 
                       GetSQLValueString($_POST['image7'], "text"), 
                       GetSQLValueString($_POST['image8'], "text")); 
  mysql_select_db($database_RealEstateOnlineCy, $RealEstateOnlineCy); 
  $Result1 = mysql_query($insertSQL, $RealEstateOnlineCy) or die(mysql_error()); 
  $insertGoTo = "/RealEstateOnline/admin/addListingSuccess.php"; 
  if (isset($_SERVER['QUERY_STRING'])) { 
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?"; 
    $insertGoTo .= $_SERVER['QUERY_STRING']; 
  header(sprintf("Location: %s", $insertGoTo)); 

Open in new window

Who is Participating?
"Also at the page the admin enters the information I want the textbox of the reference number to be filled automatically with the next available Ref Num"

You cannot do this, unless you can be absolutely sure that only one person will ever be entering data. Otherwise, by the time the admin submits the form, the reference number suggested may already be taken, and you'd end up with duplicates.

If you are still 100% sure you want to do this, you need to either use something like

SELECT max(id) + 1 AS nextid FROM listings

or better yet, the id field is autoincrementing (which it should be), then

SELECT Auto_increment FROM information_schema.tables WHERE table_name='listings';

Regarding the prepended reference number, you should simply prepend your "RE-" part when it comes to displaying the data, there is no reason at all to store it anywhere. Again, if you decide to disregard this advice, you have two choices:

1) If you disregarded my first advice about the display of the next available ID, you can simply use

GetSQLValueString("RE-" . $_POST['refNumber'], "text")

2) If you took the advice and removed that field, use

$lastID = mysql_insert_id();

to find the ID of the last inserted row, then update it with something like

UPDATE listings SET refNumber = concat("RE-", "$lastID") WHERE id = $lastID

But again, please reconsider my points about not storing the refNumber in the first place - it is unnecessary.
do you need to left pad 0's if the number is 1?  i.e (RE-001 vs RE-1)

if len(id) = 1 then {
  $pad = "00"

if len(id) = 2 then {
   $pad = "0"

if len(id) = 3 then {
   $pad = ""

$refnum =  "RE-".$pad.$_POST['id']
oops, PHP - change len to strlen

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

dino_angelidesAuthor Commented:
well, if probably it will reach more than 999 listings in the database it would best be without the 00
so RE-1 and so forth is better i think...
by the way where do I add your code in my code? the insert statement I mean
agreed - if refnum is identical to the id column you're just adding unnecessary fields.  Just appen ""REF-"  to the id and you will ensure that there is no mixup.

If your Reference numbers need to be continuous with no missing numbers though, deleting a row at some point will leave a non-contiguous break in the auto-increment number.

dino_angelidesAuthor Commented:
Thx guys
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.