Updating values in mysql database using nodejs

Image source: Pixabay

In today’s tutorial we are going to dynamically update mysql table values from nodejs server.In previous tutorials covered following topics:

a)Simple login interface: Tutorial

b)File upload module: Tutorial

c)Admin dashboard for monitoring user logins: Tutorial

In this tutorial we are going to update the code in file upload module to keep a track of number of files uploaded by the user at any given time.First we are going to add a new column in our collegeusers from mysql shell to keep a track of uploaded files.

Drop into mysql shell by running following commands from terminal

mysql -u root -p
//enter your mysql root password then select appropriate 
//database using following command
use cloudprint
//Next add a new column using Alter table
//command in mysql
ALTER TABLE collegeusers ADD COLUMN printCount INT NOT NULL;

Now if you open your admin/dockerdashboard in your browser and go to mysql monitoring you will see a printCount column as follows:

printCount

Next we need to fileroutes.js file in backend folder of cloudprint/fileupload module and modify it as follows:

First we need to add new mysql connection at the top of the file as follows:

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root@123',
  database: 'cloudprint',
  insecureAuth: false
});
connection.connect(function (err) {
  if (!err) {
    console.log("Database is connected ... nn");
  } else {
    console.log("Error connecting database ... nn", err);
  }
});

Next we need to modify exports.fileprint function as follows:

exports.fileprint = function (req, res) {
  // console.log("req",req.files);
  var filesArray = req.files;
  var filepath = './userdata/userid.json'
  jsonfile.readFile(filepath, function (err, obj) {
    var userid = obj.userid;
    connection.query('SELECT * FROM collegeusers WHERE userid = ?', [userid], function (error, results, fields) {
      if (error) {
        console.log("error ocurred", error);
        // res.send({
        //   "code":400,
        //   "failed":"error ocurred"
        // })
      } else {
        if (results.length > 0) {
          let printCount = results[0].printCount + filesArray.length;
          connection.query('UPDATE collegeusers SET printCount = ? WHERE userid = ?', [printCount, userid], function (error, results, fields) {
            if (error) {
              console.log("error", error);
            }
          });
        }
      }
    });
  });
  async.each(filesArray, function (file, eachcallback) {
    async.waterfall([
....

We are first gettting userid from file using jsonfile.readFile function, then we run select mysql query to get userdetails of that userid from mysql database. We get the user details paramter in “results” parameter.

Next we create printCount variable in which we get previous upload/print count of the user and add filescount to it.Now we need to save new printCount to database so we run UPDATE mysql query and update printcount where userid matches the currently logged in user.

You can see the complete source code for the file here:Github

Here is the screenshot of updated admin dashboard once files are uploaded:

printCount Updated

That’s it for today’s short tutorial. You can check out the repos for file upload/cloudstorage here: Github and admindashboard here: Github

Bonus Tip:

In this project we have made connection to database twice in two different files which should be generally avoided in production level projects.

Connect Deeper:

In the upcoming tutorial I will cover adding graphql middleware to my existing project and how to use apollo grapql client to get data from remote graphql server. In order to get notified you can follow us on Facebook: Technoetics