Adhaar Sharma on 2020-06-10
Saving form data from HTML directly to a MySQL database usually requires you to set up a backend. And then connect that back-end to your MySQL database.
In this tutorial, we will show you how to save data from your HTML form directly to your MySQL database.
We need to inspect the table that will be storing our form data
It is called accounts and has these text fields
1. name
2. email
3. message
4. age
Okay, if you don’t know any HTML, then this might be a steep learning curve. The example is small so you should Google terms you don’t understand and it might make sense.
Let’s create a form in HTML. It is a basic contact form and does not contain any styling.
The form will look like this:
Open up a blank file in the text editor you use for programming, copy and paste the code below and save the file with a [.html] extension. We saved ours as ContactForm.html
We will walk through the HTML part now and the Javascript part later.
The contact form has 4 fields.
Pay attention to the name attribute of the <input> tags. These MUST match up with the column names of our database table.
We are going to name the four input tags as follows. The lines refer to the line of code:
All four <input> tags will be inside a <form> tag (line 26) that we will give an id attribute of myForm
Do not give it any other id as the data submission function that we will write later in Javascript is dependent on it.
Finally, our main component will be a <button> (line 47) that will be submitting the data. In its onclick attribute we are going to specify the function SubForm().
Again, do not give the function another name as we will be writing this exact function later in Javascript to handle submitting the data.
The contact form has 4 fields.
Pay attention to the name attribute of the <input> tags. These will be the column headers of our spreadsheet we save the data in and they MUST match up.
We are going to name the four input tags as follows. The lines refer to the line of code:
All four <input> tags will be inside a <form> tag (line 30) that we will give an id attribute of myForm.
Do not give it any other id as the data submission function that we will write later in Javascript is dependent on it.
Finally, our main component will be a <button> (line 51) that will be submitting the data. In its onclick attribute we are going to specify the function SubForm().
Again, do not give the function another name as we will be writing this exact function later in Javascript to handle submitting the data.
We are going to be using API Spreadsheets for this tutorial. Sign up for an account if you do not have one
1. Sign into your API Spreadsheets account and open the Database page from the main menu.
2. Click the Connect a new database button to set up a connection.
This will open a window where you'll have to enter database credentials
3. Fill out your database information
i. Select the type of database. APISpreadsheets currently supports MySQL and PostgreSQL databases.
ii. Provide connection settings by filling out the following
- Database Name
- Port
- Hostname
Note: Enter hostname value without http prefix, starting or ending slashes.
iii. If the database is password protected, enter Username and Password as well
iv. Fill out a short description of the database
4. Click Connect to connect your database
If your database connection was successful you will be taken to the database configuration page
Here, you can create an API for the MySQL table, which will store data submitted through the form.
1. Click on the Add a New API Button
2. Enter API Name & Query for your Table
You can enter anything you want for the API Name
But the query MUST be like the following. Replace accounts with the name of your table
SELECT * FROM accounts
[OPTIONAL] Click Test Query
Before you save your API, you can test if your database table is connected by testing your query
3. Click Save API
If everything is connected properly, then you will be taken to the File page for this API
4. Copy the Create API URL for your table
Copy the API URL from the Create tab for your table and save it somewhere handy. We are going to be using this in the Javascript below to submit the data.
5. [Optional] If your API is Private you will also need to copy the Access and Secret Key and save them somewhere handy
We are going to use AJAX to submit the form. Again, if you are not familiar with Javascript, jQuery or AJAX, it might be a steep learning curve.
We are going to include the full HTML code here again. The Javascript part is within the <script> tags
1. Add jQuery from a CDN (lines 5-8)
We need to ensure the FULL jQuery library is included in our HTML to use AJAX.
2. Write the SubForm() function between two script tags (lines 9–23)
The SubForm() function is below. You will replace the URL below with the URL you obtained in Step 3. The rest of the function will remain the same.
We will go through this function in more detail at the end of this post but for now all you need to know is if the data was successfully saved an alert will pop up saying Form Data Submitted :) otherwise an alert will pop up saying There was an error :(.
1. Add jQuery from a CDN (lines 5-8)
We need to ensure the FULL jQuery library is included in our HTML to use AJAX.
2. Write the SubForm() function between two script tags (lines 9–27)
The SubForm() function is below. You will replace the URL below with the URL you obtained in Step 3. The rest of the function will remain the same.
We will go through this function in more detail at the end of this post but for now all you need to know is if the data was successfully saved an alert will pop up saying Form Data Submitted :) otherwise an alert will pop up saying There was an error :(.
That’s it! We have done everything needed to save data from our web form to our MySQL table.
Now let’s test it out and see how to view our data.
1. Save your HTML file and open it in a browser to see the contact form
2. Fill out your form and click Submit
An alert window should pop up saying Form Data Submitted :)
If the alert window says There was an error :( then feel free to email us at info@apispreadsheets.com and we can try to help you out.
3. View your data in your MySQL database
It should be saved directly in your table
Finally, let’s walk through the Javascript function we used to submit the data
If you have any further questions, feel free to email us at info@apispreadsheets.com! We can’t wait to see what you build.
Check out our other tutorials!