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)
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?