Node Js Tutorial Select From Where , Order by , Delete and Limit Using Mysql Module

Image Source

What Will I Learn?

I will learn how to restore data , how to order data by attributes , how to delete from a table and how to do a limit .

• Restore data using the ' select from where ' statement .
• Order data by the attributes with ascending and descending order .
• Delete a record from a table with the affectedRows propriety .
• Make a limit to your information display.

Requirements

• Knowledge about JavaScript language .
• Basic Knowledge about Command Line window .
• An editor for example notepad ++ .
• Connect with a database system .

Difficulty

• Basic

Tutorial Contents

In this tutorial I am going to talk about how to use ' select from where , order by , delete from a table and the limit ' in Node Js , so let's start ..

1 - Select From Where

If you are creating a node application and you need to restore data from the database , and your system with the Mysql , you need to use the ' select from where ' statement and for that I want to give you the way and the method to use this statement with the Node Js .

Firstly you must connect with the database and I explained it in the last tutorial , after the connection I will use always the method ' query ' in the library ' mysql ' and this method has 3 parameters , in this example we will use this methode with 2 and 3 parameters .


connection.query('select * from student', function(err, result){

console.log(result);

});


I have used the query method and because it's a asynchronous method, we must add a function , this function has two parameters the error and the result , the query has our statement select * from student it means select all the data from the student table and I will print by ' console.log ' the result received from this statement and this is the result :




As you see here there is two objects received from the database , I have created these object in the last tutorial and this is my ' student ' table




Now there is two data in my table I want to select by condition , there is two ways in node js to select with condition :

The first is to create the full statement as the normal way like it select * from student where name = "alexdendre" So you can write the full statement like it and this is the result :



The second method is to create the where outside the statement as variable and use the escape method that make a string portable and encode it with the ASCII characters ,concatenate it with the statement like it var name = "alexdendre";

connection.query('select * from student where name = ' + mysql.escape(name), function(err, result){

console.log(result);

});

The same result I will execute again my node file and this is the result :



I have in the function two parameters , the first is the error and the second is the result I can introduce another parameter which is ' fields ' , the fields is an object has informations about your field , I will print the fields to see all the details about it , this is the code


var name = 'alexdendre';

connection.query('select * from student where name = ' + mysql.escape(name), function(err, result, fields){

console.log(fields);

});


And this is the result when I run my application



As you see as an object has all the details the name the length all the details of this field , you can also specify the field , the field is an object and the fields is an array has in each case an object , to specify the element you select it by console.log(fields[n].propriety); , the ' n ' is the number of your field in the array , and the propriety is for example the name , the db as you have seen on the picture .

I want now to use the error inside my function if there is an error on my query and for that I have firstly the ' err ' as parameter and I need to use it by if (err) throw err; , if the value of ' err ' equals to true if there is an error then what the node will do will throw the error , you can also print the error and this is an example

I will change the ' name ' attribute to 'naame ' and test the result without this code if (err) throw err; let see the result if we execute our application



So this is the result is ' undefined ' there is no attributed named ' nnaame ' and for that the result is undefined , to see what's the error I must introduce the code above and this is the result when we do it

2- Order by :

I have now a list of student and I want to order them by age , for example I have in my application a filter box and I send the request of the client if he wants the result with ascending or descending order and for that I need the order by and this is the code


connection.query('select * from student order by age ', function(err, result){

if (err) throw err;

console.log(result);

});


I have selected all the data from the student table order by the age in ascending order and I will print the result



Here I have the ages from 29 to 45 , so by default is in ascending order , I will now add the key ' DESC ' like it 'select * from student order by age DESC ' and this is the result

3- Delete From Table

Sometimes you need to delete some data from table in your database system , in this example I want to delete a student named ' alexendre ' I will use the query method , but before that I want to create a variable named for example ' mySqlStat ' , this variable has my statement like it DELETE FROM student where name = 'alexdendre' so I deleted from the student where the condition that the name equals to 'alexdendre ' .

We need to call the query method to give it this statement , but before that the node js gives us a propriety named ' affectedRows ' to get the number of rows changed or deleted , I will give you the full code


var mySqlStat = "DELETE FROM student where name = 'alexdendre'";

connection.query(mySqlStat, function(err, result){

if (err) throw err;

console.log(result.affectedRows);

});


As you see I passed my variable as parameter to the query method and I printed the result.affectedRows to see the number of rows and this is the result

4- The Limit

This limit can helps you if you restore the data and you want just 8 fields in your page and if the number is bigger then this number the system will create another page and like it the paging system , to do that the node js has the ' limit ' option , and to do that I have 4 records , I will do the limit 3 and this is the code


connection.query("select * from student limit 3", function(err, result){

if (err) throw err;

console.log(result);

});


I selected all the data from the table student but the limit is 3 records and I will print the result on the console and this is the result



As you see here in the result I have 3 records , I will show you how the table student contains



So I have 4 records in my table and I limited it to 3 , the system gives the first 3 records , but what if I want the last 3 records what I will do ?

Simply I will use something call ' offset ' , this offset has many uses for example in bootstrap offset for the column to take a place here to start with the record that you want so the code will be like it


connection.query("select * from student limit 3 offset 1", function(err, result){

if (err) throw err;

console.log(result);

});


And this is the result when I started with the first record

Curriculum

• Node Js Tutorial Serve HTML Pages , Serve JSON Data , Connect And Insert Into DB Using Mysql Module
• Node Js Tutorial How To Create Read And Write Stream Manually And Automatically For Big Files
• Node Js Tutorial Read , Write and Edit files and directories by fs module
• Node JS Tutorial How To Use Event Emitter By Events Module

---

Posted on Utopian.io - Rewarding Open Source Contributors

---