0

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', '[email protected]', '0402011', '@julie', '20-120, Street', 'Calfornia', 'USA', 40192, '2016-07-16 04:05:22'),
(2, 'Karl', 'Max', 'Ham. co.', '[email protected]', '9828101', '@max.karl', '20-820, Street', 'New York', 'USA', 82918, '2016-07-16 04:06:57'),
(3, 'Jhon', 'Doe', 'Microsoft', '[email protected]', '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.

2
  • Can you provide an estimation of how much data we are talking about at the start and how much is written: 1MB, 1GB, 1TB, 1 PB? How many concurrent users will be accessing with separate "views"? How much disk and memory you have available? Do you have restrictions on how much time it must take to reset the data?
    – jynus
    Jul 16, 2016 at 15:52
  • I am having around 6 -8 tables with less than 10 rows per table, about 10 -20 concurrent users will access the db maybe less. I am having around 1GB of memory and there is no restriction on time taken to process.
    – Nagarjuna
    Jul 16, 2016 at 16:32

1 Answer 1

0

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.

7
  • I have created a stored procedure but i am unable to use the USE command in the stored procedure to select the DB which has been created.
    – Nagarjuna
    Jul 18, 2016 at 10:37
  • Then do the equivalent in PHP.
    – Rick James
    Jul 18, 2016 at 14:15
  • But I am unable to pass new db name as a parameter to the stored procedure.
    – Nagarjuna
    Jul 19, 2016 at 1:46
  • Pass it to a PHP function, then construct the statements you need to execute.
    – Rick James
    Jul 19, 2016 at 3:19
  • I got it working, but everything is done via PHP. I have got another problem, my hosting provider doesn't allow me to create new db from php, any other solution which doesn't involve in creating new db would be helpful.
    – Nagarjuna
    Jul 20, 2016 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.