CODEWITHSUNDEEP
htmlmysqlnode.jsexpress
Anshul Garg
Anshul Garg

Reputation: 11

Insert data from form into MYSQL using Node JS

I am trying inserting the data into MySQL using node and Express framework from a HTML form. The code for the form is :

<html>
<head>
<title>Personal Information</title>
</head>
<body>
<div id="info">
<h1>Personal Information</h1>
<form action="/myaction" method="post">

        <label for="name">Name:</label>
        <input type="text" id="name" name="name" placeholder="Enter your full name" />
    <br><br>
        <label for="email">Email:</label>
        <input type="email" id="email" name="email" placeholder="Enter your email address" />
    <br><br>
        <label for="city">City:</label>
        <input type="text" id="city" name="city" placeholder="Enter your city" />
    <br><br>
    <label for="pincode">Pincode:</label>
        <input type="text" id="pincode" name="pincode" placeholder="Enter your pincode" />
    <br><br>
        <input type="submit" value="Send message" />
</form>
</div>
</body>
</html>

and the .js file is :

var express = require('express');
var app = express();
var ejs = require('ejs');
var mysql = require('mysql');
var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true })); 
var HOST = 'localhost';
var PORT = 3000
var MYSQL_USER = 'root';
var MYSQL_PASS = 'jelly123#';
var DATABASE = 'form';
var TABLE = 'info'; 

var mysql = mysql.createConnection({
host: HOST,
port: PORT,
user: MYSQL_USER,
password: MYSQL_PASS,
});
app.get('/home',function(req,res,next){
res.sendfile('views/forms.html');
});
app.post('/myaction', function(req, res) {
console.log('req.body');
console.log(req.body);
res.write('You sent the name "' + req.body.name+'".\n');
res.write('You sent the Email "' + req.body.email+'".\n');
res.write('You sent the City "' + req.body.city+'".\n');
res.write('You sent the Pincode "' + req.body.pincode+'".\n');
res.end()

mysql.query("Insert into "+TABLE+" (name,email,city,pincode) VALUES ('"+req.body.name+"','"+req.body.email+"','"+req.body.city+"','"+req.body.pincode+"')",function(err, result)      
{                                                      
  if (err)
     throw err;
});
});
app.listen(3000);
console.log('Example app listening at port:3000');

I am able to enter the form data and display it in the page http://localhost:3000/myaction but unable to Insert data into database Please mention where I am doing wrong.

Thanks in advance.

Upvotes: 1

Views: 41035

Answers (5)

Harish Pandey
Harish Pandey

Reputation: 1

    index.html

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Registration Form</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <style>
    .error{
        color: red;
        font-style: italic;
    }
  </style>
