Broad Network


Complete code for Searching within a Site using ECMAScript and MySQL

Search Within a Site using ECMAScript and MySQL Part 3

Web Development with ECMAScript and MySQL in Node.js

Foreword: In this part of the series, I give you all the code for a simple working project.

By: Chrysanthus Date Published: 19 Oct 2016

Introduction

This is part 3 of my series, Search Within a Site using ECMAScript and MySQL. In this part of the series, I give you all the code for a simple working project. You should have read the previous parts of the series before reaching here, as this is a continuation. The complete code is for localhost.

HTML File
The code for the HTML document having the Search Box is:

<html>
    <head>
    </head>
    <body>

    <form action='http://localhost:8080/include/searchThisSite.js' method='post' style='display:inline; float:right'>
        <span id='S1' style='display:none'><b>Please Wait . . . </b></span>
        <input type='search' size='40' name='searchStr'>
        <button type='submit' onclick="document.getElementById('S1').style.display = 'inline'">Search this Site</button>
    </form>

     </body>
</html>

The searching Script
The name of the searching script file is searchThisSite.js, and it is in the c:/server/include directory. The file code is:

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

exports.searchThisSite = function(bodyArr, callback)
    {
        body = Buffer.concat(bodyArr).toString();
        body = decodeURIComponent(body);
        body = body.replace(/\+/g, " ");
        body = body.replace(/^\s*/, "");
        body = body.replace(/\s*$/g, "");

        keyValueArr = body.split('&');

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

        searchStr  = keyValueMap.get('searchStr');

        if (searchStr == "")
            {
                callback("Search string is empty!");
            }
        else
            {
                //make an array of words from search string
                searchStringWordsArr = searchStr.match(/\b\w+\b/g);

                //remove the non-keywords
                var nonKeywordsArr = ["about", "along", "among", "before", "after", "by", "for", "in", "from", "on", "of", "since", "to", "until", "till", "up", "with", "between", "the", "a", "an", "while", "whereas", "since", "as", "for", "therefore", "but", "and", "or", "I", "you", "he", "she", "we", "they", "me", "him", "her", "us", "them", "my", "your", "his", "her", "our", "their", "mine", "yours", "hers", "ours", "theirs", "some", "it", "its", "few", "many", "much", "little"];
                for (i=0; i<searchStringWordsArr.length; ++i)
                    {
                        for (j=0; j<nonKeywordsArr.length; ++j)
                            {
                                if (nonKeywordsArr[j] == searchStringWordsArr[i])
                                    {
                                        searchStringWordsArr.splice(i, 1);
                                        break;
                                    }
                            }
                    }

                //form the WHERE clause of SQL select statement
                var numberOfWords = searchStringWordsArr.length; //no. of keywords
                if (numberOfWords > 0)
                    var firstKeyword = searchStringWordsArr[0];
                var whereStr = ` WHERE (series.keywords rLike \"${firstKeyword}\")`;
                var temp;
                if (numberOfWords > 1)
                    {
                        for(k=1; k<numberOfWords; ++k)
                            {
                                temp = searchStringWordsArr[k];
                                whereStr += ` AND (series.keywords rLike \"${temp}\")`;
                            }
                    }

                //complete SQL select statement
                var selectStr = `select series.directoryName AS directoryName, series.htmlfilename AS filename, series.title1 AS title, series.forward AS foreword, readers.firstname AS firstname FROM series inner join articles inner join readers on series.articleID=articles.articleID AND articles.writerID=readers.readerID` + whereStr;

                //connect to DB and obtain resultset
                con = new mysql.Connection("user", "secret", "localhost", 3306, function(err)
                    {
                        if (err)
                            callback(err[0].get('info'));
                        else
                            {

                                var db = `broadnet_broadnet`;
                                con.selectDB(db, function(err)
                                    {
                                        if (err)
                                            callback(err[0].get('info'));
                                    });

                                con.query(selectStr, function(err, result)
                                    {
                                        if (err)
                                            {
                                                errMsg = '<h3>Error: ' + err[0].get('info') + '!<h3>';
                                                callback(errMsg);
                                            }
                                        else
                                            {
                                                if (result.length == 0)
                                                    callback(false, '<h3>No result found.</h3>');

                                                var tempStr = `<h3>Data Found</h3>`;
                                                for (i=0; i<result.length; ++i)
                                                    {
                                                        tempStr += `<p><a href=\"http://www.somesite.com/${result[i].get('directoryName')}/${result[i].get('filename')}\"><strong>${result[i].get('title')}</strong></a><br>` +
                                                        `${result[i].get('foreword')}` +`<br>` +
                                                        `<i>by: ${result[i].get('firstname')}</i></p>`;
                                                    }
                                                        
                                                 callback(false, tempStr);
                                            }
                                    });
  
                              con.close();
                        }
                    });
            }
    }

