0

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      [email protected]  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)

5
  • what happen if the same form (contact form 1) are filled from two distinct users at the same time (i.e. 1427715050.2890)? Maybe it is a rare event, but could it happen?
    – Giovanni
    Mar 31, 2015 at 13:05
  • A very long shot. Mar 31, 2015 at 13:08
  • :-) However I think it is better to have an unique id associated to a form submission, and to have all rows of the first table inserted into unique transaction.
    – Giovanni
    Mar 31, 2015 at 13:22
  • The insertion into table A is being done by a WordPress plug in, the form is online. Mar 31, 2015 at 13:23
  • So, I guess that plug in is handling that case somehow, maybe by just discarding the second user I have no idea. But you are absolutely right. Mar 31, 2015 at 13:25

1 Answer 1

1

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.

3
  • I agree, it is not proper. But the insertion is done through a WordPress plug in which I can't control it is behavior. Would this solution work? It won't insert null values? Apr 1, 2015 at 7:07
  • I'm sorry, I lose patience with 3rd party packages that get in the way. You have 2 packages -- WP and a plugin. Complain to them. Meanwhile, you are having to become an expert in MySQL just to figure out how to work around their limitations.
    – Rick James
    Apr 1, 2015 at 17:51
  • And since I am not expert enough I turned to the community here, I just need to select a column and make it a row in another table. How remains a mystery :-D Apr 1, 2015 at 17:53

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.