![](https://images.techhive.com/images/article/2014/09/nodejs-1280x1024-100453402-primary.idge.jpg)

#### What Will I Learn?
Write here briefly the details of what the user is going to learn in a bullet list.

- Insert, export, update and delete data with MySQL with NodeJS

#### Requirements

- MySQL server
- XAMPP
- NodeJS

#### Difficulty

- Basic

#### Tutorial Contents
In this tutorial, you will learn how to insert, export, delete and update data in the database.

#### Curriculum

- [NodeJS Tutorials #1](https://utopian.io/utopian-io/@lonelywolf/nodejs-tutorials-1-creating-a-static-site-node-js)
- [NodeJS Tutorials #2 - Setup MySQL](https://utopian.io/utopian-io/@lonelywolf/nodejs-tutorials-3-mysql-part-1)

#### The Tutorial

### Inserting data

the very basic stuff in mysql is to insert data into our database.

you can be done it on the `phpmyadmin` page if you don't need something dynamically to put into the database and you can use it in your script.

first, do it on phpmyadmin and get the basic syntax of mysql.

go to your database ->

![image.png](https://cdn.utopian.io/posts/cc8dae9ecceae10496f0653641acc7954dacimage.png)

this is my database.

now to be able to contact the database, create a table.

![image.png](https://cdn.utopian.io/posts/124da850ae0074c059de51ddc1979327f800image.png)

the table is the way to contact with the database, you will need multiple tables for big projects, for example, I want to make user system I will need only `users` table but for a forum system I will need for example `users`, `categories` etc.

press `GO` to create the table.

![image.png](https://cdn.utopian.io/posts/632605c2d4eda592ddde20af43a52c089a7bimage.png)

now you need to make the variables for your table (you can add more after you done), most of the time you will want `ID` at any of your tables, to do it you need `A_I` checked and set it to `Primary`.

and then your other variables, for example, I made `text` which is VARCHAR (can store string) with 120 maximum lengths.

Press `save` and you're done with the table.

if all done correctly, you will see something like that >

![image.png](https://cdn.utopian.io/posts/08f3aaaaec631e45a010dbd8815e4c5c0020image.png)

now to use SQL you can go to `SQL` tab >

![image.png](https://cdn.utopian.io/posts/2531f73614f5a82544dc9b1069eb7b0d9945image.png)

![image.png](https://cdn.utopian.io/posts/274af1b106fb69b2bd9d628a3580da455d3aimage.png)

Press the `Insert` button and you will get simple Insert syntax because we set `ID` to `A_I` you don't need to insert it so delete it and `[value_1]`

`note`: you need to use `'` to input strings!

now you have `text` and `[value_2]`, change `[value_2]` to `simple text` and press `GO`

if all have done correctly, you will get this result >

![image.png](https://cdn.utopian.io/posts/67be2f88e04c9562f4736cf5dabc03ce4d6fimage.png)

and now we can see that in the `Browse` tab >

![image.png](https://cdn.utopian.io/posts/758dcbab6515d96663d690038af1f5b559a7image.png)

### Insert, Delete, Export and Update through a NodeJS script
First, you need a basic NodeJS server and setup MySQL on your server,

tutorials at the top of the post!

simple server code >

```
var app = require('http').createServer(handler);
var fs = require('fs');
const mysql = require('mysql')

app.listen(80);

const config = {
  "host": "localhost",
  "user": "root",
  "password": "",
  "base": "mysql_example"
};

var db = mysql.createConnection({
  host: config.host,
  user: config.user,
  password: config.password,
  database: config.base
});

db.connect(function (error) {
  if (!!error)
  throw error;

  console.log('mysql connected to ' + config.host + ", user " + config.user + ", database " + config.base);
});

function handler (req, res) {
  fs.readFile(__dirname + '/index.html',
  function (err, data) {
  if (err) {
  res.writeHead(500);
  return res.end('Error loading index.html');
  }

  res.writeHead(200);
  res.end(data);
  });
}
```

so let's make a function for each action.

```
function InsertData(type, string){
  db.query("INSERT INTO `test_table`(`"+type+"`) VALUES(?)", [string], function(err, result){
  console.log(err, result);
  });
}
```

because it's basically you can do it as a function so it will be easy to use, but for advanced I suggest you to just use the normal query function.

so, we made a function called `InsertData`, with 2 variables, `type` - the type of text we want to insert the string to.

`string` - the string/text we need to put on the table.

then we use the function `db.query` this is the function you need to always use to contact the database.

now we use the INSERT function in MySQL and we use the table `test_table` and we use `type(will be text)` to fill with our string.

and in `VALUES` we put `?`, what is it?

the `?` means that the variable will input to the function after its runs, which gives us better secure when we using password etc.

`[string]`, here you put the variables to fill the `?`, for this example, we use `string`.

then we make a function with the error output and the result,
and send it to the console.

![image.png](https://cdn.utopian.io/posts/3152054e636695247320ad7fc130a36052d6image.png)

if all have done correctly this will be your result.

```
mysql connected to localhost, user root, database mysql_example
null OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 2,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
```

the other ones it's basically the same, just some minor changes on the syntax so let's make the other ones.

all of the functions >

```
function InsertData(type, string){
  console.log(type);
  db.query("INSERT INTO `test_table`(`"+type+"`) VALUES(?)", [string], function(err, result){
  console.log(err, result);
  });
}
function ExportData(type, string){
  db.query("SELECT * FROM `test_table` WHERE `"+type+"`=?", [string], function(err, result){
  console.log(err, result);
  });
}
function UpdateData(type, string, newString){
  db.query("UPDATE `test_table` SET `"+type+"`=? WHERE `"+type+"`=?", [newString, string], function(err, result){
  console.log(err, result);
  });
}
function DeleteData(type, string){
  db.query("DELETE FROM `test_table` WHERE `"+type+"`=?", [string], function(err, result){
  console.log(err, result);
  });
}
```

usage of the functions.

Exporting data >

```
ExportData("text", "this is a simple text");
```

results

![image.png](https://cdn.utopian.io/posts/4eb7e0a35d2c213ce22b82d700cc74d7de7dimage.png)

```
null [ RowDataPacket { id: 1, text: 'this is a simple text' },
  RowDataPacket { id: 2, text: 'this is a simple text' },
  RowDataPacket { id: 3, text: 'this is a simple text' } ]
```

Update data >

```
UpdateData("text", "this is a simple text", "this is a text");
```

results >

![image.png](https://cdn.utopian.io/posts/980bfbaada63fdf6a894e621516bb5ae683eimage.png)

```
null OkPacket {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 0,
  serverStatus: 34,
  warningCount: 0,
  message: '(Rows matched: 3 Changed: 3 Warnings: 0',
  protocol41: true,
  changedRows: 3 }
```

Delete data >

```
DeleteData("id", 1);
```

(here I use id instead of text, it's easier to find data with ID)

results >

![image.png](https://cdn.utopian.io/posts/0fd21edc34d0a9686f7cfd641e8bfbbc67c1image.png)


```
null OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
```

and here we're done!

#### Next Tutorial
the next tutorial will be `how to make login page with mysql and nodejs`.

in the tutorial, we will use mysql nodejs and socket.io!

if you find this tutorial useful you can score this post with the new utopian system!

Have a great day!

<br /><hr/><em>Posted on <a href="https://utopian.io/utopian-io/@lonelywolf/nodejs-tutorials-3-mysql-part-2">Utopian.io -  Rewarding Open Source Contributors</a></em><hr/>