Build a work-tracking process with MySQL

To understand how MySQL is used in node, let's look at a program that requires an RDBMS.
Suppose you want to create a Web program that records how you spend your workday. This requires recording the date of the work, the time spent on the work, and the description of the completion of the work.

1. System Analysis 1.1 System process

• This program will have a form to enter the details of the work,
  
  ?
• When the job information is entered, it can be archived or deleted so that it no longer appears above the input fields used to enter more work.
• Click on the "archived work" link to display all previously archived items.
  
  ?

1.2 System Tasks

• Creating program Logic
• Create the auxiliary functions required for the program to work
• Write a function that allows you to add, delete, update, and retrieve data with MySQL
• Write code that renders HTML records and forms

1.3 Using Modules

This program uses node's built-in HTTP module to implement Web server functionality, interacting with a MySQL server with a third-party module. A custom module called Timetrack, which is a program-specific function used to store, modify, and retrieve data in MySQL. Figure 5-4 is an overview of this program.
Install this popular MySQL node module with this command first:

install mysql

?

1.4 Final Effect

As shown in final result 5-5, a simple Web program that can be used to record the work done, as well as review, archive, and delete working records.

?

2. Creating a program's logic

The

Next requires the creation of two file-store program logic. This two files are: Timetrack_server.js, used to start the program, Timetrack.js, contains the program-related functions of the module.
Create the Timetrack_server.js first and put the code in listing 5-7 inside. This code contains node's HTTPAPI, program-specific logic, and the MySQL API. Fill in the settings for host, user, and password according to your MySQL configuration.

varrequire‘http‘ ) ;varrequire‘./lib/timetrack‘ ) ;varrequire‘mysql‘ ) ;var db = mysql.createConnection( {    ‘127.0.0.1‘,    ‘root‘,    ‘root‘,    ‘timetrack‘} ) ;

Next, add the logic in Listing 5-8 to define the behavior of the Web program. Use this program to browse, add, and delete work execution records. You can also archive work records. Archived work records no longer appear on the main page, but can also be browsed on a separate web page.

varServer = Http.createserver ( function (req, res) {    Switch(Req.method) { Case ' POST ': {Switch(Req.url) { Case '/': {work.add (db, req, res); Break; } Case '/archive ': {work.archive (db, req, res); Break; } Case '/delete ': {work.delete (db, req, res); Break; }            } Break; } Case ' GET ': {Switch(Req.url) { Case '/': {work.show (db, res); Break; } Case '/archived ': {work.showarchived (db, res); Break; }            } Break; }    }} ) ;

Code Listing 5-9 is the last piece of code in Timetrack_server.js. This code creates a database table (if it does not exist), initiates an HTTP server, and listens to Port 3000 on this machine. All node-mysql queries are executed with the query function.

