Skip to main content

Node.js MySQL Update – How to Update Data Using Node.js and MySQL

Node.js MySQL Update Query with Example

Before starting the coding, You must set up the following things
myapp/
  |__bin
  |__node_modules
  |__public
  |__routes/
  |    |__index.js
  |    |__user.js
  |__views/
  |    |__index.ejs
  |    |__user.ejs
  |    |__user-list.ejs
  |__app.js
  |__database.js
  |__package-lock.json
  |__package.json

  • 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;

Write SQL Query in Node.js to Edit Data

Use the following script in the routes/user.js file. This script will help you to fetch data from the users table and display it in HTML form.
  • router.get('/edit/:id', function(req, res, next) {
  • var UserId= req.params.id;
  • var sql=`SELECT * FROM users WHERE id=${UserId}`;
  • db.query(sql, function (err, data) {
  • if (err) throw err;
  • res.render('user', { title: 'User List', editData: data[0]});
  • });
  • });

Write SQL Query in Node.js to Update Data

You can use the following script in the routes/user.jsfile. This script will help you to update data of  users table and display it in HTML form.
  • router.post('/edit/:id', function(req, res, next) {
  • var id= req.params.id;
  • var updateData=req.body;
  • var sql = `UPDATE users SET ? WHERE id= ?`;
  • db.query(sql, [updateData, id], function (err, data) {
  • if (err) throw err;
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • res.redirect('/user/user-list');
  • });

Fetch Edit Data and display in HTML Form

Use the following code in the views/user.ejs file. This code will help you edit and delete data by the HTML form.
  • <!--====form section start====-->
  • <div class="user-detail">
  • <form action="<%=(typeof editData!='undefined')?'/user/edit/'+editData.id:'/user/create'%>" method="POST">
  • <label>Full Name</label>
  • <input type="text" placeholder="Enter Full Name" name="fullName" required value="<%=(typeof editData!='undefined')? editData.fullName:''%>">
  • <label>Email Address</label>
  • <input type="email" placeholder="Enter Email Address" name="emailAddress" required value="<%=(typeof editData!='undefined')? editData.emailAddress:''%>">
  • <label>City</label>
  • <input type="city" placeholder="Enter Full City" name="city" required value="<%=(typeof editData!='undefined')? editData.city:''%>">
  • <label>Country</label>
  • <input type="text" placeholder="Enter Full Country" name="country" required value="<%=(typeof editData!='undefined')? editData.country:''%>">
  • <button type="submit">Submit</button>
  • </div>
  • </div>
  • <!--====form section start====-->

Complete Script to update Data using Node.js and MySQL

Include the database connection file database.js in the routes/user.js file.
  • var db=require('../database');
You can the following  complete script in routes/user.js file.
Now, Include routes/user.js file in the root file app.js
  • var userRouter = require('./routes/user');
  • app.use('/user',userRouter);
File Name: routes/user.js
  • var express = require('express');
  • var router = express.Router();
  • var db=require('../database');
  • // write here create & display data script
  • router.get('/edit/:id', function(req, res, next) {
  • var UserId= req.params.id;
  • var sql=`SELECT * FROM users WHERE id=${UserId}`;
  • db.query(sql, function (err, data) {
  • if (err) throw err;
  • res.render('user', { title: 'User List', editData: data[0]});
  • });
  • });
  • router.post('/edit/:id', function(req, res, next) {
  • var id= req.params.id;
  • var updateData=req.body;
  • var sql = `UPDATE users SET ? WHERE id= ?`;
  • db.query(sql, [updateData, id], function (err, data) {
  • if (err) throw err;
  • console.log(data.affectedRows + " record(s) updated");
  • });
  • res.redirect('/user/user-list');
  • });
  • module.exports = router;

Comments

Post a Comment

Popular posts from this blog

4 Ways to Communicate Across Browser Tabs in Realtime

1. Local Storage Events You might have already used LocalStorage, which is accessible across Tabs within the same application origin. But do you know that it also supports events? You can use this feature to communicate across Browser Tabs, where other Tabs will receive the event once the storage is updated. For example, let’s say in one Tab, we execute the following JavaScript code. window.localStorage.setItem("loggedIn", "true"); The other Tabs which listen to the event will receive it, as shown below. window.addEventListener('storage', (event) => { if (event.storageArea != localStorage) return; if (event.key === 'loggedIn') { // Do something with event.newValue } }); 2. Broadcast Channel API The Broadcast Channel API allows communication between Tabs, Windows, Frames, Iframes, and  Web Workers . One Tab can create and post to a channel as follows. const channel = new BroadcastChannel('app-data'); channel.postMessage(data); And oth...
Post a Comment
Read more

Certbot SSL configuration in ubuntu

  Introduction Let’s Encrypt is a Certificate Authority (CA) that provides an easy way to obtain and install free  TLS/SSL certificates , thereby enabling encrypted HTTPS on web servers. It simplifies the process by providing a software client, Certbot, that attempts to automate most (if not all) of the required steps. Currently, the entire process of obtaining and installing a certificate is fully automated on both Apache and Nginx. In this tutorial, you will use Certbot to obtain a free SSL certificate for Apache on Ubuntu 18.04 and set up your certificate to renew automatically. This tutorial will use a separate Apache virtual host file instead of the default configuration file.  We recommend  creating new Apache virtual host files for each domain because it helps to avoid common mistakes and maintains the default files as a fallback configuration. Prerequisites To follow this tutorial, you will need: One Ubuntu 18.04 server set up by following this  initial ...
Post a Comment
Read more

Working with Node.js streams

  Introduction Streams are one of the major features that most Node.js applications rely on, especially when handling HTTP requests, reading/writing files, and making socket communications. Streams are very predictable since we can always expect data, error, and end events when using streams. This article will teach Node developers how to use streams to efficiently handle large amounts of data. This is a typical real-world challenge faced by Node developers when they have to deal with a large data source, and it may not be feasible to process this data all at once. This article will cover the following topics: Types of streams When to adopt Node.js streams Batching Composing streams in Node.js Transforming data with transform streams Piping streams Error handling Node.js streams Types of streams The following are four main types of streams in Node.js: Readable streams: The readable stream is responsible for reading data from a source file Writable streams: The writable stream is re...
Post a Comment
Read more