Most Popular Tutorials




How to fetch Data from MySQL database table in Node.js?

create node.js connection to mySql Database

First of all, you have to create a connection node.js app to the MySQL database.

File Name : database.js

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

Create a Route for Fetch Data

Include the database.js file for connection.
Create a route /user-list to fetch data from the users table
Write a SQL query to fetch data from the database.
Pass the fetched data into the view file in the form of userData
finally export router.

File Name : users.js

var express = require('express');
var router = express.Router();
var db=require('../database');
router.get('/user-list', function(req, res, next) {
var sql='SELECT * FROM users';
db.query(sql, function (err, data, fields) {
if (err) throw err;
res.render('user-list', { title: 'User List', userData: data});
});
});
module.exports = router;

Load Route into the root file

You have to include the created route in the app.js.

File Name : app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
;
var app = express();

// view engine setup
app.use('/', indexRouter);
app.use('/users', usersRouter);



app.set('views', path.join(__dirname, 'views'));

app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};

// render the error page
res.status(err.status || 500);
res.render('error');
});

module.exports = app;

Create Html view

Create HTML view to Display data

File Name : user-list.ejs

<!DOCTYPE html>
<html lang="en">
<head>
<title>how to Fetch data using MySQL and Node.js</title>
</head>
<body>
<div class="table-data">
<h2>Display Data in html table using Node.js & MySQL</h2>
<table border="1">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Email Address</th>
<th>City</th>
<th>Country</th>
<th>Edit</th>
<th>Delete</th>
</tr>

<%
if(userData.length!=0){
var i=1;
userData.forEach(function(data){
%>
<tr>
<td><%=i; %></td>
<td><%=data.fullName %></td>
<td><%=data.emailAddress %></td>
<td><%=data.city %></td>
<td><%=data.country %></td>
<td><a href="/users/edit/<%=data.id%>">Edit</a></td>
<td><a href="/users/delete/<%=data.id%>">Delete</a></td>
</tr>
<% i++; }) %>
<% } else{ %>
<tr>
<td colspan="7">No Data Found</td>
</tr>
<% } %>
</table>
</div>
</body>
</html>

Run Application

File Name :

Start your Node.js server using npm start.

Test On Browser

File Name :

http://localhost:3000/users/user-list





Previous Next


Trending Tutorials


PHP

PHP

100+ Tutorials
oops

PHP OOPs

80+ Tutorial
Codeigniter

Codeigniter

100+ Tutorial
Laravel

Laravel

100+ Tutorials
Node.js

NODE.JS

100+ Tutorials
Javascript

Javascript

100+ Tutorials


Review & Rating

0.0 / 5

0 Review

5
(0)

4
(0)

3
(0)

2
(0)

1
(0)

Write Review Here


Most Popular Tutorials

 Important Link

Ittutorial