A PHP Library of functions to enable one to many relationships between forms in Machform

sync.lib.php

<?php
    
    /*
        CUSTOM_CODE
        -----------
        Author: Ano Tisam
        Date: 09/03/2015
        Description: PHP Library of functions to enable 1:M database relational database features in Machform Form Builder.
    */

    /* SELECT All children of a Parent_Form */
    function Select_Links($form_id){
        return "SELECT
                    id,
                    parent_form,
                    parent_element,
                    child_form,
                    child_element
                FROM
                    link
                WHERE
                    parent_form = {$form_id}";
    }

    /* Delete Parent_Form Data from Children */
    function Empty_Child_Options($rsnew){
        return "DELETE FROM ap_element_options WHERE
                        form_id = {$rsnew['child_form']}
                        AND element_id = {$rsnew['child_element']};
                        ";
    }

    /* Insert Parent_Form Data into Children (Sync Data) */
    function Populate_Child_Options($rsnew){
        return "INSERT INTO ap_element_options (
                    ap_element_options.form_id,
                    ap_element_options.element_id,
                    ap_element_options.option_id,
                    ap_element_options.position,
                    ap_element_options.`option`,
                    ap_element_options.option_is_default,
                    ap_element_options.live)
                    SELECT 
                        {$rsnew['child_form']} as `form_id`, 
                        {$rsnew['child_element']} as `element_id`, 
                        `id` as `option_id`, 
                        `id` as `position`, 
                        `element_{$rsnew['parent_element']}` as `option`, 
                        0 as `option_is_default`, 
                        1 as `live`
                    FROM 
                        `ap_form_{$rsnew['parent_form']}`
                    ORDER BY 
                        `element_{$rsnew['parent_element']}`;
                    ";	
    }

    /* Delete Child_Form Data */
    function Exernal_Empty_Child_Data($rsnew){
        return "TRUNCATE ap_form_{$rsnew['child_form']};
                ";
    }

    /* Insert External_Table Data into Children (Sync Data) */
    function External_Populate_Child_Options($rsnew){
        return "INSERT INTO `ap_form_{$rsnew['child_form']}` (
                    `ap_form_{$rsnew['child_form']}`.`id`,
                    `ap_form_{$rsnew['child_form']}`.`element_{$rsnew['child_element']}`)
                    SELECT 
                        `{$rsnew['table_id']}` as `id`, 
                        `{$rsnew['table_field']}` as `element_{$rsnew['child_element']}`
                    FROM 
                        `{$rsnew['table_db']}`.`{$rsnew['table_name']}`
                    WHERE
                        `{$rsnew['table_field']}` <> ''
                    ORDER BY 
                        `{$rsnew['table_field']}`;
                    ";	
    }

    /* Select field names for each field*/
    function Select_Checkboxes_Fields($form_id, $element_id){       

        return "
            SHOW COLUMNS
            FROM
                `ap_form_{$form_id}`
            WHERE
                field LIKE '%element_{$element_id}%'
        ";
    }
    
    /* Select Checkboxes Query*/
    function Select_Checkboxes($form_id){

        return "    
            SELECT
                element_id
            FROM
                `ap_form_elements`
            WHERE
                form_id = {$form_id}
            AND element_type = 'checkbox'
        ";

    }

    /* Select a Checkbox's Options */
    function Select_Checkbox_Options($form_id, $element_id){
        return "
            SELECT
                `option_id`
            FROM
                `ap_element_options`
            WHERE
                `form_id` = {$form_id} 
            AND `element_id` = {$element_id}
            ";
    }

    /* Remove Checkbox Options */
    function Drop_Checkbox_Field($form_id, $fieldName){
        return "ALTER TABLE `ap_form_{$form_id}` DROP COLUMN `{$fieldName}`; ";
    }
    function Drop_Checkbox_Column($form_id, $element_id, $option_id){
        return "ALTER TABLE `ap_form_{$form_id}` DROP COLUMN `element_{$element_id}_{$option_id}`; ";
    }

    /* Add Checkbox Options */
    function Add_Checkbox_Column($form_id, $element_id, $option_id){
        return "ALTER TABLE `ap_form_{$form_id}` ADD COLUMN `element_{$element_id}_{$option_id}` TINYINT(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Checkbox - {$option_id}'";
    }
    function Add_Checkbox_Other($form_id, $element_id){
        return "ALTER TABLE `ap_form_{$form_id}` ADD COLUMN `element_{$element_id}_other` TEXT COMMENT 'Choice - Other'";
    }

?>