Mysql – create table from another table

database-designMySQL

I have master categories table like this

category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

when the user selects category i need to create new table with all categories and subcategories & sub sub categories (infinity subcategories). for example if user selects animals & pets supplies the new category table should be like this

  category_id    category_name            parent_name           parent_id
     1         animals & pet supplies       null                   0
     2         live animals            animals & pet supplies      1
     3         pet supplies            animals & pet supplies      1
     4         bird supplies           pet supplies                3

if the user selects clothing category then new category table should be like this

 category_id    category_name            parent_name           parent_id
     5         clothing                     null                   0
     6         mens clothing           clothing                    5
     7         womens clothing         clothing                    5
     8         shirts                  mens clothing               6
     9         formal shirts           shirts                      8

anybody please help me in achieving this. thanks in advance. suggestions to change in table designing also welcome to achieve this

Best Answer

Why do you need to create new table on selection?

Can user select any sub category directly ? i.e. Instead of clothing, Men's Clothing

Regards, Maulin.

Related Solutions

MySQL – Create a function from another function

You could play some games with mysql.proc, the physical home of all Stored Procedures and Stored Functions.

First, here are the databases on my PC

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

Let's create a Stored Function in the database rolando called GetUnixTimeStamp:

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

Let's try to sculpt the CREATE FUNCTION SQL Statement based on the function itself

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

What does this generate ?

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK so far, so good. Let's change the SQL to place this function in the database david

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

OK Great. We can make the SQL. Can I execute the creation of the new function ???

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Does the function exist ???

mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Biggest questiion of all : Does the newly copied function run ???

mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

HOORAY !!! This is how you can copy a function from one database to another.

FINAL QUESTION : Is this all worth it ???

SUMMARY

Here is the original SQL to generate function creation

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `david`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

This will create Stored Procedures as well

Here is the copy operation, which must take place from the OS level calling the mysql client

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

Mysql – pass data from deleted table to another

If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id):

ALTER TABLE table_1
   CHANGE COLUMN id 
      id_table_1 int NOT NULL AUTO_INCREMENT,
-- ADD PRIMARY KEY 
--    (id_table_1),
   CHANGE COLUMN active 
      status TINYINT NOT NULL DEFAULT 1,
   CHANGE COLUMN sent
      sent TINYINT DEFAULT 0,
   DROP COLUMN description ;

Tested at SQL-Fiddle

Best Answer

Related Solutions

MySQL – Create a function from another function

SUMMARY

Mysql – pass data from deleted table to another

Related Question