JotForm User Guide / Advanced Features /

How to send Submissions to Your MySQL Database Using PHP

How to send Submissions to Your MySQL Database Using PHP

First thing you'll need to learn is to know how to post submission data to your thank you page.  You can read and learn from the this guide

Assuming that you have set your form to post data to a custom URL page, you will have do some PHP/MySQL programming to do a form to MySQL.

The data being posted by JotForm is actually an array so to find out the array keys and values, you will have to try to insert this in your script:

   print_r( $_POST );   ?>

Next:  Do an actual submission to test your form and get the result.  Once you have submitted the form, it should be redirected to your custom thank-you URL that will receive the post data.  Here's an example of the data from a sample form:  https://www.jotform.com/form/11814245193 

Array
(
    [submission_id] => 186725738203177939
    [formID] => 11814245193
    [ip] => 203.177.93.98
    [name] => sample name test data
    [email] => email@gmail.com
    [phonenumber13] => Array
        (
            [0] => 3343
            [1] => 4234
        )

    [subject7] => Technical Support assistance
    [message6] => test
)

Also, you can test your PHP script via Postman Tool. You can download Postman from this link.

You can able to test your PHP script to see posted submission data and run simple debug methods easily even in your local environment. 

Let's do a simple POST request with our PHP script using Postman.

You can download PHP script example from here.

(Feel free to make a submission using this form to see the array result for yourself )

Now that we have the keys, we can now play around with these information, create your own fields on your database.  As for this example, this is what my fields look like:

Table structure

Creating your PHP script

Now that you have created your database, you can start creating your PHP script.

Step 1: To prevent possible SQL injection vulnerabilities to your database, we'll need to run PHP's addslash() function for every post variable.  And since the $_POST data is an array, let's make a function that will loop on every variable:

// 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);
        }
}

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

?>

Step 2:  I identified each POST data with a variable.  You will also notice that I merged the two phone number POST variables in to one (I just liked it that way :) ):

$submission_id = $_POST['submission_id'];
$formID =
$_POST['formID'];
$ip =
$_POST['ip'];
$name =
$_POST['name'];
$email =
$_POST['email'];
$phonenumber =
$_POST['phonenumber13'][0] ."-". $_POST['phonenumber13'][1];
$subject =
$_POST['subject7'];
$message =
$_POST['message6'];

?>

Step 3:  Now we initialize the database connectivity for the script to MySQL


$db_host = 'db hostname here';
$db_username = 'db username here';
$db_password = 'db password here';
$db_name = 'name of your database';

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

Step 4: To check if an insert or an update should be done, a search should be initialized if the submission ID exists and then set a condition that if the submission ID exists, an update should be initialized using that submission ID.  And if there is no submission ID matching, then a new insert is going to be made:


// search submission ID

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

