FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE forms (
  id INT AUTO_INCREMENT KEY,
  title VARCHAR(200)
);

CREATE TABLE fields (
  id INT AUTO_INCREMENT KEY,
  label VARCHAR(200)
);

CREATE TABLE form_fields (
  id INT AUTO_INCREMENT KEY,
  form_id INT,
  field_id INT
);

CREATE TABLE submissions (
  id INT AUTO_INCREMENT KEY,
  form_id INT
);

CREATE TABLE submission_fields (
  id INT AUTO_INCREMENT KEY,
  submission_id INT,
  field_id INT,
  field_value VARCHAR(255)
);

INSERT INTO forms(title) VALUES ('Form 1');

INSERT INTO fields(label) VALUES ('First Name');
INSERT INTO fields(label) VALUES ('Last Name');
INSERT INTO fields(label) VALUES ('Email Address');
INSERT INTO fields(label) VALUES ('Address');
INSERT INTO fields(label) VALUES ('City');
INSERT INTO fields(label) VALUES ('State');
INSERT INTO fields(label) VALUES ('Zip');
INSERT INTO fields(label) VALUES ('Phone');

INSERT INTO form_fields(form_id, field_id) VALUES (1, 1);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 2);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 3);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 8);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 4);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 5);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 6);
INSERT INTO form_fields(form_id, field_id) VALUES (1, 7);

INSERT INTO submissions(form_id) VALUES (1);

INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 1, 'Joe');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 2, 'Smith');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 3, 'joe@smith.com');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 4, '123 Main St');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 5, 'Somewhere');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 6, 'CA');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 7, '90210');
INSERT INTO submission_fields(submission_id, field_id, field_value) VALUES (1, 8, '555-555-5555');

-- QUERY database
SELECT
    submissions.id AS submission_id,
    submissions.form_id AS form_id,
    forms.title AS form_title,
    GROUP_CONCAT(COALESCE(form_fields.field_id, '') ORDER BY form_fields.id ASC SEPARATOR '|') AS form_fields_field_ids,
    GROUP_CONCAT(COALESCE(fields.label, '') ORDER BY form_fields.id ASC SEPARATOR '|') AS field_labels,
    GROUP_CONCAT(COALESCE(submission_fields.field_id, '') ORDER BY form_fields.id ASC SEPARATOR '|') AS field_ids,
    GROUP_CONCAT(COALESCE(submission_fields.field_value, '') ORDER BY form_fields.id ASC SEPARATOR '|') AS field_values
FROM
    submissions
LEFT OUTER JOIN
    forms
ON
    forms.id = submissions.form_id
LEFT OUTER JOIN
    form_fields
ON
    form_fields.form_id = submissions.form_id
LEFT OUTER JOIN
    fields
ON
    fields.id = form_fields.field_id
LEFT OUTER JOIN
    submission_fields
ON
    submission_fields.submission_id = submissions.id AND fields.id = submission_fields.field_id
WHERE
    submissions.id = 1
GROUP BY
    submissions.id;

submission_id	form_id	form_title	form_fields_field_ids	field_labels	field_ids	field_values
1	1	Form 1	1\|2\|3\|8\|4\|5\|6\|7	First Name\|Last Name\|Email Address\|Phone\|Address\|City\|State\|Zip	1\|2\|3\|8\|4\|5\|6\|7	Joe\|Smith\|[email protected]\|555-555-5555\|123 Main St\|Somewhere\|CA\|90210