Create a new database item, then open it for editing: LAST_INSERT_ID().
Undo is better than “are you sure”. Mark items deleted, then empty trash when sure.
Read the basics first: MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu.
Preparation
We’ll use the same database we created in MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu. If you did that tutorial, you already have the databases. Otherwise
mysql> CREATE DATABASE student CHARACTER SET utf8;
mysql> GRANT ALL ON student.* TO student@localhost IDENTIFIED BY 'dk387KaaJEE-.d//NA';
mysql> EXIT
$ mysql -u student -p
mysql> CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(1024));
mysql> INSERT INTO students(name) VALUES ("Tero");
mysql> INSERT INTO students(name) VALUES ("Matti");
mysql> INSERT INTO students(name) VALUES ("Maija");
Insert Record, Get LAST_INSERT_ID()
In a web application, you often create a new record to database, then open it to editing view. For this, you need to know the id (primary key) of the record you just inserted.
The LAST_INSERT_ID() really returns your last id, even if there are other updates to the database at the same time. The last id information is maintained per-connection basis.
mysql> INSERT INTO students(name) VALUES ("Foo");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.01 sec)
Soft Delete, Empty Trash
Undo is better than “are you sure”.
Add a new boolean field deleted.
mysql> ALTER TABLE students ADD deleted BOOLEAN DEFAULT FALSE; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> DESCRIBE students; +---------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(1024) | YES | | NULL | | | deleted | tinyint(1) | YES | | 0 | | +---------+---------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
Soft delete a record.
mysql> UPDATE students SET deleted=TRUE WHERE name="Tero Karvinen"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM students; +----+---------------+---------+ | id | name | deleted | +----+---------------+---------+ | 1 | Tero Karvinen | 1 | | 3 | Maija | 0 | | 4 | Foo | 0 | +----+---------------+---------+ 3 rows in set (0.00 sec)
Show only non-deleted records.
mysql> SELECT * FROM students WHERE deleted=FALSE; +----+-------+---------+ | id | name | deleted | +----+-------+---------+ | 3 | Maija | 0 | | 4 | Foo | 0 | +----+-------+---------+ 2 rows in set (0.00 sec)
Empty trash – really, permanently delete marked records.
mysql> DELETE FROM students WHERE deleted=TRUE; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM students; +----+-------+---------+ | id | name | deleted | +----+-------+---------+ | 3 | Maija | 0 | | 4 | Foo | 0 | +----+-------+---------+ 2 rows in set (0.00 sec)
Now you have learned two common tricks you can use in your web apps: getting last insert id and implementing trash.