MySQL – Reset Table Data After Each Session in PHP

MySQLPHP

I am creating a PHP application demo. The users who login can insert, modify and delete data but when they logout the data must be reset to the default data I set before.

The data must reset depending on the session so that multiple users can access the default data at the same time.

Is this possible using MySQL?

UPDATE 1:
I have created a stored procedure to create a new db and dump the default data, but I am getting an error while using the USE db_name in a procedure.

NOTE: I am using phpMyAdmin.

Error: #1314 USE is not allowed in stored procudures

Here is the stored procedure i have created with sample data. 

BEGIN

CREATE DATABASE IF NOT EXISTS `dp_demo2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `dp_demo2`;

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `cid` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `company` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `skype_id` varchar(40) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `country` varchar(50) NOT NULL,
  `zip_code` int(10) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `first_name`, `last_name`, `company`, `email`, `phone`, `skype_id`, `address`, `city`, `country`, `zip_code`, `created`) VALUES
(1, 'Julie', 'Penning', 'Apple', 'juli@example.com', '0402011', '@julie', '20-120, Street', 'Calfornia', 'USA', 40192, '2016-07-16 04:05:22'),
(2, 'Karl', 'Max', 'Ham. co.', 'karl@example.com', '9828101', '@max.karl', '20-820, Street', 'New York', 'USA', 82918, '2016-07-16 04:06:57'),
(3, 'Jhon', 'Doe', 'Microsoft', 'jhon@example.com', '892123', '@jhone.doe', '9-21-23, ham street', 'Troy', 'USA', 83082, '2016-07-16 04:08:48');

END

UPDATE 2:

I am able to create new db and use them, but i got another problem, how can I get dynamic db_name into the stored procedure.

Parameters for the stored procedure is not working for names, but only for the data.

Best Answer

Setup:

  1. Take a mysqldump of the 'default' data.
  2. Put the output into a Stored Procedure.
  3. Modify the stored procedure to also CREATE DATABASE and USE that database. The database name would be dynamic -- probably the user id, with something prepended to avoid naming conflicts. Also surround with backtics.

User comes in:

  1. perform that stored procedure.

User leaves. This could be tricky -- what if he stays logged in? what if some server dies without getting to this code? Etc. There may need to be something that runs around periodically to clean up orphaned databases.

  1. Drop his database.

Note: Your question implied that everyone is using the same database. I switched to having everyone use a different database.

Related Question