Can you add form data from a single form into more than one mySQL table?

  • Profile Image
    lplatz
    Asked on October 24, 2019 at 09:01 PM

    Can the script (referenced below) be modified to post form data into more than one table?

    How to send submissions to your MySQL database using PHP
    https://www.jotform.com/help/126-How-to-send-Submissions-to-Your-MySQL-Database-Using-PHP



    This is a re-post of a comment on How to send Submissions to Your MySQL Database Using PHP

  • Profile Image
    roneet
    Answered on October 24, 2019 at 10:02 PM

    Yes, you can modify the query here, you can write join table queries to insert values in multiple tables.

    $query = "SELECT * FROM `table_name` WHERE `submission_id` = '$submission_id'";

    Let us know if you have further questions.

    Thanks.

  • Profile Image
    lplatz
    Answered on October 24, 2019 at 11:36 PM

    roneet provided a solution which if I were more skilled in mySQL, could probably have ran with and solved my issue.  Given I'm not a skilled mySQL developer, I discovered another means for posting form data from a single form into multiple tables within my mySQL database.

    Using the sample php file jotform provides for posting data to your personal mySQL database, I modified the following section of this form (Ref: https://www.jotform.com/help/126-How-to-send-Submissions-to-Your-MySQL-Database-Using-PHP).

    I simply replicated the "search submission ID" section for each table I wanted to post form data into.  In my case, I had a single form collecting information relevant to two tables.  Hopefully the following makes sense.  Perhaps any mySQL developers who may run across this can hang a little meat on this solution to whether this is a good or bad approach.

      

    // search submission ID

    $query = "SELECT * FROM `table_name1` WHERE `submission_id` = '$submission_id'";

    $sqlsearch = mysql_query($query);

    $resultcount = mysql_numrows($sqlsearch);

    if ($resultcount > 0) {

        mysql_query("UPDATE `table_name1` SET

                                    `tbl1item1` = '$tbl1item1',

                                    `tbl1item2` = '$tbl1item2',

                                    `tbl1item3` = '$tbl1item3',

                                    WHERE `submission_id` = '$submission_id'")

         or die(mysql_error());

    } else {

    mysql_query("INSERT INTO `table_name1` (submission_id, formID, IP, tbl1item1, tbl1item2, tbl1item3)

                                   VALUES ('$submission_id', '$formID', '$ip', '$tbl1item1', '$tbl1item2', '$tbl1item3') ")

        or die(mysql_error()); 


    }

    // This section for the 2nd table 

    $query = "SELECT * FROM `table_name2` WHERE `submission_id` = '$submission_id'";

    $sqlsearch = mysql_query($query);

    $resultcount = mysql_numrows($sqlsearch);

    if ($resultcount > 0) {

        mysql_query("UPDATE `table_name2` SET

                                    `tbl2item1` = '$tbl2item1',

                                    `tbl2item1` = '$tbl2item1',

                                    WHERE `submission_id` = '$submission_id'")

         or die(mysql_error());

    } else {

    mysql_query("INSERT INTO `table_name2` (submission_id, tbl2item1, tbl2item1)

                                   VALUES ('$submission_id', '$tbl2item1', '$tbl2item1') ")

        or die(mysql_error()); 


    }