MySQL: Capture every 5 values from column a in Table A to create one row in Table B

MySQLtrigger

I have submissions of a form from a web page records in a database.
the table has the following schema

submit_time form_name field_name field_value field_order file

the form has 5 fields, each form submission results in 5 rows in the previous table, one row per field.
the following is an example of 5 rows created after a submission.

    submit_time       form_name     field_name  field_value field_order file
    1427715050.2890 Contact form 1  your-name       Jung            0   NULL
    1427715050.2890 Contact form 1  your-email      Jung@Jung.Jung  1   NULL
    1427715050.2890 Contact form 1  your-subject    Jung maybe :)   2   NULL
    1427715050.2890 Contact form 1  your-message    Jung :D         3   NULL
    1427715050.2890 Contact form 1  Submitted Login Mohammad    9999    NULL

I want the data to be written in another table in another database the new table has the following schema:

Name email subject message Login

What I tried
I thought about writing a trigger like the following:

CREATE
    TRIGGER copy_data
    AFTER INSERT
    ON TABLE_A FOR EACH ROW
    BEGIN
    # check if submission entry exists in table B

    # NO :
        # INSERT the data in the corresponding column 

    # YES:
        # UPDATE the data in the corresponding column  

    END

but I can't insert NULL values in the second table.

what can I do in such situation ?

Note: I can't guarantee the order of the rows in Table A ( which field will be inserted first is something I can't control)

Best Answer

The TRIGGER can be simplified down to

INSERT ... ON DUPLICATE KEY UPDATE ...

It is not proper to have "redundant" data in a database. Why can't you just leave it in the original table? When you need it, do a JOIN.

Related Solutions

MySQL: A better way to write this query

Note: 1 + 1 + NULL = NULL

For this reason I'd change one part...

SUM(CASE
      WHEN e.oiPost  = 1 THEN 1
      WHEN e.oiPhone = 1 THEN 1
      WHEN e.oiEmail = 1 THEN 1
                         ELSE 0
    END)                                  AS opt_ins

Why all the records in the submission table are being processed, I have no idea. How many rows do you actually have that are submitted > '2012-04-01'?

Mysql – Keeping 2 tables in sync

If you have this scenario

table_a has an auto_increment field
table_b and table_c have table structure identical to table_a
You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Load_New_Data` $$ 
CREATE PROCEDURE `Load_New_Data` () 
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$ 
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev 
    ON SCHEDULE 
      EVERY 5 MINUTE 
      STARTS (NOW() + INTERVAL 5 MINUTE) 
    DO 
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1

Best Answer

Related Solutions

MySQL: A better way to write this query

Mysql – Keeping 2 tables in sync

Related Question