The Node Server
In this project, the web server is short. The web server file is in the directory, c:/server. The name of the web server file is, server.js. It must be running for you to test the complete code. the node server code is:

    const http = require('http');
    const fs = require('fs');
    const sts = require('./include/searchThisSite.js');

    http.createServer(function(request, response)
        {
            var url = request.url;

            if (url == '/')
                url = '/server/index.htm';
            else
              url = '/server' + url;


            response.setHeader('Content-Type', 'text/html');


            if (url.search(/.*\.htm\s*$|.*\.html\s*$/i) != -1)
                {
                     fs.readFile(url, (err, data) => {
                         if (err)
                             response.end(err.toString('utf8'));
                         else
                             {
                                 output = data.toString('utf8');
                                 response.end(output);
                             }
                    });
                }


            var body = [];

            request.on('data', function(chunk)
                {
                    body.push(chunk);      
                }).on('end', function()
                {

                    if (url.search(/include\/searchThisSite\.js/i) != -1)
                        sts.searchThisSite(body, function(err, feedback)
                            {
                                if (err)
                                    {
                                         response.end(err);
                                    }
                                 else
                                    {
                                         response.end(feedback);
                                    }
                            });

                    //images
                    if (url.search(/.*\.gif\s*$|.*\.jpg\s*$/i) != -1)
                        {
                             fs.readFile(url, (err, data) => {
                                     if (err)
                                         response.end(err);
                                     else
                                         {
                                             matchObj = url.match(/\....\s*$/);
                                             matchStr = matchObj.toString();
                                             ext = matchStr.replace(/\s*/g, '');
                                             ext = ext.replace(/\./, '');
                                             contType = 'image/' + ext;  
                                             response.setHeader('Content-Type', contType);
                                             response.end(data);
                                         }
                                });
                        }


                });


            request.on('error', function(err)
                {
                    // print error to output
                });

            response.on('error', function(err)
                {
                    //send error message to output
                });

        }).listen(8080);

The Database
The script to create the database is in the directory, c:/server/include. You run it from the concole with the command:

    node c:/server/include/createDB.js

The code is:

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

    //connect to MySQL server
    con = new mysql.Connection("root", "azemawo", "localhost", 3306, function(err)
        {
            if (err)
                console.log(err[0].get('info'));
        });

    var crtDBStr = `create database dbName`;
    con.query(crtDBStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('database created');
        });

    var db = `dbName`;
    con.selectDB(db, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('database selected');
        });

    var crtTablewStr = `create table writers
        (
            writerID int not null,
            birthDate date,
            biography varchar(10000),
            directoryPath varchar (150),
            writerAvatarImageName varchar (150),
            categoryID int,
            empID int not null,
  
            PRIMARY KEY (writerID)
         ) ENGINE = INNODB;`;
    con.query(crtTablewStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('table created');
        });

    var crtTablesStr = `create table series
        (
            articleID int not null,
            partNo int not null,
            title1 varchar(150) not null,
            title2 varchar(150),
            title3 varchar(150),
            keywords varchar(150),
            forward varchar(1000) not null,
            titleS1 varchar(150),
            titleS2 varchar(150),
            titleS3 varchar(150),
            titleS4 varchar(150),
            titleS5 varchar(150),
            titleS6 varchar(150),
            directoryName varchar(150) not null,
            htmlfilename varchar(150) not null,
            writerNumArticles int,

            primary key (articleID,partNo)
        ) ENGINE = INNODB;`;
    con.query(crtTablesStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('table created');
        });

    var crtTableaStr = `create table articles
        (
            articleID int not null auto_increment,
            writerID int not null,
            categoryID int not null,
            totalNumArticles bigint,

            primary key (articleID)
        ) ENGINE = INNODB;`;
    con.query(crtTableaStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('table created');
        });

    var crtTablerStr = `create table readers
        (
            readerID int not null auto_increment,
            registeredDate timestamp not null,
            type varchar(30) not null,
            email varchar(40) not null,
            phone varchar(12),
            title varchar(150),
            firstname varchar(20) NOT NULL,
            middlename varchar(20),
            lastname varchar(20) not null,
            password varchar(16) not null,
            company varchar(30),
            who varchar(500),
            language varchar(16),
            locationID int ,
            empID int not null,

            PRIMARY KEY (readerID)
        ) ENGINE = INNODB;`;
    con.query(crtTablerStr, function(err)
        {
            if (err)
                console.log(err);
            else
                console.log('table created');
        });

   con.close();

That is it for this part of the series.

Chrys

Related Links

Web Development Basics with ECMAScript (JavaScript) and MySQL
ECMAScript (JavaScript) Validation of HTML Form Data
Web Live Text Chart Application using ECMAScript (JavaScript) and MySQL
Page Views with Ajax and ECMAScript (JavaScript) and MySQL
Search Within a Site using ECMAScript and MySQL
More Related Links
Node Mailsend with JavaScript
EMySQL API with JavaScript
Node.js Web Development Course with JavaScript
Major in Website Design
Low Level Programming - Writing ECMAScript (JavaScript) Module
ECMAScript (JavaScript) Course

BACK

Comments

Become the Writer's Follower
Send the Writer a Message