How to connect html form to postgresql database

Saving form data from HTML directly to a Postgres database usually requires you to set up a backend. And then connect that back-end to your Postgres database.

In this tutorial, we will show you how to save data from your HTML form directly to your Postgres database.

Inspect table in 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

Set up a contact form in an HTML file

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 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:

1. name, line 29
   This will be a text field with the full name of the person
2. email, line 33
   This will be a text field with the email of the person
3. age, lines 37–38
   This will be a radio selection field with two age options. 18–35 and 35+. We will denote them in the value attribute of the radio fields
4. message, line 42
   This will be a textarea that will contain the message the person types

All four tags will be inside a 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 [line 47] that will be submitting the data. In its title 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.

Connect your Database to API Spreadsheets

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

Get your API URL for your Database Table

If your database connection was successful you will be taken to the database configuration page

Here, you can create an API for the database table we will be saving the form data to

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

//gist.github.com/asharma327/a39add55791c378427fcab26aecf9fe1#file-contactformpublic-htmlIf 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

Configure the Javascript to submit data from the form

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 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 :[.

That’s it! We have done everything needed to save data from our web form to our Postgres table.

Now let’s test it out and see how to view our data.

Testing

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 and we can try to help you out.

3. View your data in your Postgres data

It should be saved directly in your table

JavaScript Function Explained

Finally, let’s walk through the Javascript function we used to submit the data

1. We use jQuery syntax to call ajax. That’s what the $.ajax does
2. The url is the URL we are calling from the request. In this case it is a type post request.
3. The data is our data from #myForm. The serialiazeArray[] method creates a JavaScript array of objects, ready to be encoded as a JSON string [don’t worry if you don’t understand this too deeply. As long as you name your inputs EXACTLY the same as your column names in your table, then this whole demo should work]
4. If the request was successful then the success function is called.
5. If the request had an error then the error function is called.

If you have any further questions, feel free to email us at ! We can’t wait to see what you build.

Check out our other tutorials!

How fetch data from PostgreSQL database and display HTML table?

How do I connect my Postgres database to my website?

How fetch data from PostgreSQL database in PHP?

How send data from database to HTML?