How to Upload Image to MySQL Database in Node.js Express Using FileUpload and Display it Using EJS Full Tutorial

How to Upload Image to MySQL Database in Node.js Express Using FileUpload and Display it Using EJS Full Tutorial
x
How to Upload Image to MySQL Database in Node.js Express Using FileUpload and Display it Using EJS

Welcome folk today in this tutorial we will be looking how to upload images to MySQL database using node JS using the is available in node JS so we will interact with the node JS application and we will be uploading images to MySQL database and we will also be displaying those images inside the application and we will be displaying the images from the my sequel database and then displaying it inside the application all the source code of this application will be given in the description you can copy the source code and step-by-step YouTube video shown below.

 

Screenshots

 

 

Requirements

 

 

Now create a package for JSON file inside your root directory of your project and copy the following code

{
  "name": "backend",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node index.js",
    "dev": "nodemon index.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.16.1",
    "ejs": "^2.5.6",
    "express": "^4.14.1",
    "express-fileupload": "^0.1.1",
    "mysql": "^2.13.0"
  },
  "devDependencies": {
    "nodemon": "^2.0.4"
  }
}

 

Now create a separate folder for the uploaded images. Create a public folder which will be static and inside it create another folder images and inside it create another folder called as uploaded_images

 

Now we will create the starting point of the application which is the index for JS file here is a sample Express app which will be starting on port will provide and also we will be initialising some of the middleware that we have installed and the file upload library

 

 

var express = require('express')
  , routes = require('./routes')
  , path = require('path'),
    fileUpload = require('express-fileupload'),
    app = express(),
    mysql      = require('mysql'),
    bodyParser=require("body-parser");
    
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : '###yourdatabasename###'
});
 
connection.connect();
 
global.db = connection;
 
// all environments
app.set('port', process.env.PORT || 8080);
app.set('views', __dirname + '/views');
app.set('view engine', 'ejs');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));
app.use(fileUpload());
 
// development only
 
app.get('/', routes.index);//call for main index page
app.post('/', routes.index);//call for signup post 
app.get('/profile/:id',routes.profile);
//Middleware
app.listen(8080)

 

In this we have created sample Express app and we have started this Express up at Port 8080 you can take any port number of your choice and also we have initialised a database connection name for which the database that you have created you just need to replace the database in here and we have connected to the database and in the next step we will create table programmatically inside mysql environment

See also  Build a HTML5 Birthday Reminder or Notification Web App in Browser Using Vanilla Javascript Full Project For Beginners

Now in the next step we will create a my sequel table so what you need to do is that you need to go to the white sequin environment and here you need to copy paste mysql query which I am giving you right below

CREATE TABLE IF NOT EXISTS `users_image` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `mob_no` int(11) NOT NULL,
  `user_name` varchar(20) NOT NULL,
  `password` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

So as you can see inside the structure of the table there are 6 fields inside the table first is the ID which is the primary key of the table and then we have our first name and last name then we have also the profile picture of the person which were registering at that we have to mobile number and then we have is the username and password

Now create a simple views file called as index.ejs which will hold a simple form where we will get the input from the user

<!DOCTYPE html>
<html>
  <head>
    <title>Currency Converter in Javascript</title>
    <link
      rel="stylesheet"
      href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    />
  </head>
  <body>
    <div class="container col-sm-12" id="mainform">
        <div id="signupbox" style=" margin-top:50px" class="mainbox col-md-6 col-md-offset-3 col-sm-8 col-sm-offset-2">                                     
            <div class="panel panel-info">
                <div class="panel-heading">
                    <div class="panel-title">Register</div>
                </div> 
                <div class="panel-body" >
                    <form class="form-horizontal" role="form" method="post" action="/" enctype="multipart/form-data">
                        <% if (message.length > 0) { %>
                            <div class="alert alert-success col-sm-12"><%= message %></div>
                        <% } %>
                        <div id="signupalert" style="display:none" class="alert alert-danger">
                            <p>Error:</p>
                            <span></span>
                        </div>
                        <div class="form-group">
                            <label for="first_name" class="col-md-3 control-label">First Name</label>
                            <div class="col-md-9">
                                <input type="text" class="form-control" name="first_name" placeholder="First Name">
                            </div>
                        </div>
                        <div class="form-group">
                            <label for="last_name" class="col-md-3 control-label">Last Name</label>
                            <div class="col-md-9">
                                <input type="text" class="form-control" name="last_name" placeholder="Last Name">
                            </div>
                        </div>
                                                                                        <div class="form-group">
                            <label for="mob_no" class="col-md-3 control-label">Mobile Number</label>
                            <div class="col-md-9">
                                <input type="number" class="form-control" name="mob_no" placeholder="Mobile Number">
                            </div>
                        </div>
                        <div class="form-group">
                            <label for="mob_no" class="col-md-3 control-label">Profile Image</label>
                            <div class="col-sm-9">
                                <input class="form-control" type="file" name="uploaded_image" accept=""/>
                            </div>
                        </div>                                
                        <div class="form-group">
                            <label for="user_name" class="col-md-3 control-label">User Name</label>
                            <div class="col-md-9">
                                <input type="text" class="form-control" name="user_name" placeholder="User Name">
                            </div>
                        </div>
                        <div class="form-group">
                            <label for="password" class="col-md-3 control-label">Password</label>
                            <div class="col-md-9">
                                <input type="password" class="form-control" name="password" placeholder="Password">
                            </div>
                        </div>
                        <div class="form-group">
                            <!-- Button -->                                       
                            <div class="col-md-offset-3 col-md-9">
                                <button id="btn-signup" type="submit" class="btn btn-info"><i class="icon-hand-right"></i> &nbsp Register</button>
                            </div>
                        </div>  
                    </form>
                 </div>
            </div>
        </div>
    </div>
