MySQL Auto-Increment with several different user-input words

I'm currently working on a species cataloging code written on HTML + PHP using a MySQL InnoDB database.

The code allows the user to enter data of animals and store that on the database for further search (ID(used for indexing),Specie, order, family, name - these are the fields on the current database).

One of the requeriments is that every animal have a unique code, which the first 2 words are defined by the user.

EG: TES/DES.01<br> Format: str/str.int

This is where i got stuck, the first two words must be designed by the user and the integer should be an auto-increment field based on previous results, since there will be multiple animals with the same prefix and the difference on the unique code will be the integer. EG:

TES/DES.01<br> TES/DES.01<br> RUN/MAL.01(There will be multiple different prefixes, and they will grow as long the users keep adding more)<br>

RUN/MAL.02 ... and so goes on

My first thought was to clone the table schema for every new prefix and then concatenate the prefix with the ID field for indexing, but this would be extremely inefficient on my current hardware structure(Probably inefficient even in a good hardware?).

How i can achieve this?

Thanks for the help.

--------------Solutions-------------

You want a 2-part PRIMARY KEY where the second is an AUTO_INCREMENT that resets for each change in the first part? This is available directly in MyISAM, but not in InnoDB.

It can be simulated.

CREATE TABLE ... (
part1 ...,
part2 TINYINT ZEROFILL UNSIGNED NOT NULL,
...
PRIMARY KEY(part1, part2),
) ENGINE=InnoDB;

-- Insert a new row:
BEGIN;
SELECT @num := MAX(part2) FROM .. WHERE part1 = '...' FOR UPDATE;
INSERT INTO ... ('RUN/MAL', IFNULL(@num+1, 1), ...);
COMMIT;

-- Get the string you really want:
SELECT ... CONCAT(part1, '.', part2) ...

Category:php Time:2018-12-02 Views:1

Related post

  • Is mysql auto increment safe to use as userID? 2011-11-10

    I am working on website that allows people to create profiles online. I was wondering if it is the right choice to use MySQL AUTO_INCREMENTed IDs as my user ids. Also bearing in mind that I might have to duplicate the database across multiple servers

  • MySQL - Auto Increment after delete 2010-02-06

    I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'. I

  • Invoking an external process with a different user in java 2009-09-11

    We have a java application running as a windows service. A particular functionality needs to execute a binary but with a different user then which started the application. Is there any way by which we can invoke an exe with 'Run as a different user'

  • Mysql - db-select with several joins and tablestructure 2012-02-19

    I am using the following mysql select-query with several joins. I am wondering if this is how a somewhat good select-statement should look like: SELECT * FROM table_news AS a INNER JOIN table_cat AS b ON a.cat_id = b.id INNER JOIN table_countries AS

  • How to open Mozila firefox with a different user using Bat file or any script 2012-12-26

    Hi...! There. I want to create a bat file by which I can open Firefox with a different user name. I have typed below code and it worked well in Command Prompt but its not working as bat file. runas /user:Domain\UserName "C:\Program Files\Mozila Firef

  • AUTO INCREMENT value for multiple user mysql php 2012-02-24

    hi guys i am using the code below to get mysql auto increament value its working how ever when more than 2 users try to insert row two different table at the same time they are all get the same number and which causes error. is there any way to handl

  • MySQL Auto-incremented ID number sent to User as registration number 2011-06-27

    The question is in bold. Everything else is for context. User submits form data, which is inserted into a MySQL db. The MySQL db generates an auto-incremented "id". "id" is then (auto) pulled out of the db and is emailed to User via a "Success" email

  • Using Auto Increment with MySQL and need to retrieve that number 2011-10-01

    I have a database in which I need some IDs to be autoincremented, but I also need that ID that gets auto incremented to be the Foreign Key for another table. Is there a way to recover that number within the same transaction to insert values into the

  • MySQL Auto-Increment Issue with Numbering 2012-03-17

    I have a table in my database with around 5m records, and I'm noticing the IDs of the each records are not incrementing by one any every insert. For example, I have the following schema setup: my_table activity_id INT(11) PRIMARY (AUTO_INCREMENT), cr

  • MySQL how to order based on user inputs with joined tables? 2012-01-12

    This is an "example version" of my current SQL query: SELECT DISTINCT files.* FROM vw_files files LEFT JOIN tbl_file_ext fext ON files.id = fext.fileID INNER JOIN tbl_extensions ext ON fext.extensionID = ext.id WHERE ext.extension = 'exe' OR ext.exte

  • Implement auto-increment with Word macro 2009-04-08

    I'm writing a Word/VBA macro for a document template. Every time a user saves/creates a new document from the template, the document needs an ID embedded in the text. How can I (as simple as possible) implement auto-increment for this ID? The ID is n

  • MySQL Auto Increment Values After DELETE without WHERE 2009-09-13

    I noticed after running, DELETE FROM tablename My ID (auto increment) values became weird 7, 8, 9, 0, 1, 12, 3, 4, 15 In this order when I do a, SELECT * FROM tablename I know that the certification guide says that IDs may or may not be reset when DE

  • MySQL: Auto incrementing combined keys 2010-03-10

    Ok, I have a table that will look something like this: Post ˪ Id ˪ Version ˪ Title ˪ Content The idea is that the Id and Version together will be the primary key since you can have a single post multiple times but of different versions. My question i

  • Auto increment with a Unit Of Work 2010-04-21

    Context I'm building a persistence layer to abstract different types of databases that I'll be needing. On the relational part I have mySQL, Oracle and PostgreSQL. Let's take the following simplified MySQL tables: CREATE TABLE Contact ( ID varchar(15

  • Make MySQL auto-increment id (re) start from 1 2010-12-10

    "BIG" UPDATE: Ok I was getting the whole auto-increment point wrong. I though this would be an easier way to target the first, second, third and so row, but it is just the wrong approach. You should instead care about that the auto_increments are uni

  • How does MySQL Auto Increment work? 2010-12-30

    I was just creating a new table using MySQL Query Browser, and noticed there's a tick under Auto Increment Column. How does that work? When adding to the database programatically, do I just add a number, and then the database automatically increments

  • MySQL Auto Increment Custom Values 2011-03-08

    I am trying to make a column in a mysql database that auto increments by one but goes from 0-Z and then rolls. For example 000, 001, 002, ..., 009, 00A, 00B, ..., 00Z, 010, ..., 0ZZ, ..., 100. I would like to have the database create the column throu

  • Using generics to cast different user input types 2009-04-01

    I have a windows application that allows input from pen input on tablet machine. When required a form (A) will open another form (B) to allow user input. In form (B) I have the following function to return the decoded value that the user inputs with

  • Mysql auto increment primary key id's 2009-10-23

    I have some mysql tables that have auto incrementing id's that are primary keys, but I notice that I never actually use them... I used to think that every table must have a primary key so I guess that is why I created them before. Should I remove the

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 4.877 (s). 15 q(s)