Prepend letters to an autoincrementing field in mysql

[mrjonnytou]

I have an id field in my sql database that auto increments for every record that is added via a form. I would like to prepend three letters to this field and was wondering is there an easy way to do this in php?

 

regards,

[john-formby]

Is this a random 3 letters or a set value?

[mrjonnytou]

a set value e.g. 'abc'(auto incrtmenting number)

 

cheers

[GingerRobot]

Well, select the value from your database, and concatenate with the string:

 

$id = $row['id'];//for example, if this is your field
$str = 'abc';
echo $str.$id;

 

[mrjonnytou]

Hi, thanks for the response.

 

I think I should have been more specific. I would like to do this when creating an entry and save it to the database, rather than having to extract the entry first and attach it.

 

Regards,

[john-formby]

I am guessing that you want to store this value in the database?  The way I would do it (there may be better ways) is to have 2 fields, the first ID field is used to generate the autonumber (type = integer).  Then the second field is used to create the new ID (type = varchar).  Have a look at the following example:

 

testpage.php

<?php

$dbHost = "localhost"; 
$dbUser = "blah"; 
$dbPass = "blah"; 
$dbname = "blah"; 

$db = mysql_connect($dbHost,$dbUser,$dbPass); 
mysql_select_db($dbname,$db);                

if(isset($_POST['submit'])) {
$urname = $_POST['urname'];
$sql = mysql_query("INSERT INTO tbltest (IDnum, IDmain, urname) VALUES ('', '0', '$urname')");
$lastid = mysql_insert_id();
$IDmain = 'abc_'.$lastid;
$sql2 = "UPDATE tbltest SET IDmain='$IDmain' WHERE IDnum = '$lastid";
$result = mysql_query($sql2);
}

echo '<form name="test" action="testpage.php" method="post">
<input type="text" name="urname" />
<input type="submit" name="submit" value="submit" />
</form>';

?>

 

Hope this helps

[anon_login_001]

mrjonnytou  - To be clear, if the field is an actual "Auto Increment" field, you cannot prepend/append/modify it as the record is added, because the contents of that field are handled by the database ... it has to check what the last value was, and insert the next available number.

 

However, a solution like john-formby suggested will work, though you're adding a record, checking the record, and updating the record for every time you want to enter data.

 

You might want to evaluate exactly why you need to prepend data, and see if there's a workaround from there, because there is definitely a more effecient way to solve your problem, I'm sure.

 

Perhaps you could explain your expected end result, or what the exact problem is you're trying to solve, and we could help you a bit better.

 

 

[gigas10]

make another column, and take the auto increment field and the letters u need, and concat them into that column

[discomatt]

Or simply use an auto-increment field, and when you echo/display/use the id, append your 'abc' to it