if ($resultcount > 0) {
 
    mysql_query("UPDATE `table_name` SET
                                `name` = '$name',
                                `email` = '$email',
                                `phone` = '$phonenumber',
                                `subject` = '$subject',
                                `message` = '$message'       
                             WHERE `submission_id` = '$submission_id'")
     or die(mysql_error());
   
} else {

    mysql_query("INSERT INTO `table_name` (submission_id, formID, IP,
                                                                          name, email, phone, subject, message)
                               VALUES ('$submission_id', '$formID', '$ip',
                                                 '$name', '$email', '$phonenumber', '$subject', '$message') ")
    or die(mysql_error()); 

}
?>

You're done.  Name the script as your thank you php file and upload it to match the exact detail that you have set in your Custom Thank You URL

Here's the complete script:

// 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'];
$email =
$_POST['email'];
$phonenumber =
$_POST['phonenumber13'][0] ."-". $_POST['phonenumber13'][1];
$subject =
$_POST['subject7'];
$message =
$_POST['message6'];

$db_host = 'db hostname here';
$db_username = 'db username here';
$db_password = 'db password here';
$db_name = 'name of your database';


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


// search submission ID

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

if ($resultcount > 0) {
 
    mysql_query("UPDATE `table_name` SET
                                `name` = '$name',
                                `email` = '$email',
                                `phone` = '$phonenumber',
                                `subject` = '$subject',
                                `message` = '$message'       
                             WHERE `submission_id` = '$submission_id'")
     or die(mysql_error());
   
} else {

    mysql_query("INSERT INTO `table_name` (submission_id, formID, IP,
                                                                          name, email, phone, subject, message)
                               VALUES ('$submission_id', '$formID', '$ip',
                                                 '$name', '$email', '$phonenumber', '$subject', '$message') ")
    or die(mysql_error()); 

}

?>

Send Comment

115 Comments...

See all comments
  • Patricia Thuli

    Ii wanna know if im registered on the Data base


  • tdp4events

    so how i can insert data ?

  • lplatz

    Can this script be modified to post form data into more than one table?

  • adel

    Thank you! this was great and helped me finish my marketing project on time. Appreciate all the awesome examples!.

    Regards,

  • hammersoftware

    Just thought I'd mention, you're giving bad advice on how to solve SQL injection. Adding slashes is not going to work properly if the person is not using MySQL, which is why the php documentation for addslashes() specifically states that is bad advice and suggests that you use a database-specific method to solve it.

    Instead a proper suggestion to tell the person wanting to save your data is to use prepared statements:
    https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks/8265319

    If you're using PHP, this can be accomplished via PDO or PEAR, or for MySQL specifically, you can use mysqli.

    Using backslashes in SQL code can introduce a whole host of other problems.

    From the same stackoverflow article:
    For example, there(1) are(2) still(3) many(4) answers(5), including the second most upvoted answer suggesting you manual string escaping - an outdated approach that is proven to be insecure.

  • ampsys

    How do you get the array to include ALL data fields even when the form is set to skip/hide fields based on certain responses?

    The problem I'm having is that I want the form to be interactive, but the results feed keeps changing. Sometimes it doesn't include fields altogether, other times it does but with a NULL value (which is fine), and yet other times still, the order is all jumbled up.

    Obviously this causes the sql insert to fail because of mismatched data types or missing values altogether.

    Please help!

    Thanks.

  • Sarika

    How did you generete the form id and submission id in above program. I want to do the same in my project.
    So please guide about this so that i can use that id for updating the information by user.

  • vivek raj

    Hey,
    Great, thanks for sharing us. really it's very useful.

  • Mariana

    test experience

  • bishopkldavis2

    WHEN SUBMITTING FORM USER SELECT AMOUNT OF $75.00 WHICH PAYPAL ACCEPTS WITH NO PROBLEMS. AMOUNT IS ENTER INTO PAYPAL, BUT NOT THE DATABASE. ALL THE FIELDS ARE ENTERED INTO THE DATABASE WITH THE EXCEPTION OF (registrationFee).

    URL: https://www.jotform.com/build/90867582341161


    mysql_query("INSERT INTO `registration` (submission_id, formID, ip, studentname, birthdate24, gender, address, studentemail6, mobilenumber, phonenumber25, degreetype, dateregistration, registrationFee)
    VALUES ('" . $submissionData["submission_id"] . "',
    '" . $submissionData["formID"] . "',
    '" . $submissionData["ip"] . "',
    '" . $studentname . "',
    '" . $birthdate24 . "',
    '" . $submissionData["gender"] . "',
    '" . $submissionData["address"] . "',
    '" . $submissionData["studentemail6"] . "',
    '" . $mobilenumber . "',
    '" . $phonenumber25 . "',
    '" . $submissionData["degreetype"] . "',
    '" . $dateregistration . "',
    '" . $submissionData["registrationFee"] . "' ) ") or die(mysql_error());

  • تبلیغات گوگل

    very thanks, how i can do this with Ajax?

  • Chinakwadom

    Send me more updates

  • PRAJESH YEOTIKAR

    Can i create an online customer entry database and when customer enters his/her name then the message should be automatically sent to him/her

  • Vanishree

    Good example to understand

  • shubham

    This will not work for PHP

  • Bjorn

    Hi!
    This will not work for PHP 7.0

  • manjeet

    hello world!

  • twarren42

    Thanks for the excellent example of posting form data to a database.

  • john

    This is working!

  • mshafiq1

    great nice script

  • test

    thanks for the script

  • mario

    Obligatory warning: `ExtendedAddslash()` is poor advise and will NOT prevent SQL injections. addslashes itself is insufficient to account for charset variations. See also: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

  • vivek raj

    Great, Thanks For Sharing.

  • Hello

    Thanks.

  • puneet

    good but worst

  • puneet

    good but worst

  • Php On Web

    Hi dude, i have also find out one good example
    Variables – Php Example

  • Rob

    Really nice tutorial thank you

  • Morris

    Thanks

  • chris

    thank you

  • Gede Adi

    Hai, can I have a php code submit data without using tag form ? so like if I click specified button so some data will send to data base (CRUD)

    BEst Regards

  • Oscar Gatheru

    awesome

  • vaishshagun

    i had a query that where we initalize our array??

  • Itunes customer service

    This program has some bugs when the attached the date be to send the MySQL query to show you the error.

  • HNUSK

    Hi,

    I am new to PHP, Please help me ...
    I need to display each student details from database into html form one by one on clicking next button using php.

  • Javed UR Rehman

    This is quite simple and easy tutorial, i also shared a tutorial about how to display data from database into HTML table, i hope you will like it. https://www.allphptricks.com/display-data-from-database-into-html-table-using-php/

  • lalithsagar

    i am new to php actually i have a question. i created a form and oce i submit the form data has to save in my database but data is not saving.can anyone help me with that.





    div {
    background-color: lightgrey;
    width: 300px;
    border: 25px solid green;
    padding: 25px;
    margin: 25px;
    }



  • Modestus

    Hello Chatmate! Can you please assist me with PHP and SQL codes.

    I wannah develop an ITs where i will need to use a form to entering Data in a SQL.

    Best regards

  • Xprobes

    i wonder, i have created a custom post type form for email submission. I was thinking that do they also get saved in any of my wp directory also. The forms are submitted to external post type to external url. Is there any trace submitted email left?

  • Chitti

    Very usefull

  • nani

    good

  • Imran

    Dears,
    Hello I want to create simple website. in website 3 user have been working. one is student, second is teacher and third is principle. when the student is a holiday, he fill the application form and submit to teacher not directly submited to principle. then teacher is checked the application and forward to principle. plz tell me how teacher forward application thorugh php and mysql. i just do not know forward. plz help me. tell me smiple method code or refrence link plz help me.


    Thanks

  • Akash Aher

    awesome article i like it

  • Jan Hovens

    dit is een proef mail

  • karthik

    its very good

  • karthik

    its very good

  • javeriamahveen

    Can I use Oracle database?

  • hhaaee

    plz tell me in easy way

  • omkar

    lets see i have made a poster and on bottom of it i add email, your name and submitt. When i submit it shows the same page. even is we chnge its input name, id for match up. its still not matching

  • anam

    where i have to save php file? because i save my php file in folder Home/username/php/tests... cant match by system .