</body>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</html>

 

See also  jQuery + jsPDF PDF Certificate Generator From HTML Using html2canvas.js Library in Browser Using Javascript Full Project For Beginners

Now create a new file called as routes.js inside this file we will write the business logic of the application so whenever user loads the app for the very first time then we will show a ejs template and when it submits the form then we will make a post request to the home route.

 

exports.index = function(req, res){
    message = '';
   if(req.method == "POST"){
      var post  = req.body;
      var name= post.user_name;
      var pass= post.password;
      var fname= post.first_name;
      var lname= post.last_name;
      var mob= post.mob_no;
 
      if (!req.files)
                return res.status(400).send('No files were uploaded.');
 
        var file = req.files.uploaded_image;
        var img_name=file.name;
 
         if(file.mimetype == "image/jpeg" ||file.mimetype == "image/png"||file.mimetype == "image/gif" ){
                                 
              file.mv('public/images/upload_images/'+file.name, function(err) {
                             
                  if (err)
 
                    return res.status(500).send(err);
                        var sql = "INSERT INTO `users_image`(`first_name`,`last_name`,`mob_no`,`user_name`, `password` ,`image`) VALUES ('" + fname + "','" + lname + "','" + mob + "','" + name + "','" + pass + "','" + img_name + "')";
 
                            var query = db.query(sql, function(err, result) {
                                 res.redirect('profile/'+result.insertId);
                            });
                       });
          } else {
            message = "This format is not allowed , please upload file with '.png','.gif','.jpg'";
            res.render('index.ejs',{message: message});
          }
   } else {
      res.render('index');
   }
 
};

 

Display Uploaded Image Using Unique ID

 

Now we will display the uploaded image for that we will use the primary key of the table. The id of the record we will take it and make a simple request to the mysql table. Inside our routes.js file copy paste this code below it.

exports.profile = function(req, res){
    var message = '';
    var id = req.params.id;
    var sql="SELECT * FROM `users_image` WHERE `id`='"+id+"'"; 
    db.query(sql, function(err, result){
      if(result.length <= 0)
      message = "Profile not found!";
      
      res.render('profile.ejs',{data:result, message: message});
   });
};

Now create a profile.ejs file and write the following code to see the uploaded image inside the browser

<!DOCTYPE html>
<html>
  <head>
    <title>Currency Converter in Javascript</title>
    <link
      rel="stylesheet"
      href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
    />
  </head>
  <body>
    <div class="row">
        <div class="col-xs-12 col-sm-12 col-md-6 col-lg-6 col-xs-offset-0 col-sm-offset-0 col-md-offset-3 col-lg-offset-3 toppad" style="padding-top:30px">
          <% if (message.length > 0) { %>
            <div class="alert alert-danger col-sm-12" ><%= message %></div>
          <% } else {%>
            <div class="panel panel-info">
              <div class="panel-heading">
                <h3 class="panel-title"><%=data[0].first_name%>, <%=data[0].last_name%></h3>
              </div>
              <div class="panel-body">
                <div class="row">
                  <div class="col-md-3 col-lg-3 " align="center"> <img alt="User Pic" src="http://localhost:8080/images/upload_images/<%=data[0].image%>" class="img-circle img-responsive"> </div>
                  <div class=" col-md-9 col-lg-9 "> 
                    <table class="table table-user-information">
                      <tbody>
                        <tr>
                          <td>User Name:</td>
                          <td><%=data[0].user_name%></td>
                        </tr>
                        <tr>
                          <td>Mobile:</td>
                          <td><%=data[0].mob_no%></td>
                        </tr>
                      </tbody>
                    </table>
                  </div>
                </div>
              </div>
            </div>
          <% } %>
          </div>
        </div>
  
</body>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</html>

 

See also  GridStack.js HTML2PDF.js Example to Convert Grid Stack Widgets to PDF Document in HTML5 & Javascript Full Project For Beginners

DOWNLOAD SOURCE CODE

Leave a Reply