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

  • Profile Image
    dgowing
    Asked on May 03, 2017 at 01: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

  • Profile Image
    AIDAN
    Answered on May 03, 2017 at 02: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.

  • Profile Image
    dgowing
    Answered on May 03, 2017 at 02: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()); 

    }
    ?>

  • Profile Image
    Kevin_G
    Answered on May 03, 2017 at 05: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: 

    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. 

  • Profile Image
     
    Answered on May 05, 2017 at 04:13 PM

    I believe I am getting further but still not achieving what it is I am wanting to do.

     

    At my office I have a mysql db and am trying to retrieve the data from the submitted forms and then insert the data into my db.

     

     

     

    I have installed the api Jotform.php, and generated an API KEY, and have looked at your api docs but am still having trouble retrieving the submission data. Of course until I am able to retrieve the data I will not be able to write it to my DB.

     

     

     

    I have 3 fields on my form (Name, Date & File Upload). I have a form with 3 submissions. In my code I am trying to retrieve submission #2 and trying to echo anything at all just to confirm I have the data. When I run my code I get 1 array back but I can’t make sense of the data it is returning. Below is my code and the results I get back.  The first couple of array values are X and then blank and then G. I can’t match that with any of my submission data.

     

     

     

    Here is 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.

     

    -->

     

     

     

    <?php

     

    include "JotForm.php";

     

     

     

    $ch = curl_init();

     

    if (!function_exists('curl_init')){

     

        die('Sorry cURL is not installed!');

     

    } else {

     

        echo 'ok to proceed';

     

    }

     

     

     

    $results = 'X GET https://api.jotform.com/submission/xxxxx/?apiKey=zzzzz' ;

     

     

     

    if (!$results){

     

        echo ('<br> no results');

     

    } else {

     

        echo '<br> I have some results <br>';

     

    }

     

    $arraycount = count($results);

     

    echo 'result count =   ';

     

    echo $arraycount;

     

    echo '<br><br>';

     

    if ($arraycount = 1) {

     

        //$fname = $results['submission_id'];

     

        echo 'first val <br>';

     

        echo $results[0] . '<br>';

     

             echo '2nd val <br>';

     

        echo $results[1] . '<br>';

     

         echo '3rd val <br>';

     

        echo $results[2] . '<br>';

     

            echo "all done";

     

    }

     

     

     

    ?>

     

     

     

     

     

     

     

     

     

    Here are the results I get

     

    ok to proceed
    I have some results 
    result count = 1

    first val 
    X
    2nd val 

    3rd val 
    G
    all done

     

     

     

     

     

    Here are my submissions that I manually exported to excel

     

    Submission Date

    First Name

    Last Name

    Date

    5/2/2017 15:05

    w

    z

    5/2/2017

    5/2/2017 14:44

    Joe

    Dash

    5/8/2017

    5/2/2017 14:40

    Darcy

    Forde

    5/2/2017

     

     

     

  • Profile Image
    Welvin
    Answered on May 05, 2017 at 05:30 PM

    The given guide won't need you to setup an API. You just need to enable the Send Post method in the 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