Broad Network


Coding for Search within a Site using ECMAScript and MySQL

Search Within a Site using ECMAScript and MySQL – Part 2

Web Development with ECMAScript and MySQL in Node.js

Foreword: In this part of the series I explain the code of the ECMAScript file used in searching a site with MySQL.

By: Chrysanthus Date Published: 19 Oct 2016

Introduction

This is part 2 of my series, Search Within a Site using ECMAScript and MySQL. In this part of the series I explain the code of the ECMAScript file used in searching a site with MySQL. The code is my design. I gave you an overview of this topic in the previous part of the series. You should be reading the parts of this series in the order given; this is a continuation of the previous part. The interface code and the database query are in one ECMAScript server file. I gave you the client web page Form code in the previous part of the series and that will not be repeated here.

The ECMAScript File Segments
There are three major code segments in the file. The first segment obtains the search phrase sent from the client. Remember, the ECMAScript file is at the server, in the directory, c/server/include. The next major segment, removes the non-keywords from the search phrase, places the keywords in an ECMAScript array and then prepares the SQL query in string form. The third major segment connects to the database, sends the SQL query to the database, receives the result from the database and then prepares the HTML code for the result and send to the client’s browser. The result is the feedback seen by the client. The second and third code segments are further divided into smaller segments.

The First Code Segment
The first major code segment is quite short. It 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');

It gets the search phrase from the HTTP POST variable and assigns to the ordinary ECMAScript variable, searchStr. I will explain the details of this code later. Also note that we are using the Pure ECMAScript MySQL API that is easy to use and easy to install.

The Second Code Segment
The skeleton of the second major code segment is:

    if (searchStr == "")
        {
            callback("Search string is empty!");
        }
    else
        {
            - - - - - -
        }

The if-condition checks if the search string was empty. If it was, the if-block informs the user so. Under this condition, the SQL Select query is not effectively form and no further information is developed and sent to the user. The algorithm here does not do that.

If the search string is not empty, the else part of the if-construct is executed. This else part consists of smaller code segments. The first small code segment here forms an array of all the words in the search string. The second small segment here, removes all the non-keywords from the array. The third small segment, forms the WHERE clause for the database SQL query. The fourth and last small code segment produces the complete SQL query string in ECMAScript terms.

The first two small code segments are:

                //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;
                                    }
                            }
                    }

The third small code segment is:

                //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}\")`;
                            }
                    }

It begins by obtaining the number of elements in the search phrase array and assigning it to the variable, numberOfWords. The first keyword of the array is then assigned to the variable, firstKeyword. The first part of the WHERE clause is determined and assigned to the variable, whereStr.

If there are more than one keyword in the search array, then the second if-construct builds up the rest of the WHERE clause, otherwise the WHERE clause would have only one keyword. The for-loop is what actually builds the rest of the WHERE clause and string-append to the variable, whereStr. In the last statement of the for-loop, the variable temp is used.

The last small code segment is:

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

This statement assigns the complete database SQL query in ECMAScript terms, to the variable, selectStr. It string-appends the complete WHERE clause to the first part of the database SQL query.

The Database SQL Query
An example of the MySQL Select Query without ECMAScript, for this project is:

select series.directoryName AS directoryName, series.htmlfilename AS filename, series.title1 AS title, series.forward AS forward, writers.firstname AS firstname FROM series inner join articles inner join writers on series.articleID=articles.articleID AND articles.writerID=writers.writerID WHERE (series.keywords rLike "relationship") AND (series.keywords rLike "man") AND (series.keywords rLike "woman");

This Select query looks into the database for article data (rows) of articles that are based on “relationship between man and woman”. This quotation is the search phrase the user would type. The ECMAScript file would remove the preposition, “between”, leaving you with an array consisting of the keywords, “relationship”, “man” and “woman”.

You should be able to understand the part of the Select query before the WHERE clause. The WHERE clause is:

    WHERE (series.keywords rLike "relationship") AND (series.keywords rLike "man") AND (series.keywords rLike "woman");

So, for each cell in the keywords column of the series table in the database, the query checks if “relationship” is present and if “man” is present and if “woman” is present. In the statement, “rLike” is of MySQL regular expression technique. The two same Boolean logic operators of “AND” are of SQL. Because of the ANDs, all three keywords, "relationship",  "man" and "woman" must be present in the cell. If any of these words is absent, the row is not selected. Remember, a cell of the keywords column of the series table, has article keywords separated by commas.

So, the more keywords the user has in his search phrase, the less the number of relevant rows selected.

I hope at this point, you can go back to the third small code segment above and understand how the WHERE clause is produced. Note, the user can type more than three keywords or less than three keywords. Above, there is an array that holds the keywords typed. The number of keywords in the search phrase determines the array length and so the length (number of ANDs) of the WHERE clause.

The Third Code Segment
The third major code segment of the ECMAScript file is:

                //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();
                        }
                    });
            }
    }

This segment begins by attempting the connection. If that fails it sends an error message to the user and no database search is done. The else part of the if-construct here is what does the search. At the end of the segment, if no rows match the search phrase (keywords) the user is told so. At the end of the segment, the connection is closed.

Note, the feedback page sent to the client’s browser is formed by the ECMAScript file.

Regular Expression Techniques used
The ECMAScript file uses regular expression technique in two places. In the first place, an operation is used to copy each word of the search phrase to an array. In the second place, ECMAScript “wraps” a MySQL regular expression; this happens at the formation of the MySQL Select Query.

Database to be created
As you can see from this and the previous part of the series, the database has to be created. For each HTML article, rows must be inserted into the series, articles, and writers tables. So, you or someone else has to create the database.

Wow, impressive, and not as difficult as it seemed, pre-knowledge haven accomplished! Time to take a break. See you in the next 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 NEXT

Comments

Become the Writer's Follower
Send the Writer a Message