To add an alphabet in my auto generated id

[genzedu777]

Hi,

 

I have a question here. Currently my $tutor_id is auto incremental in my SQL, and it is in running numbers.

 

However I will like to add a 'T' in front of the id.

 

Example:

 

Currently, our id number is

1

2

3

4

etc.

 

Would like to achieve

T1

T2

T3

T4

etc.

 

How do I do it? Below is my code as well. Please kindly advise. Thanks

 

 

/**INSERT into tutor_id table to create a new id for new tutors**/

$query = "INSERT INTO tutor_id (id) " .

"VALUES ('$id')";

 

$results = mysqli_query($dbc, $query)

or die(mysqli_error());

 

$tutor_id = mysqli_insert_id($dbc);

 

/**INSERT into tutor table**/

$query1 = "INSERT INTO tutor (tutor_id, email) " .

"VALUES ('$tutor_id', '$email')";

 

$results1 = mysqli_query($dbc, $query1)

or die(mysqli_error());

[Pikachu2000]

That isn't the purpose of an autoincremented index field. If you want a T before the number for display, prepend it when you display it.

[genzedu777]

Hi Pikachu2000,

 

Appreciate your reply, however may I know how I can prepend it? The reason for adding a T and making it auto incremental, it's because we are working on our existing database, which has already taken up all numeric code

 

To separate them from old and new records, we thought of adding a T to the new ID codes.

 

Please kindly advise how we can prepend the code? Thanks

[DavidAM]

An AUTO_INCREMENT column has to be numeric (at least in mySql). Since 'T' is a character (not numeric) you cannot prepend it to the ID number in the ID column of the database.

 

What is the datatype of your current ID column? An UNSIGNED BIG INT column can hold 18,446,744,073,709,551,615 numbers (that's a lot).  If your current column is NOT a BIG INT, you can ALTER the table to change the datatype and allow for new numbers to use.

[genzedu777]

Hi David,

 

My current ID column is using Varchar(50).

 

So is there a way to assign T at the front with running numbers?

 

May I know do you have any advice? Thanks

[Buddski]

So the field itself does not auto-increment? If you are incrementing the values via the code (which I am assuming you are) when you have created your "Auto-Increment" just add a 'T' to the front.

 
// code to fetch the last digit //
$last_digit = 65;
$id = 'T' . $last_digit;

 

This is a dodgy work around, in reality you should look at restructuring your database, why generate (im assuming) the auto-inc when mysql can do it for you.. ID fields in my opinion should be, as previously stated, a numeric field. Not a VARCHAR.

[genzedu777]

Hi Buddski,

 

Thanks. But I have tried, it doesn't work. Perhaps I should show you my code to have a better idea

 

I am currently taking the 'id' from tutor_id.sql table,

Therefore after which, I entered another code to pull out the 'id' number from tutor_id.sql and place it in $tutor, then I execute your advice command, but apparently it doesn't work...any idea?

 

$tutor = mysqli_insert_id($dbc);

$tutor_id = 'T' . $tutor;

 

 

/**INSERT into tutor_id table to create a new id for new tutors**/

$query = "INSERT INTO tutor_id (id) " .

"VALUES ('$id')";

 

$results = mysqli_query($dbc, $query)

or die(mysqli_error());

 

$tutor = mysqli_insert_id($dbc);

$tutor_id = 'T' . $tutor;

 

/**INSERT into tutor_login table**/

$query1 = "INSERT INTO tutor (tutor_id, email) " .

"VALUES ('$tutor_id', '$email')";

 

$results1 = mysqli_query($dbc, $query1)

or die(mysqli_error());

[Buddski]

Ok so..

What field type are you using for the 'id' field in the 'tutor_id' table and..

what field type are you using for the 'tutor_id' in the 'tutor' table?

[genzedu777]

If you can see in the attachment,

 

I am using $tutor = mysqli_insert_id($dbc); to pull out the 'id' from 'tutor_id', and place it into

tutor.sql table.

 

/**INSERT into tutor_id table to create a new id for new tutors**/
   $query = "INSERT INTO tutor_id (id) " .
   "VALUES ('$id')";
   
   $results = mysqli_query($dbc, $query)
   or die(mysqli_error());

   $tutor = mysqli_insert_id($dbc);
   $tutor_id = 'T' . $tutor;
   
/**INSERT into tutor_login table**/
   $query1 = "INSERT INTO tutor (tutor_id, email) " .
   "VALUES ('$tutor_id', '$email')";
   
   $results1 = mysqli_query($dbc, $query1)
   or die(mysqli_error());

 

Perhaps in the first place, how could I change the 'tutor_id' in tutor_id.sql table into having 'T' in the front? What are the codes which I need to write? I have changed 'tutor_id' into varchar.

 

 

[attachment deleted by admin]

[Buddski]

We need to know the field types.. You said earlier that your id field was a varchar which will accept non-numeric data..you did not say which table that was.. one or both?

 

If you can please tell us what the field types as I requested before are we can move forward to a solution

[genzedu777]

Okay. Perhaps let me clearly explain the entire process again.

 

We are working on our existing database, where the 'tutor_id' is in alphanumeric

 

Now, we will like to export our EXISTING database and import it into another hosting domain.

 

And I will like to start off the 'tutor_id' with running sequence like 'T1', 'T2', 'T3' etc.

 

Scenario

 

1) User will register in a registration form

