How to Insert Form Data Into the Table Using Node.js and MySQL

Node.js MySQL Insert Into

Before Getting Started this Script, You must do the following things.

You have to Install Basic Express Application like the following project folder structure.

myapp/
  |__bin
  |__node_modules
  |__public
  |__routes/
  |    |__index.js
  |    |__user.js
  |__views/
  |    |__index.ejs
  |    |__user.ejs
  |__app.js
  |__database.js
  |__package-lock.json
  |__package.json

Even, You have to connect Node.js with MySQL Database by writing the following connection script in the database.js

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost', // Replace with your host name
  user: 'root',      // Replace with your database username
  password: '',      // Replace with your database password
  database: 'nodeapp' // // Replace with your database Name
}); 
conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

Create a Table into MySQL Database

Create a table into MySQL database with name users as the following query.

CREATE TABLE `users` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `fullName` varchar(255) DEFAULT NULL,
  `emailAddress` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
   `country` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If you are working with PHP, You can create nodeappdatabase & users table in localhost/PHPMyAdminand use it for inserting the form data.

Create an HTML Form in Node.js App

Now, Use the following HTML code in the views/user.ejs to insert data using Node.js & MySQL.

File Name: user.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
* {
  box-sizing: border-box;}
.user-detail {
    height: 100vh;
    border: 2px solid #f1f1f1;
    padding: 16px;
    background-color: white;
    width: 30%;}
input{
  width: 100%;
  padding: 15px;
  margin: 5px 0 22px 0;
  display: inline-block;
  border: none;
  background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
    background-color: #ddd;
    outline: none;}
button[type=submit] {
    background-color: #434140;
    color: #ffffff;
    padding: 10px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 100%;
    opacity: 0.9;
    font-size: 20px;}
label{
  font-size: 18px;;}
button[type=submit]:hover {
  background-color:#3d3c3c;}
</style>
</head>
<body>
<!--====form section start====-->
<div class="user-detail">
    <h2>Create User Data</h2>
    <form action="/user/create" method="POST">
          <label>Full Name</label>
          <input type="text" placeholder="Enter Full Name" name="fullName" required>
          <label>Email Address</label>
          <input type="email" placeholder="Enter Email Address" name="emailAddress" required>
          <label>City</label>
          <input type="city" placeholder="Enter Full City" name="city" required>
          <label>Country</label>
          <input type="text" placeholder="Enter Full Country" name="country" required>
          <button type="submit">Submit</button>
        </div>
</div>
<!--====form section start====-->
</body>
</html>

This form page will open through http://localhost:3000/user URL.

Use the following script in the routes/user.js file and lo to load the HTML form.

router.get('/', function(req, res, next) {
  res.render('user');
});

You must declare the following points:

Form method must be POST like method="POST"
Form action contains user/create/ ( It is created in routes/user.js to post form data ) like action="user/create".
You should declare field name the same as the column name of the users table.

Form Field	Field Name
Full Name	name=”fullName”
Email Address	name=”emailAddress”
City	name=”city”
Country	name=”country”

Write MySQL Query in Node.js to Insert Data

First of all, Include the database connection file in the routes/user.js file

var db=require('../database');

Now use one of the following scripts in the routes/user.js file to insert the form data into the table.

router.post('/create', function(req, res, next) {
   
      const userDetails=req.body;
         
      var sql = 'INSERT INTO users SET ?'; 
      db.query(sql, userDetails,function (err, data) {
          if (err) throw err;
              console.log("User dat is inserted successfully ");
           });
      res.redirect('/user');
});

router.post('/create', function(req, res, next) {
    
      var fullName     = req.body.firstName;
      var emailAddress = req.body.emailAddress;
      var city         = req.body.city;
      var country      = req.body.country;
      
     var sql = `INSERT INTO users (fullName, emailAddress, city, country ) VALUES ('${fullName}', '${emailAddress}', '${city}', ${country} )`;
     db.query(sql,function (err, data) {
        if (err) throw err;
             console.log("record inserted");
         });
     res.redirect('/user');
});

Complete Script: user.js

var express = require('express');
var router = express.Router();
var db=require('../database');
router.post('/create', function(req, res, next) {
  
  // store all the user input data
  const userDetails=req.body;
 
  // insert user data into users table
  var sql = 'INSERT INTO users SET ?';
  db.query(sql, userDetails,function (err, data) { 
      if (err) throw err;
         console.log("User dat is inserted successfully "); 
  });
 res.redirect('/user');  // redirect to user form page after inserting the data
}); 
module.exports = router;

Include the user.js file in app.js root file

You have to include user.js route file in app.js the root file as.

var userRouter = require('./routes/user');
app.use('/user',userRouter);

You can read the official documentation of express routing to learn Routing in express

NEW TECH UPDATES

Search This Blog