</head>
<body>
    <button type='text' class="btn btn-primary" id="showbtn">Show/Hide Form</button>
    <div class = "container">
        <div class = "col-md-6 col-md-offset-3">
            <div class = "panel panel-primary">
                <div class = "panel-heading">
                    Registration
                </div>
                <div class = "panel-body">
                    <form id = "registration" action="http://127.0.0.1:7744/register">
                        <input type = "text" class = "form-control" name = "username" placeholder = "Username"/>
                        <br/>
                        <input type = "text" class = "form-control" name = "email" placeholder = "Email"/>
                        <br/>
                        <input type = "password" class = "form-control" name = "password" placeholder = "Password" id = "password"/>
                        <br/>
                        <input type = "password" class = "form-control" name = "confirm" placeholder = "Confirm Password"/>
                        <br/>
                        <input type = "text" class = "form-control" name = "fname" placeholder = "First Name"/>
                        <br/>
                        <input type = "text" class = "form-control" name = "lname" placeholder = "Last Name"/>
                        <br/>
                        <div class = "gender">
                            <label class="radio-inline"><input type="radio" value="male" name="gender" class = "form-contorl"/>Male</label>
                            <label class="radio-inline"><input type="radio" name="gender" value="female" class = "form-contorl"/>Female</label>       
                        </div>
                        <br/>
                        <select class = "form-control" name = "city">
                            <option value="0" selected="" disabled="">--SELECT--</option>
                            <option>Delhi</option>
                            <option>Ghaziabad</option>
                            <option>Noida</option>
                            <option>Faridabad</option>
                            <option>GuruGram</option>
                        </select>
                        <br/>
                        <textarea class = "form-control" name = "address" placeholder="Address"></textarea>
                        <br/>
                        <button type = "submit" class = "btn btn-primary" >Submit</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.validate.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script> $(document).ready(function () {
        jQuery.validator.addMethod("noSpace", function (value, element) {
            return value == '' || value.trim().length != 0;
        }, "No space please and don't leave it empty");
        jQuery.validator.addMethod("customEmail", function (value, element) {
            return this.optional(element) || /^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/.test(value);
        }, "Please enter valid email address!");
        $.validator.addMethod("alphanumeric", function (value, element) {
            return this.optional(element) || /^\w+$/i.test(value);
        }, "Letters, numbers, and underscores only please");
        var $registrationForm = $('#registration');
        if ($registrationForm.length) {
            $registrationForm.validate({
                rules: {
                    username: {
                        required: true,
                        alphanumeric: true
                    },
                    email: {
                        required: true,
                        customEmail: true
                    },
                    password: {
                        required: true
                    },
                    confirm: {
                        required: true,
                        equalTo: '#password'
                    },
                    fname: {
                        required: true,
                        noSpace: true
                    },
                    lname: {
                        required: true,
                        noSpace: true
                    },
                    gender: {
                        required: true
                    },
                    country: {
                        required: true
                    },
                    address: {
                        required: true
                    }
                },
                messages: {
                    username: {
                        required: 'Please enter username!'
                    },
                    email: {
                        required: 'Please enter email!',
                        email: 'Please enter valid email!'
                    },
                    password: {
                        required: 'Please enter password!'
                    },
                    confirm: {
                        required: 'Please enter confirm password!',
                        equalTo: 'Please enter same password!'
                    },
                    fname: {
                        required: 'Please enter first name!'
                    },
                    lname: {
                        required: 'Please enter last name!'
                    },
                    country: {
                        required: 'Please select country!'
                    },
                    address: {
                        required: 'Please enter address!'
                    }
                },
                errorPlacement: function (error, element) {
                    if (element.is(":radio")) {
                        error.appendTo(element.parents('.gender'));
                    }
                    else {
                        error.insertAfter(element);
                    }
    
                }
            });
        }else{
            alert("submitted");
        }
    
        $('#showbtn').click(function () {
            $('.container').fadeToggle('slow');
        });
    
    });
    </script>  
</body>
</html>

index.js Server

var express = require('express');
var app = express();
var mysql = require('mysql');

var connection = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: '',
    database: 'node_form'
});
connection.connect();
app.get('/', function (req, res) {
    res.sendFile(__dirname + "/" + "index.html");
})


app.get('/register', function (req, res) {
    var fname = req.query.fname;
            var lname = req.query.lname;
            var username = req.query.username;
            var gender = req.query.gender;
            var email = req.query.email;
            var city = req.query.city;
            var password = req.query.password;
            var address = req.query.address;
            var confirm = req.query.confirm;

    var check = "select * from registration where email = '" + email + "' or username = '" + username + "'";
    connection.query(check, function (err, result) {
        if (err) {
            throw err;
        } else if (result.length !== 0) {
            console.log("user already exist");
        }
        else {
            
            var user = {
                fname: fname,
                lname:lname,
                username:username,
                gender:gender,
                email:email,
                city:city,
                password:password,
                address:address
            };

            if (password !== confirm) {
                console.log("password and confirm password not match")
            } else {
                connection.query('insert into registration set ?', user, function (err, rs) {
                    if (err) throw err;
                    console.log('Form submitted successfully');
                    res.sendFile(__dirname + "/" + "index.html");
                })
            }
        }
    })
})

var server = app.listen(7744, function () {
    console.log("server started");
})

Upvotes: 0

saravanan
saravanan

Reputation: 11

This my js file-

