Broad Network


Sending Data to MySQL Database and Email Box in Node.js

Web Development Basics with ECMAScript and MySQL – Part 7

Web Development with ECMAScript and MySQL in Node.js

Foreword: In this part of the series, I talk about the server ECMAScript script that responds to the registration (subscription) of a member.

By: Chrysanthus Date Published: 16 Jul 2016

Introduction

This is part 7 of my series, Web Development Basics with ECMAScript and MySQL. In this part of the series, I talk about the server ECMAScript script that responds to the registration (subscription) of a member. I assume you have read the previous parts of the series, before reaching here; this is a continuation. I will talk about the login script in the next part of the series.

The name of the server script is, register.js. In this project, the scripts are in the home directory (c:/server). The script connects to the database as the root: The script attempts to create the user account. If it does not succeed, it informs that user (student) that the user already exists – precisely it tells him that he should change the user info and try again. If this step is successful, it goes on to give the new user privileges, in some tables of the Bookshop database. After that it selects the Bookshop database and inserts a row of the user’s credentials in the Members table. After that it sends an email to the member’s email box, informing him that his membership request has been accepted.

In my computer, the home directory where the files are saved is, c:/server. You can create a similar directory for your project.

Note, before you can test any code, the MySQL server and the Web Server have to be on and running.

We now look at the different code segments of the ECMAScript script.

The First Code Segment
The very first code segment is:

const mysql = require('./Mysql.js');
const ms = require('./Mailsend.js');

This segment includes the MySQL API and the Mailsend modules. The rest of the code in the file is an exported function. An exported function in a module, can be seen in the file that includes the module.

The exports.register Function Skeleton
The skeleton for the register() function that is exported, is:

exports.register = function(bodyBytesArr, callback)
    {
        //convert received bytes of HTML Form dataset of name/value pairs into a string.

        //place the name/value pairs into a map of key/value pairs.

        //assign values of map to variables

        //validate the variables (values)

        //send Dataset to database and email to subscriber.

    });

The comments in the skeleton explain what each segment does. The rest of the segments explained below, are inside the register function. The register function is called by the node server program file.

The Second Code Segment
It is:

        body = Buffer.concat(bodyBytesBuf).toString();
        body = decodeURIComponent(body);
        body = body.replace(/\+/g, " ");

This code segment converts the bytes of bodyBytesBuf into a usable string in body.

The Third Code Segment
It is:

       keyValueArr = body.split('&');

        keyValueMap = new Map();
        for (item of keyValueArr )
         {
                arr = item.split('=');
                keyValueMap.set(arr[0], arr[1]);
         }

This code segment places the name/value pairs of body into a map of key/value pairs.

The Fourth Code Segment
It is:

        firstname = keyValueMap.get('firstname');
        middlename = keyValueMap.get('middlename');
        lastname = keyValueMap.get('lastname');
        email = keyValueMap.get('email');
        phone = keyValueMap.get('tel');
        university = keyValueMap.get('university');
        address = keyValueMap.get('address');
        city = keyValueMap.get('city');
        State = keyValueMap.get('state');
        country = keyValueMap.get('country');
        username = keyValueMap.get('username');
        password = keyValueMap.get('password');

This segment assigns values of key/value pairs from the map to variables (names).

The Fifth Code Segment
It is”
        //simple validation
        state = true;
        if (firstname == "")
         state = false;
        if (middlename == "")
         state = false;
        if (lastname == "")
         state = false;
        if (email == "")
         state = false;
        if (phone == "")
         state = false;
        if (university == "")
         state = false;
        if (address == "")
         state = false;
        if (city == "")
         state = false;
        if (State == "")
         state = false;
        if (country == "")
         state = false;
        if (username == "")
         state = false;
        if (password == "")
         state = false;

This code segment does simple validation, checking whether each value from the Form dataset is present or not, ignoring whether it is present and incorrect. If all values are present, then the value of the state variable is true.

The Sixth Code Segment
If the state value is false, meaning one or more of the values from the Form dataset of the web page is (are) absent, the sixth code segment calls the callback function defined in the node server program file, indicating so, and the register() function ends. Otherwise, it prepares the data (registration) for the database and sends. If the data to the database is recorded successfully, then it sends an email to the user (student).

