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
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 ->
this is my database.
now to be able to contact the database, create a table.
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.
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 >
now to use SQL you can go to SQL tab >
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 >
and now we can see that in the Browse tab >
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.
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
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 >
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 >
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!
Posted on Utopian.io - Rewarding Open Source Contributors
Thank you for the contribution It has been approved.
Need help? Write a ticket on https://support.utopian.io.
Chat with us on Discord.
[utopian-moderator]
Thanks!
Hey @lonelywolf I am @utopian-io. I have just upvoted you!
Achievements
Utopian Witness!
Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x