naming properly the primary keys, how to concatenate

[cary1234]

I'm having a problem on how do I put primary keys whenever a user register another person.

My pattern is this (batch number) + (incrementing value). For example a person named Cary register to my website and he inputted his batch number as  batch 53, so his primary will become 53001. Then if another person named Josh with batch number 53 again register to my website his primary should be 53002. Then another person name Brock register but his batch is 36 so his primary should be 36001.

So my solution is to get the batch number that the user inputted while they are registering then combine it with the incrementing value. My problem is how do I get the incrementing value and let the php know the next incrementing value? How do php know that the next number will be 003 or 004 or 005.

[QuickOldCar]

If you are using mysql you can be inserting using AUTO_INCREMENT

 

If and after it inserted you can get the id by using mysqli_insert_id()

 

[cary1234]

I tried to use AUTO_INCREMENT but it didn't worked out
 

here's my code

$final_u_ID = $batch .  mysqli_insert_id($db_Connection)

Whenever I register another user nothing happened maybe because of the same primary key.

Do you have any idea on how do I concatenate and get the last 3 digits of my u_ID in database?

 

Example:

52001 = I will concatenate it so it will become 52 / 001 then I will get the last 3 digits which is 001 then add +1 to that?

 

Other Examples:

35001 then if another user register with the same batch number it will become 35002

Take note that the user has freedom on what batch they want.

Thanks QuickOldCar

[Barand]

Firstly, don't store multiple fields in a single field, separate the batch and the id into separate columns.

 

Use a MyISAM table and you can make the second part of a compound key auto increment. Here's an example

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$sql = "DROP TABLE IF EXISTS `person`";
$db->query($sql);

$sql = "CREATE TABLE `person` (
          `batch` int(11) NOT NULL,
          `id` int(3) ZEROFILL NOT NULL AUTO_INCREMENT,
          `name` varchar(45) DEFAULT NULL,
          PRIMARY KEY (`batch`,`id`)
        ) ENGINE=MyISAM";
$db->query($sql) or die ($db->error);

$sql = "INSERT INTO person (batch, name) VALUES
        (53, 'Cary'),
        (53, 'Josh'),
        (36, 'Brock'),
        (36, 'Barand')
        ";
$db->query($sql);

mysql> SELECT * FROM person;
+-------+-----+--------+
| batch | id  | name   |
+-------+-----+--------+
|    53 | 001 | Cary   |
|    53 | 002 | Josh   |
|    36 | 001 | Brock  |
|    36 | 002 | Barand |
+-------+-----+--------+

[cary1234]

Thanks Barand, yeah I already thought of that to make my life easier. But my client ask me before if it is possible to create some pattern in the id's. They wanted me to create id according to the batch+incrementing number. So that they can easily identify it, what batch is this person.  Because in other company whenever a user creates a new account the id naming is year of birthdate + incrementing numbe (example: 199201, 1992 is birthdate and 01 is the incrementing number).

What do you think? Is it possible? I'm thinking whether should I change the naming of id's...

[kicken]

What do you think? Is it possible? I'm thinking whether should I change the naming of id's...

He just showed you it is possible. Separate the fields, and have the second be an auto-increment field. Whenever you display the fields on your reports or whatever then you can combine them into a single number. Just because you store them separately doesn't mean you have to display/use them separately.

 

If you'll need the ability for a user to type in a number to search by it, you can either combine them for the search or split the input into separate fields. Splitting the input would be ideal so long as you have a consistent format that you can split easily (ie last 3 = auto-increment, remaining = batch)

[Barand]

for example

mysql> SELECT CONCAT(batch, id) as id
    -> , name
    -> FROM person
    -> ORDER BY batch;
+-------+--------+
| id    | name   |
+-------+--------+
| 36001 | Brock  |
| 36002 | Barand |
| 53001 | Cary   |
| 53002 | Josh   |
+-------+--------+

and your client doesn't have to care what's going on behind the scenes.

[cary1234]

Thanks I got the idea. Yeah you are right. Thank you very much