db.query ( ' Create Table if not EXISTS work ('  +  ' ID int (TEN) NOT null auto_increment, '  + 
    
      ' hours decimal (5, 2) default 0, '  + 
      ' date date, '  + 
      ' archived int (1) default 0, '  +  ' description longtext, '  +  "PRIMARY key (ID)) ' , fu Nction   { if  (err) throw  err;        Console.log ( "Server started ... ' );    Server.listen (3000 ,  ' 127.0.0.1 ' ); }) ;
    

3. Create an auxiliary function to send HTML, create a form, receive form data

The startup program's files have been completed, which creates a file that defines the other functions of the program. Create a directory named Lib, and then create the file Timetrack.js in this directory. Put the code in listing 5-10 in this file, which contains the node QueryString API, and defines the helper functions for sending Web page HTML to receive data submitted through the form.

install querystring

varQS =require(' QueryString '); exports.sendhtml = function (res, HTML) {Res.setheader (' Content-type ',' text/html ') ; Res.setheader (' Content-length ', Buffer.bytelength (HTML)); Res.end (HTML);} ; exports.parsereceiveddata = function (req, CB) {    varBODY ="'; Req.setencoding (' UTF8 ') ; Req.on (' Data ', function (chunk) {BODY = chunk;    } ) ; Req.on (' End ', function () {        vardata = Qs.parse (body);    CB (data); } ) ;} ; exports.actionform = function (ID, path, label) {    varHTML =' <form method= ' post ' action= '+ Path +' > '+' <input type= ' hidden "name=" id "value=" '+ ID +' > '+' <input type= ' submit ' value= '+label+'/> '+' </form> ';returnhtml;} ;

4. Add data with MySQL

The auxiliary functions are in place, and the code to add the work records to the MySQL database is written. Add the code in the following code listing to the Timetrack.js.

exports.add = function   (DB, req, res)  { Exports.parsereceiveddata (req, function   { db.query ( ' INSERT into work (hours, date , description) '  + , [work.hours, work . date, work.description], function   { if  (Err) th                Row  err;            Exports.show (DB, res);    }        ) ; } ) ;} ;

Note the question mark (?) in the above code, which is a placeholder to indicate where the parameter should be placed. Before being added to a query statement, the query method automatically escapes the parameters in case of a SQL injection attack. Also note the second parameter of the Query method, which is a string of values to replace the placeholder.

5. Delete MySQL data

function (db, req, res) {    function (work) {        db.query(            ‘delete from work where id = ?‘,            [work.id],            function (err) {                ifthrow err ;                exports.show( db, res ) ;            }        ) ;    } ) ;} ;

6. Update MySQL Data

To implement the logic of updating the work record, mark it as archived and add the following code to the Timetrack.js.

function (db, req, res) {    function (work) {        db.query(            ‘update work set archived = 1 where id = ?‘,            [work.id],            function (err) {                ifthrow err ;                exports.show( db, res ) ;            }        ) ;    } ) ;} ;

7. Get MySQL Data

The logic to add, delete, update the work record is already defined, and now you can add the logic in listing 5-14 to Timetrack to get the working record data (archived or not archived) to render it as HTML. A callback function was passed in when the query was initiated, and its parameter rows were used to hold the returned query results.

Exports.show = function (db, res, showarchived) {Console.log (' in Show function ') ;varquery =' SELECT * from work '+' where archived =? '+' ORDER by date desc ';varArchivevalue = (showarchived)?1:0; Console.log (' Archivevalue: '+ Archivevalue); Db.query (query, [Archivevalue], function (err, rows) {Console.log (rows);if(ERR)Throwerr; html = (showarchived)?"':' <a href= '/archived ' >archived work</a><br/> ';            HTML + = exports.workhitlisthtml (rows);            HTML + = exports.workformhtml ();        Exports.sendhtml (res, HTML); }    ) ;} ; exports.showarchived = function (db, res) {Exports.show (DB, Res,true) ;}

8. Render MySQL Records

Add the code from the following code listing to Timetrack.js. It renders the work record as HTML.

exports.workhitlisthtml = function (rows) {    varHTML =' <table> '; for(varIinchrows) {html + =' <tr> '; HTML + =' <td> '+ Rows[i].date +' </td> 'HTML + =' <td> '+ Rows[i].hours +' </td> 'HTML + =' <td> '+ Rows[i].description +' </td> '        if(!rows[i].archived) {html + =' <td> '+ exports.workarchiveform (rows[i].id) +' </td> '} HTML + =' <td> '+exports.workdeleteform (rows[i].id) +' </td> '; } HTML + =' </table> ';returnhtml;} ;

9. Rendering HTML Forms

exports.workformhtml = function () {   varHTML =' <form method= ' POST "action="/"> "+' <p>date (YYYY-MM-DD): <br/><input name= "Date" type= "text" ></p> '+' <p>hours worked:<br/><input name= "Hours" type= "text" ></p> '+' <p>Description:<br> '+' <textarea name= ' description ' ></textarea></p> '+' <input type= ' submit ' value= ' Add > '+' </form> ';returnhtml;} ; exports.workarchiveform = function (ID) {    returnExports.actionform (ID,'/archive ',' Archive ') ;} ; exports.workdeleteform = function (ID) {    returnExports.actionform (ID,'/delete ',' Delete ') ;} ;

10. Try It

The program has been finished and can now be run. Remember to use the MySQL admin tool to create a database named Timetrack first. Then start the program in the command line with the following command:

node timetrack_server.js

Finally access the http://127.0.0.1:3000 in the browser

Build a work-tracking process with MySQL