Hi I want to create a php-sql application for my case study where in I have these attributes:
Book ID, Book Name, Author, Editor, ISBN
the user will input the Book Name, Book ID, Author and Editor and automatically there will be an input for ISBN Number that is increments every time the user will input. Is that possible anyway? Well thanks for the help in advance.
It's unclear why you need this but you can achieve your goal with a BEFORE INSERT trigger and a separate table for sequencing
Tables
-- A sequencing table
CREATE TABLE book_seq
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
-- Set initial value
ALTER TABLE book_seq AUTO_INCREMENT = 9000;
CREATE TABLE books
(
book_id INT,
book_name VARCHAR(256),
author VARCHAR(256),
editor VARCHAR(256),
isbn VARCHAR(12)
);
A trigger
DELIMITER $$
CREATE TRIGGER tg_book_seq
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
INSERT INTO book_seq VALUES(NULL);
SET NEW.isbn = CONCAT('8000-', LAST_INSERT_ID());
END$$
DELIMITER ;
Now you can simply insert rows
INSERT INTO books (book_id, book_name, author, editor) VALUES
(1, 'Book1 Title', 'Author1', 'Editor1'),
(2, 'Book2 Title', 'Author2', 'Editor1');
Your pseudo ISBN number will be auto-generated
| BOOK_ID | BOOK_NAME | AUTHOR | EDITOR | ISBN | --------------------------------------------------------- | 1 | Book1 Title | Author1 | Editor1 | 8000-9000 | | 2 | Book2 Title | Author2 | Editor1 | 8000-9001 |
Here is SQLFiddle demo
Another, much simpler, approach is to use INSERT ... SELECT syntax and calculate next pseudo ISBN number at the time of insert with the query like this
INSERT INTO books (book_id, book_name, author, editor, isbn)
SELECT 1, 'Book1 Title', 'Author1', 'Editor1',
CONCAT('8000-', COALESCE(SUBSTRING_INDEX(MAX(isbn), '-', -1), '8999') + 1) new_value
FROM books
Here is SQLFiddle demo
Note: the major drawback of this approach is that it might fail under heavy load in concurrent environment when two or more concurrent users may have get the same MAX(isbn) value thus producing rows with the duplicate isbn number.
Like Trung-Hieu Le wrote in his comment:
The ISBN shouldn't be an automatic input but if you REALLY want that you should have a look at
Auto Increment
yes you can make ISBN field as auto-increment, starting with a definite number
if you are working on your won ISBN numbers for a specific project then yes you can set a field to auto increment starting for your desirable number,
but if it is not only your won ISBN numbers and you need real books ISBN number then you will need to have an ISBN number database that contain books ISBN numbers then whenever a user input a book a name and other details then the ISBN will be automatically inserted in your ISBN field based on a match query with the book information...
hope this help