[NodeJS & MySQL] How To Use MySQL with NodeJS + SteemJS Example

Repository

https://github.com/steemit/steem-js

What Will I Learn?

• How to use MySQL & PhpMyAdmin
• How To Save/Delete/Get/Update Data With MySQL
• Send Comment With SteemJS and save the data with MySQL

Requirements

• Text Editor - I recommending using Atom
• NodeJS
• XAMPP (Install Here: https://www.apachefriends.org/index.html)

Difficulty

• Basic

Tutorial Content

first create your database,
run XAMPP and open localhost/phpmyadmin click "New"

and create a database called mysql_database

now create a table called steem

now create 3 columns, first will be id with A_I(AutoIncrement) It will auto-fill the identifier of the row (1,2,3,4,5 etc.)

and you will need 2 VARCHARS (strings), first it's author it will store the author of our comment,
second will be permlink, it will store the permlink of our comment.

great, now click save, we're done with the database!

now add the packages,
we will use HTTP for the server,
steem for the API of steem,
mysql to access the database.

var http = require('http').createServer(), //creating the server
  steem = require('steem'),
  mysql = require('mysql'); // getting the mysql package

we already create the server, we just need to listen for the port (we will use 8080 for now)

// create listener for port 8080 - opening the server
http.listen(8080, function(){
  console.log('listening on *:8080');
});

now to access database we will need to connect the database,
first, create config json object with -
host, user, password, and base

//the config object of the database
var config = {
  "db":{
    host: "localhost",
    user: "root",
    password: "",
    base: "mysql_database"
  }
};

host - the host of the database (the IP)
user - the username of the database, for xampp it will be root, in web host it will change
password - if you didn't open it to the public it should be blank, on a web host, it will be what you choose
base - the name of the database you've created before

when you're using xampp the settings will be the same just the database will change (this is the database you made above, if you called it mysql_database leave it the same)

now create the connection to the database

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

so we're creating a connection and giving it to the variable db,
after we create the connection we can successfully make a connection to the database -

// connecting to the database
db.connect(function (error) {
  if (error)
  throw error;

  console.log('mysql connected');
});

if there is an error we will get the error to the console,
if the connection made successfully it will say mysql connected.

great now we can use mysql!

basic queries:

We will use a lot of question marks ? it means that the data will inject later, it preventing SQL injections from outside

• Insert

db.query("INSERT INTO `table`(`variable1`, `variable2`, `variable3`) VALUES(?, ?, ?)", [var1, var2, var3], function(err, res){
console.log(res);
});

• Select

db.query("SELECT * FROM `table`", function(err, res){
console.log(res);
});
db.query("SELECT * FROM `table` WHERE `variable1`=? AND `variable2`=?", [var1, var2], function(err, res){
console.log(res);
});

• Delete

db.query("DELETE FROM `table` WHERE `id`=?", [iD], function(err, res){
console.log(res);
});

• Update

db.query("UPDATE `table` SET `variable1`=? WHERE `id`=?", [varnew1, iD], function(err, res){
console.log(res);
});

that's all of the queries you will use most of the times,
there are some advanced ones but it's not used so often so we will go for it later in the tutorials!

now for the end of the tutorial let's make a steem comment and store the data on our database

so first, create a function that creates new permlink (a random text)

function permlinkCreate(number) {
  var text = "";
  var possible = "abcdefghijklmnopqrstuvwxyz";

  for (var i = 0; i < number; i++)
  text += possible.charAt(Math.floor(Math.random() * possible.length));

  return text;
}

so first, we're taking a number (the length of the text),
we're taking a "possible" letters (the ABC) and then making a loop of the length (number) and adding the letter to the text variable
we made at the start then sending back the permlink.

now we have a function that creates new permlink,
we need a function that sends a comment

so first create a function called sendComment with the variables account, wif, author, permlink, permalink, body
permalink is our new permlink
permlink is the permlink of the post

function sendComment(account, wif, author, permlink, permalink, body){

}

now make the comment function from the steem API

steem.broadcast.comment(wif, author, permlink, account,
permalink, '', body,
JSON.stringify({
tags: "steemit",
app: 'steemit/0.1',
  users: [account],
  format: "markdown"
}),
function(err, result) {

}

so we're at the comment function we need the posting wif, the author of the post, the permlink of the post,
the commentor(the one who send the comment - account), the permalink (the comment permlink), the body which is the content of the comment, json object with the tagsteemitjust cause comment need a basic tag, the app which is the application that the comment sent from (leavesteemit/0.1), users which is the commentor, the format which is the normal content format ofmarkdown`

now at the function insert the data to the database -

  console.log("the comment sent successfully!");
  // inserting data to the `steem` table
  db.query("INSERT INTO `steem`(`author`, `permlink`) VALUES(?, ?)", [account, permalink], function(err, res){
  if(err)
  throw err;

  console.log("Information saved Succesfully", res);
  });

first, we're telling the console that the comment sent,
then we're inserting the author and permlink to the steem table.
then when the insert made we're telling the console that the information is saved and sending the result

now the function is done, let's create variables for the account author, post permlink, comment permlink and the account&wif.

var pr = permlinkCreate(13),
  author = "upmeboost",
  permlink = "steemjs-full-tutorial-all-the-functions-all-the-abilities";
var acc = "guest123",
  wif = "5JRaypasxMx1L97ZUX7YuC5Psb5EAbF821kkAGtBj7xCJFQcbLg";

so easy enough, pr is the comment permlink,
the author is the post author,
permlink is the post permlink.
acc is the commentor,
wif is the posting key of the commentor.

now just send the function.

sendComment(acc, wif, author, permlink, pr, 'This is a test comment for utopian.io tutorial');

and you're done!

results -

if you have any suggestions for the next tutorial just leave comment here!

Curriculum

• NodeJs Tutorial part 1
• NodeJS Tutorial part 2

Proof of Work Done

https://github.com/upmeboost-utopian