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
e.g
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

dino_angelidesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thedwillCommented:
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']
0
thedwillCommented:
oops, PHP - change len to strlen

0
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
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

rjdownCommented:
"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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thedwillCommented:
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.



0
dino_angelidesAuthor Commented:
Thx guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.