Update ir insert mysql tables

[jonysrb]

Hello everyone, I need help with creating php function, procedural php, or MySQLi for update or insert data.

I have 3 tables. Table PRODUCTS contain all the information that i want to transfer into other two tables. 

Table PRODUCTS: id, name, serial, description 
Table List: id, name, serial 
Table View: id, name, description 

I need script that read table PRODUCTS and data from this table inesert or update values in the other two tables. 
1. After call function 
2. Read Table PRODUCTS 
3. Read table LIST, if in "list" table have record with same serial as in table PRODUCTS update name with that serial from table products and if don't have insert id, name and serial with data from products 
4. Read table View, if have record with same name as in table PRODUCTS update description with data from table PRODUCTS or insert id, name, description 

In table products I have over 4000 records.

Thank you

Edited November 26, 2016 by jonysrb

[Barand]

When you have all the product info in table "product", why would you want to duplicate that info into two other tables?

[jonysrb]

I have windows B2C app that use table PRODUCTS but our new web app use other tables, and I want to sync old tables with new tables. I can't change windows app to use new tables.  only solution is to make function that when I make changes on old table update new tables.

 

For example:

 

OLD APP use one table: CLIENTS ( id, uname, pass, user_group,email,....)

 

NEW APP use two tables:

USERS (id, username, pwd, email,...)

USER_GROUP (id, group_name, status,...)

[benanamen]

You can use a Mysql View to map the columns.

[Barand]

So it looks like the exercise is to normalize the user_group, creating a separate table and then storing the id of the group in the user table instead of the name
 
Original data

mysql> SELECT * FROM clients;
+----+----------+-----------------------+------------+----------------+
| id | uname    | pass                  | user_group | email          |
+----+----------+-----------------------+------------+----------------+
|  1 | jonysrb  | d5er56rPg12ebN8eh65lk | Admin      | jony@gmail.com |
|  2 | peter99  | kl8HGbL93sbx7         | Users      | pete@gmail.com |
|  3 | paul1999 | asdFG5VbF98z          | Moderator  | paul@gmail.com |
|  4 | maryram  | kQ87FF75wT2v          | Users      | mary@gmail.com |
+----+----------+-----------------------+------------+----------------+

 
Step 1: Create the new tables

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(16) NOT NULL,
  `pwd` varchar(32) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_users_group_id` (`group_id`)
) ;

CREATE TABLE `user_group` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_group` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`group_id`)
) ;

Step 2a

Check that spellings of the groups are consistent. (I once did an exercise like this on a large invoices table and some of the supplier names had half a dozen different spelling variations)

SELECT DISTINCT user_group
FROM clients
ORDER BY user_group;

Step 2b: populate the new groups table

INSERT INTO user_group (user_group)
SELECT DISTINCT user_group
FROM clients
ORDER BY user_group;

Step 3: populate the new users table

INSERT INTO users (id,username,pwd,email,group_id)
SELECT c.id
 , c.uname
 , c.pass
 , c.email
 , g.group_id
FROM clients c
    INNER JOIN
    user_group g USING (user_group);    -- join on group name to get the new id

New data

mysql> SELECT * FROM user_group;
+----------+------------+
| group_id | user_group |
+----------+------------+
|        1 | Admin      |
|        2 | Moderator  |
|        3 | Users      |
+----------+------------+

mysql> select * from users;
+----+----------+-----------------------+----------------+----------+
| id | username | pwd                   | email          | group_id |
+----+----------+-----------------------+----------------+----------+
|  1 | jonysrb  | d5er56rPg12ebN8eh65lk | jony@gmail.com |        1 |
|  2 | peter99  | kl8HGbL93sbx7         | pete@gmail.com |        3 |
|  3 | paul1999 | asdFG5VbF98z          | paul@gmail.com |        2 |
|  4 | maryram  | kQ87FF75wT2v          | mary@gmail.com |        3 |
+----+----------+-----------------------+----------------+----------+