User Guide

 

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:  http://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
)

(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

60 Comments...

See all comments
  • valuchi

    I will like to know if their is any one how can help me redirect script that usually submit in mysql database so it can be submission to email so I can use it on c-panel or shell. I am willing to pay if it can be done.

  • Mugisha isaac

    How do I connect allm that to the HTML form ??

  • Hery

    how can I get information about my app Facebook and to send it to mysql

  • Alemayehu Waza

    Sorry this idea is very complex to understand users and different from other sources it may be give some idea but does not give full idea there try to solve how to insert or retrieve data into or from based on or to the project!.

  • Surendra Bharadwaj

    Mr. XYZ,
    It is a suggestion to change the background of your page.
    And change the visibility of content.

    If someone need help from your website, how he/she will take help.

    There is no match of mackground color and text color in your website......

  • ifin2000

    Hi,
    I need your help.
    when I redirect webhook to https://www.jotform/show-data-post/ there are correct output (array and php code $_POST[...])

    but where I redirect to my php script to catch POST data, array is blank, no data any more.
    print_r($_POST);
    $id_post = $_POST['submission_id'];
    $id_form = $_POST['formID'];
    $ip_addr = $_POST['ip'];
    $nama = $_POST['nama'];
    $mail = $_POST['email'];

    where is the mistake?
    please help me,
    because I had already fallen in love with jotform :)

    thanks

  • shiprequest

    Can you just make a module that transmits everything from Jotform to a MySQL database including the pdf extensions?

  • Hannah

    Hi, good day. Can you make a video of this? So that it will be more clear. Thank you and I'm looking forward to your reply.

  • Rick

    I want to create a form where, for example, a person enters a 7 digit number. Each number represents a different field, although when completing the form the person just records the 7 numbers in the text box. The php form would then pull out the values in the database fields. I don't know if that made sense but if you know what I mean does anyone know of a script/code for it? Many thanks!

  • Francis

    your explanation looks like what i really want to do on my joomla project. but m confused! how do i get this form builder and use it on joomla.
    Thank you.

  • AirGermanWolf

    Where I put the php code?

  • tort

    Its really great helpfull tutorials ,i solved this problem right here HELPFULL TUTORIALS

  • Hello, guys please help. Am learning programming. Have created the php script and a form that should enable me input data to a table in mysql data base but when I run, it literally does not input data but the following message is displayed." Parse error:syntax error, unexpected '$password'(T-VARIABLE)in C:\xammp\htdocs\w\insert.php on line 4.

    Your help will be highly appreciated!

  • Abhinav

    want to connect my php form with data base

  • pktfteam

    how can submission show in app and website

  • tochi

    What did you mean by this please " . 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 "

  • Abdulrasheed

    Guys it works but you must correct some things in the above code depending on your database table/fields

  • charie

    I have used the code above and got errors that 'undefined index:name'. How do i correct that error and why does it bring that error?

  • serge@fitlink.edu.au

    I have created a mysql database and the following thankyou.php file was placed under publich_html folder of the server (http://pastiebin.com/5580f1d5f340f). I have turned Post Data on, have pointed the thank you URL to sergebaric.com/thankyou.php and am also pointing a webhook to it. Have not been able to get it working yet.

    form url: http://form.jotform.co/form/41321763228854

    Any help will do. Thanks.

  • paul

    I need to get data entered in a website form into my FileMaker database. There may be about 150 forms submitted per day. How can this be accoplished?

  • GSPNetworks

    Hi I was just wondering is there a way to get this to work with SQL Server and not MySql?

  • razan

    Hi, i was creat a form here and i have my database i create it using xampp how i can connected to the form ,can you help me please ...

  • Michael

    hi I have looked over several different tutorials on this subject and this far in for some reason I have not yet managed to get my form data into my database what are the chances if I give you my codes you can review them for m and *hopefully* shed some enlightening on me please........
    the system I have been trying t use is as follows:
    I have a rather crude looking form consisting of a method, action = post [to a PHP action file &
    a text area & lastly a submit button........
    and that's it nothing more in that html file;
    the action file opens a DB connection my DB with my user name and p/w and of course the DB name
    once I submit my form I receive my pre defined message that the data was saved so I know there are no error there however once I go in PHPmyadmin and look at my table there has been no data saved there since I manually inserted 2 rows

  • RCinSTP

    When you get the error below. Try going into phpMyAdmin and adding one row of bogus data to your table. That way, when it counts the number of rows it will find 1 (it is looking for more than zero)

    Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in line 33
    Below is the 33 in my file. "$resultcount = mysql_numrows($sqlsearch);

  • RCinSTP

    After the person submits the form, I would like to show a Thank You message. Is there some sort of code like below that could be place near the end of the php code?

    if (mysql_query) === TRUE) {
    echo "Thanks Dude!";
    }

  • harry

    I submitted a form to my database but I searched the database and couldn't find the input. meanwhile it didn't display any error message

  • jamesforpm

    What is the appropriate way to handle checkboxes in mySQL? I've created a column in my db that maps to a checkbox field but when I submit data to it, it shows as Array rather than what has been selected. Thanks.

  • RAccessPro

    I have taken the code above, inserted what I need from my form as well as my sql database,and I am getting nothing

    Can I email the info to someone for trouble shooting ?

  • jamesforpm

    If I have two fields that I want to merge, would the code be:

    $phonenumber = $_POST['field1'] ."-". $_POST['field2'];

    Then in my db this would appear in one column as 'field1' - 'field2'

  • RAccessPro

    do i have to put in the open php tag first

    "

  • jamesforpm

    In the search ID section of the PHP script, I assume the 'table_name' has to be updated to my table name, is that correct?

  • dalepotter_upreach

    Thanks for this tutorial, really helped us with lots of form inputting!

    By the way, everyone should be using the 'mysqli_query' method to connect to MySQL databases as 'mysql_query' will be removed from PHP in future: http://uk3.php.net/manual/en/function.mysql-query.php

  • Akabuo

    very nice

  • texico

    Nice explanation, what a pity that PHP is not really easy to learn.

  • tunji

    hi,i try to run the form to mysql database and it was successful but i cant found the input inside the database.it also show me undefined variable in line 33.here is my codes

  • donadioj

    This is great, but how could I run a search on my database and prepopulate form input with data that already corresponds to an id before updating.

  • dgkg

    So now we can write the fields to a database, but now we need to read it back also and place it onto the page, do you have an example like above to show me how this is done?

  • Antonio

    Jotform folks

    I just copy and the example provided and change all the parameters/string with my database information, but the following message has displayed:

    Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in line 33
    Below is the 33 in my file. "$resultcount = mysql_numrows($sqlsearch);
    "
    Any help is greatly appreciated.

    Thanks

  • sportsaac

    Getting the following error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''lacrosse_test' (submission_id, formID, ip, fullname3, email4, newto) VALUE' at line 1

    Any ideas?

  • selin

    I need to update my entries in my login page to my database i need coding help

  • Iwan Saputa

    Thank you, finally i found your tutorial was work for me after spend 6 hours just looking for update mysql.

  • embscareers

    Hello,

    Thanks for the script. It works as charm for me. The only one problem I have is that when I include the item "file" for the submitted document. The thank you page shows me that : Unknown column 'file' in 'field list'

    That's the code I have:


  • Xavier

    Hello,

    I do have a concern as sometime jotform add some figures after the field I created.

    Let say I add an input field name. On the Post I get on my page, the field that correspond to name is [name17]. Which of course cannot be inserted in my Database.

    Of course in my landing page, I could make a script in order to recover the corresponding field but that would not always work

    Do we have a possibility to put another tag for the input field (i want my label = name and the post id will be 'username')

  • Dir3t

    Hello I created a form in here just jot form I tried to insert it for my school project where I wanted to implement the fumulário that I created with my SQL database in php.

    I tried changing the script but without success, I am working on xampp but am not succeeding.

    In my SQL table with the name (user has the following data:
    login
    pw
    email
    name

    My database is called: headsport, host: localhost, name: root, and no password pw.

    Note: on my registration form only has this login, pw, email, name.

    Will anyone be able to adapt the script for my project?

    Hugs, Dir3t

  • sujeet

    my dear sir if you know joomla plz clear my doubet

    1> how can made registration form in joomla ?
    2> how can add dynamic template page in inner page plz explain details and easy langauge

  • yuyun

    sorry, i mean it is not a primary key.

  • yuyun

    how to insert the data that have the same id with data in the database.

    for example, i want to insert the data of branches from the customer master. so, it will send the customermasterId('8') and branchesId ('14') to the database.

    however, in the database, it is already have a data with customermasterId ('8')

    it means, how can i insert the data with multiple customermasterid. for your information, those id is not a foreign key.

  • amit kumar

    yu no programi data base ighj df

  • estvmachine

    Its works!, thank you. You save my job!.

  • Nyameko Yabo

    how do you retrieve a form with the information in it and updating the information on the database using php