How can I send submissions to my MySQL database using PHP?

  • dgowing
    Asked on May 3, 2017 at 1:06 PM

    I have searched thru the forum but cannot find an answer to my problem / question and am hoping you can help.

    I have a form with 4 text fields. At my office I have a mysql db that I would like to store submission data. 

    At my office I am trying to create a PHP script (I downloaded the PHP API and have created a KEY) and am having trouble retrieving the submission data. I figured to start with I would just pull down the submission data for 1 response and then echo the values, just to prove to myself that I was successful in retrieving the information. I have the form ID & the submission #. In as much detail as possible, what should my PHP script look like?

     

    Thanks

  • AIDAN
    Replied on May 3, 2017 at 2:40 PM

    Please consider following our guide on how to send submissions to your MySQL database using PHP here: https://www.jotform.com/help/126-How-to-send-Submissions-to-Your-MySQL-Database-Using-PHP

    I hope this helps. If you need further assistance please let us know. Thank you.

  • dgowing
    Replied on May 3, 2017 at 2:54 PM

    Hi Aidan. I have already reviewed this post and actually copied the sample script into my PHP code (made substitutions based on my form and db settings), but get errors when I run it. What am I missing?

    ERRORS

     

     

     

    MY CODE

    <!DOCTYPE html>
    <!--
    To change this license header, choose License Headers in Project Properties.
    To change this template file, choose Tools | Templates
    and open the template in the editor.
    -->

    <!-- //$result = curl -X GET "https://api.jotform.com/form/71216155529253/submissions?apiKey=f9762403b73d00a67c149b9f549c14d"  -->
    <?php
    include "JotForm.php";
    // This function will run within each post array including multi-dimensional arrays
    function ExtendedAddslash(&$params)
    {
            foreach ($params as &$var) {
                // check if $var is an array. If yes, it will start another ExtendedAddslash() function to loop to each key inside.
                is_array($var) ? ExtendedAddslash($var) : $var=addslashes($var);
                unset($var);
            }
    }

    // Initialize ExtendedAddslash() function for every $_POST variable
    ExtendedAddslash($_POST);     

    $submission_id = $_POST['submission_id'];
    $formID = $_POST['formID'];
    $ip = $_POST['ip'];
    $name = $_POST['name'];
    $date = $_POST['date'];


    $db_host = 'localhost';
    $db_username = 'root';
    $db_password = '';
    $db_name = 'jotformdb';

    mysql_connect( $db_host, $db_username, $db_password) or die(mysql_error());
    mysql_select_db($db_name);

    // search submission ID

    $query = "SELECT * FROM `jotformdata` WHERE `subID` = '$submission_id'";
    $sqlsearch = mysql_query($query);
    $resultcount = mysql_numrows($sqlsearch);

    if ($resultcount > 0) {
     
        mysql_query("UPDATE `table_name` SET
                                    `subdate` = '$date',
                                    WHERE `subID` = '$submission_id'")
         or die(mysql_error());
       
    } else {

        mysql_query("INSERT INTO `jotformdata` (subID, formID, subIP, subName, subDate)
                                   VALUES ('$submission_id', '$formID', '$ip',
                                                     '$name', '$date) ")
        or die(mysql_error()); 

    }
    ?>

  • Kevin Support Team Lead
    Replied on May 3, 2017 at 5:01 PM

    I think the error you are getting may be related to the fact that the name and date field will be an array since they have sub-fields, for example, the name field has the first and last name fields, while the date field has three sub-fields: date, month and year. 

    You could try modifying your code to get properly the data: 

    $submission_id = $_POST['submission_id']; 

    $formID = $_POST['formID'];

    $ip = $_POST['ip'];

    $name = $_POST['name'][0]." - ".$_POST['name'][1];

    $date = $_POST['date'][0]." / ".$_POST['date'][1]." / ".$_POST['date'][2];

    You will find this explained on the step 2 of the guide shared above.

    To identify which fields behave like this you could print the POST variable that contains the submitted info, this code will help you to get it: 

    echo "<pre>";

    print_r($_POST);

    echo "</pre>"; 

    This will be the result: 

    How can I send submissions to my MySQL database using PHP? Image 1 Screenshot 20

    If you're getting any error at the moment of running the connection to your database or executing the query, you could also try replacing your code with this one: 

    $query = "SELECT * FROM 'jotformdata' WHERE 'subID' = '".$submission_id."'";

    $sqlsearch = mysql_query($query);

    $resultcount = mysql_numrows($sqlsearch);

     

    if ($resultcount > 0) {

        mysql_query("UPDATE 'table_name' SET 

                                    'subdate' = '".$date."',

                                    WHERE 'subID' = '".$submission_id."'") 

         or die(mysql_error());

        

    } else {

     

        mysql_query("INSERT INTO 'jotformdata' (subID, formID, subIP, subName, subDate) 

                                   VALUES ('".$submission_id."', '".$formID."', '".$ip."', 

                                                     '".$name."', '".$date.") ") 

        or die(mysql_error()); 

     

    Do let us know how it goes. 

  • Welvin Support Team Lead
    Replied on May 5, 2017 at 5:30 PM

    The given guide won't need you to setup an API. You just need to enable the Send Post method in your PHP form and create your PHP script as shown in the guide. 

    For the API, our API team can help you about it. Send them an email at api@jotform.com.