The code segment is:

        var feedback;

        //Error feedback or insert in database and send mail
        if (state == false)
            {
                feedback = "One or more form fields was/were not filled";
                callback(feedback);
            }
        else
            {
                //prepare and send data to the database
                con = new mysql.Connection("root", "secret", "localhost", 3306, function (err)
                    {
                        if (err)
                            {
                                feedback = "Error: Connection to database could not be made. See web master";
                                callback(feedback);
                                return;
                            }
                    });

                //select database
                con.query('USE Bookshop', function(err)
                    {
                        if (err)
                            {
                                feedback = ":Error: Database could not be selected. See web master.";
                                callback(feedback);
                                return;
                            }
                    });

                 //create user
                 var createUserStr = `CREATE USER '${username}' IDENTIFIED BY '${password}'`;  
                 con.query(createUserStr, function(err)
                     {
                         if (err)
                             {
                                 feedback = "User name already taken; change name";
                                 callback(feedback);
                                 return;
                             }
                     });

                 //give privileges
                 var grantStr1 = `GRANT INSERT, SELECT ON Bookshop.Members TO ${username}`;
                 var grantStr2 = `GRANT SELECT ON Bookshop.Products TO ${username}`;
                 var grantStr3 = `GRANT INSERT, SELECT ON Bookshop.Sales TO ${username}`;
                 var grantStr4 = `GRANT INSERT ON Bookshop.SaleItems TO ${username}`;
                 con.query(grantStr1, function(err)
                     {
                         if (err)
                             {
                                 feedback = err;
                                 callback(feedback);
                                 return;
                             }
                     });
                 con.query(grantStr2, function(err)
                     {
                         if (err)
                             {
                                 feedback = err;
                                 callback(feedback);
                                 return;
                             }
                     });
                 con.query(grantStr2, function(err)
                     {
                         if (err)
                             {
                                 feedback = err;
                                 callback(feedback);
                                 return;
                             }
                     });

                 con.query(grantStr4, function(err)
                     {
                         if (err)
                             {
                                 feedback = err;
                                 callback(feedback);
                                 return;
                             }
                     });


                    var credentialsStr = `insert into Members (username, email, phone, firstname, middlename, lastname, university, address, city, state, country) values ('${username}', '${email}', '${phone}', '${firstname}', '${middlename}', '${lastname}', '${university}', '${address}', '${city}', '${state}', '${country}')`;
                    con.query(credentialsStr, function(err)
                        {
                            if (err)
                                {
                                    feedback = "Credentials could not be inserted in datanase. See web master";
                                    callback(feedback);
                                    return;
                                }
                         });


                    //prepare and send mail
                    //if feedback is still undefined, then no error has occured above.
                    //form body string
                    if (feedback == undefined)
                        {
                            bodyStr = firstname + '\n' + phone + '\n' + 'Your application for membership has neem accepted.';
                            ms.setMAILFROM ('<' + email + '>');
                            ms.setRecipientsArr(['<' + email + '>']);
                            ms.setFrom ('<' + email + '>');
                            ms.setSubject('Registration');
                            ms.setBody(bodyStr);
                            ms.sendmail(function(err, feedback)
                                {
                                    if (err)
                                        {
                                            error = err;
                                            callback(error);
                                            con.close();
                                        }
                                     else
                                        {
                                            feedback = "Message has been sent.";
                                            callback(null, feedback);
                                            con.close();
                                        }
                                 });
                        }

            }


The register() function is called by the client, when he fills in the Form data at the web page and clicks Submit (Send). The register() function is at the server.

You will be able to download all the code in this series at the end of the series.

As I said in one of the previous parts of the series, all the HTML and ECMAScript scripts are in the home directory of the web server. To test the log_reg.htm and register.js files you will type the following at the address bar of your browser (the node server has to be on):

    http://localhost:8080/log_reg.htm

When the web page opens, you will see the lower Form titled, Register; fill the form and then click, Send. The email will be sent if you are hooked to the Internet.

Note: in practice, the script will have to login with some non-root account that has some root privileges, for security.

That is it for this part of the series. We take a break here and continue in the next part.

Chrys

Related Links

Web Development Basics with ECMAScript and MySQL
ECMAScript Validation of HTML Form Data
Web Live Text Chart Application using ECMAScript and MySQL
More Related Links
Node Mailsend
EMySQL API
Node.js Web Development Course
Major in Website Design
Low Level Programming - Writing ECMAScript Module
ECMAScript Course

BACK NEXT

Comments

Become the Writer's Follower
Send the Writer a Message