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

How to use Ngx-Charts in Angular ?

Charts helps us to visualize large amount of data in an easy to understand and interactive way. This helps businesses to grow more by taking important decisions from the data. For example, e-commerce can have charts or reports for product sales, with various categories like product type, year, etc. In angular, we have various charting libraries to create charts.  Ngx-charts  is one of them. Check out the list of  best angular chart libraries .  In this article, we will see data visualization with ngx-charts and how to use ngx-charts in angular application ? We will see, How to install ngx-charts in angular ? Create a vertical bar chart Create a pie chart, advanced pie chart and pie chart grid Introduction ngx-charts  is an open-source and declarative charting framework for angular2+. It is maintained by  Swimlane . It is using Angular to render and animate the SVG elements with all of its binding and speed goodness and uses d3 for the excellent math functio...
Post a Comment
Read more

Understand Angular’s forRoot and forChild

  forRoot   /   forChild   is a pattern for singleton services that most of us know from routing. Routing is actually the main use case for it and as it is not commonly used outside of it, I wouldn’t be surprised if most Angular developers haven’t given it a second thought. However, as the official Angular documentation puts it: “Understanding how  forRoot()  works to make sure a service is a singleton will inform your development at a deeper level.” So let’s go. Providers & Injectors Angular comes with a dependency injection (DI) mechanism. When a component depends on a service, you don’t manually create an instance of the service. You  inject  the service and the dependency injection system takes care of providing an instance. import { Component, OnInit } from '@angular/core'; import { TestService } from 'src/app/services/test.service'; @Component({ selector: 'app-test', templateUrl: './test.component.html', styleUrls: ['./test.compon...
Post a Comment
Read more

How to solve Puppeteer TimeoutError: Navigation timeout of 30000 ms exceeded

During the automation of multiple tasks on my job and personal projects, i decided to move on  Puppeteer  instead of the old school PhantomJS. One of the most usual problems with pages that contain a lot of content, because of the ads, images etc. is the load time, an exception is thrown (specifically the TimeoutError) after a page takes more than 30000ms (30 seconds) to load totally. To solve this problem, you will have 2 options, either to increase this timeout in the configuration or remove it at all. Personally, i prefer to remove the limit as i know that the pages that i work with will end up loading someday. In this article, i'll explain you briefly 2 ways to bypass this limitation. A. Globally on the tab The option that i prefer, as i browse multiple pages in the same tab, is to remove the timeout limit on the tab that i use to browse. For example, to remove the limit you should add: await page . setDefaultNavigationTimeout ( 0 ) ;  COPY SNIPPET The setDefaultNav...
Post a Comment
Read more