2) Details of the USER will be capture and sent to various SQL tables

3) FIRST, it will capture the EMAIL and place it in tutor.sql //Please see tutor_login1 & 2.jpg for more clarification

4) As you can see, 'tutor_id' is also created at the same time when the EMAIL is inserted into 'tutor.sql'

5) Thereafter, my 2nd command will pull the created 'tutor_id' and place it into the 2nd table tutor_profile.sql.

6) As you can see in 'tutor_profile.sql', one of the columns is cater for 'tutor_id', this 'tutor_id' is supposed to be taken from the 1st table (tutor.sql)

7) My question: Is the highlighted code in

applicable to be used? If not, any other advice?


/**INSERT into tutor_login table**/
$query1 = "INSERT INTO tutor (email) " .
"VALUES ('$email')";

$results1 = mysqli_query($dbc, $query1)
or die(mysqli_error());

       

$tutor = mysqli_insert_id($dbc);
$tutor_id = 'T' . $tutor;

 

/**INSERT into tutor_profile table**/

$query2 = "INSERT INTO tutor_profile (tutor_id, name) VALUES ('$tutor_id', '$name')";

 

        $results2 = mysqli_query($dbc, $query2)

or die(mysqli_error());

 

 

 

[attachment deleted by admin]

[MMDE]

Why do you not just have an extra column for the "t"? =o the more it is normalized, the better and easier it is to use.

 

You could even just have it as a tinyint!

Example:

10=T

20=non-T's

 

This would in no way use more space than what you currently want, and as I said earlier, way easier to work with in about every single way that I can think of.

[genzedu777]

Hi MMDE,

 

Thanks for the idea, I have thought of it too. But I realised our existing record has integer values already.

 

For instance id '103242'. Hence If I were to use integer value, in no time, our new records will clash with the existing record.

 

Any other suggestions? It doesn't insert the 'T' value when I used the below command

 

$tutor = mysqli_insert_id($dbc);

$tutor_id = 'T' . $tutor;

[MMDE]

Planning is half the work, trust me. :\

Bad planning = many times as much work later on!

 

1. Create a new database that actually is normalized, and has the functionality that you want.

2. Take a "backup" of the current scripts etc, and edit it to work with the new database.

3. Write a script to transfer the current data in the current database to the new one. (remember to test it before actually doing it)

4. Take the servers offline, good idea is to do this in the night.

5. Do a backup of the current database.

6. Run the transfer script.

7. Update the offline files you got and that you edited to work with the new system.

8. Test that everything works as it should locally.

9. Put the server online again.

 

Might seem like a lot of hassle, but if you are smart and plan this well, you might be able to add other stuff that you've wanted to add too.

 

 

Else, the best way to do this the way you want to is to first run a SELECT query against the db:

SELEECT id WHERE id = T% ORDER BY id DESC

 

then enter this as the new id:

'T'.(substr(mysql_result($result,0),1)+1)

$result is the mysql query result.

 

EDIT: doh, i forgot +1, but I added it now! xD