Seeking Advice - Data Structure

[Adamhumbug]

HI All,

I am making an application that allows a user to fill in forms.

Forms can be attached to an umbrella element called a pass.

A pass has dates and zones.

The forms that can be attached to it can contain anything.

Currently i am saving all of the data to the person record in json format.  All of the fields get a field code that is unique accross the system and identify the data in the json.

ie

Field Name	Field Code
First Name	fname
Last Name	lname

However,

I am going to have a listing for submissions that will need to seperate all of the data out at least in terms of viewing it.

What i mean by this is that i will have a section where the user can click to view all of the form submissions, filter by type and action them - maybe reject them or accept them.

My question is - what do you think would be the best way to store the submitted data - i believe that it should be saved into a normalised table with a json column containing all of the submitted data.

If this is the route that i go down, it leads to another question.

As the pass can contain many forms, when submitting the data, how do it split it so that it submits as multiple forms.

Currently i am using ajax formData which is submitting the lot in one chunk - i have chosen this route as the forms are dynamic so i wont know what the fields are.  This is currently working pretty well interms of handling the data and attaching it to the person in json format.

I have attached an image of the form to give a bit of context.

You can see that the pass is called P1 and there are 2 forms: test and One.

 

I know that this is likely very subjective but i am looking for a steer in the right direction as i feel messing this up will cause major headaches down the line.

As always, if you dont know what i am talking about, feel free to ask for more context and i can provide code to help.

 

Thanks All

 

Adam
 

[Adamhumbug]

Also apologies, this has been posted in the wrong section.

Edited April 29 by requinix
(used to be in Applications)

[Danishhafeez]

For storing submitted data, consider a normalized table structure with a JSON column for flexibility in handling dynamic forms. Ensure the JSON data is well-structured to facilitate easy querying and analysis.

To handle multiple forms attached to a single pass, include metadata (like form ID or name) with each submission. Use this metadata to separate and store each form's data independently in your database.

For dynamic forms, utilize AJAX FormData for submission to accommodate varying field structures. Implement robust server-side validation mechanisms to ensure data integrity and security.

Best Regard

Danish Hafeez | QA Assistant

ICTInnovations

 

[Adamhumbug]

function submitPass($pdo)
{


    $personId = $_SESSION['portalUId'] ?? null;
    if ($personId === null) {
        return json_encode(['status' => 'error', 'message' => 'Invalid or missing person ID.']);
    }

    // Start transaction
    $pdo->beginTransaction();

    try {
        // Retrieve existing person data
        $sqlGetPerson = "SELECT person_json FROM person WHERE id = :person_id";
        $stmtGetPerson = $pdo->prepare($sqlGetPerson);
        $stmtGetPerson->execute([':person_id' => $personId]);
        $personData = $stmtGetPerson->fetchColumn();
        $personData = $personData ? json_decode($personData, true) : [];

        // Process each form data entry
        foreach ($_POST as $key => $value) {
            if (strpos($key, 'passId-') === 0) {
                $passTypeId = explode('-', $key)[1];
                $sqlPass = "INSERT INTO pass_submission (pass_type_id, person_id, pass_submission_json) 
                            VALUES (:pass_type_id, :person_id, :pass_json)
                            ON DUPLICATE KEY UPDATE pass_submission_json = :update_pass_json";
                $stmtPass = $pdo->prepare($sqlPass);
                $stmtPass->execute([
                    ':pass_type_id' => $passTypeId,
                    ':person_id' => $personId,
                    ':pass_json' => $value,
                    ':update_pass_json' => $value
                ]);
            } elseif (strpos($key, 'passForm-') === 0) {
                $formId = explode('-', $key)[1];
                $formData = json_decode($value, true)[0];
                $personData['person_data'] = array_merge($personData['person_data'] ?? [], $formData);

                $sqlForm = "INSERT INTO form_submission (person_id, form_id, form_submission_json, status) 
                            VALUES (:person_id, :form_id, :form_json, 'pending')
                            ON DUPLICATE KEY UPDATE form_submission_json = :update_form_json";
                $stmtForm = $pdo->prepare($sqlForm);
                $stmtForm->execute([
                    ':person_id' => $personId,
                    ':form_id' => $formId,
                    ':form_json' => $value,
                    ':update_form_json' => $value
                ]);
            }
        }

        // Update the person data in the person table
        $updatedPersonJson = json_encode($personData);
        $sqlUpdatePerson = "UPDATE person SET person_json = :person_json WHERE id = :person_id";
        $stmtUpdatePerson = $pdo->prepare($sqlUpdatePerson);
        $stmtUpdatePerson->execute([':person_json' => $updatedPersonJson, ':person_id' => $personId]);

        $pdo->commit();  // Commit all changes
        return json_encode(['status' => 'success', 'message' => 'All data processed successfully.']);
    } catch (Exception $e) {
        $pdo->rollBack();  // Roll back on error
        return json_encode(['status' => 'error', 'message' => 'Database error: ' . $e->getMessage()]);
    }
}

What i went with in the end was exactly this.  I passed the formID for each form through and looped them.