var express = require("express");
var app     = express();
var path    = require("path");
var mysql = require('mysql');
var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "mydb"
});
app.get('/',function(req,res){
  res.sendFile(path.join(__dirname+'/index.html'));
});
app.post('/submit',function(req,res){

  var name=req.body.name;
  var email=req.body.email;
  var username=req.body.username;
  res.write('You sent the name "' + req.body.name+'".\n');
  res.write('You sent the email "' + req.body.email+'".\n');
  res.write('You sent the username "' + req.body.username+'".\n');

  con.connect(function(err) {
  if (err) throw err;
  var sql = "INSERT INTO form (name, email,description) VALUES ('"+name+"', '"+email+"','"+username+"')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
     res.end();
  });
  });
})
app.listen(3000);
console.log("Running at Port 3000");

This my html file-

<html>
<head>
    <title> test </title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
    <body>
        <form action="/submit" method="POST">
            <fieldset>
                <label for="name">Name: </label>
                <input type="text" id="name" name="name" autofocus />
                <br/>
                <label for="email">Email: </label>
                <input type="email" id="email" name="email"  />
                <br/>
                <label for="username">User name: </label>
                <input type="textbox" id="username" name="username" />
                <br/>
                <input type="submit" value="create profile" />
            </fieldset>
        </form>
    </body>
</html>

Upvotes: 1

user8438998
user8438998

Reputation: 1

You can try this

i am doing this with express js in node pre-requisites:install node js,mysql,express,express-generator

Write this in index.jade file

<html>
<head>
<title> test </title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>
<form action="/submitform/" method="POST">
<fieldset>
<label for="name">Name: </label>
<input type="text" id="name" name="name" autofocus />
<br/>
<label for="email">Email: </label>
<input type="email" id="email" name="email"  />
<br/>
<label for="description">Description: </label>
<textarea id="description" name="description"></textarea>
<br/>
<input type="submit" value="create profile" />
</fieldset>

</form>
</body>
</html>

Than ,write this in index.js file

var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var con = mysql.createConnection({
     host: "localhost",
     user: "root",
     password: "",
     database: "testn"
});

router.post('/submitform', function(req, res, next) {
    console.log(req.body.name);
    console.log(req.body.email);
    console.log(req.body.description);
    con.connect(function(err) {
  if (err) throw  err;
  console.log("connected");
  var sql = "INSERT INTO `form`(`name`,`email`, `description`) VALUES ('"+req.body.name+"','"+req.body.email+"','"+req.body.description+"')";
  con.query(sql, function(err, result)  {
   if(err) throw err;
   console.log("table created");
  });
});

  res.render('index', { title: 'Express' });
});


module.exports = router;

This code will insert values from form into MySql in node js

Hope this will help you

Upvotes: 0

Sureshkumar
Sureshkumar

Reputation: 41

var express = require('express');
var app = express();
var ejs = require('ejs');
var pg = require('pg');
var bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true })); 


        var conString = process.env.DATABASE_URL || "postgres://postgres:Emdsystems@localhost:5432/student";
        var client = new pg.Client(conString);
        client.connect();

app.get('/',function(req,res,next){
res.sendfile('views/forms.html');
});

app.post('/myaction', function(req, res) {

console.log('req.body');
console.log(req.body);
res.write('You sent the name "' + req.body.name+'".\n');
res.write('You sent the Email "' + req.body.email+'".\n');
res.write('You sent the City "' + req.body.city+'".\n');
res.write('You sent the Pincode "' + req.body.pincode+'".\n');
res.end()

client.query("Insert into record (name,email,city,pincode) VALUES ('"+req.body.name+"','"+req.body.email+"','"+req.body.city+"','"+req.body.pincode+"')",function(err, result)      
{                                                      
  if (err)
     throw err;
});
});
app.listen(3000);
console.log('Example app listening at port:3000');

Upvotes: 4

Jaffer Wilson
Jaffer Wilson

Reputation: 7273

try this 

mysql.query('select id, name, price from ' + TABLE + ' where price < 100',
function(err, result, fields) {
    if (err) throw err;
    else {
        console.log('Gadgets which costs less than $100');
        console.log('----------------------------------');
        for (var i in result) {
            var gadget = result[i];
            console.log(gadget.name +': '+ gadget.price);
        }
    }
});

reference: http://www.hacksparrow.com/using-mysql-with-node-js.html

Upvotes: 1

Related Questions