Create Table in MySQL Database and Insert data

Nodejs Course

- Insert Into Table
- Insert Multiple Records

Creating Table

To create a table in MySQL, use the "CREATE TABLE" statement.
In the mysql.createConnection() method add the "database" property with the name of the database when you create the connection.
- In this example we create a table "friends" in the "nodedb" mysql database:

const mysql = require('mysql');

const con = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8' //encoding charset
});

let sql ='CREATE TABLE IF NOT EXISTS friends (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, address VARCHAR(255))';
  
con.query(sql, (err, res)=>{
  if(err) throw err;
  console.log('Table created.', res);

  con.end(err=>{
    if(err) throw err;
    console.log('Connection is terminated.');
  });
});

Save the code above in a file called "mysql_create_table.js" and run the file:

node test/mysql_create_table.js

Insert Into Table

To insert data into a MySQL table, use the "INSERT INTO" SQL query. - Example, insert one row with values for name and address fields.

const mysql = require('mysql');

const con = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8' //encoding charset
});

var name ='Olpram';
var adr ='Heaven, Peace 0';
let sql ='INSERT INTO friends (name, address) VALUES ('+ mysql.escape(name) +', '+ mysql.escape(adr) +')';

con.query(sql, (err, res)=>{
  if(err) throw err;
  console.log(res);

  con.end(err=>{
    if(err) throw err;
    console.log('Connection is terminated');
  });
});

- Or, the same INSERT statement, with placeholders:

const mysql = require('mysql');

const con = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8' //encoding charset
});

var name ='Olpram';
var adr ='Heaven, Peace 0';
let sql ='INSERT INTO friends (name, address) VALUES (?, ?)';

con.query(sql, [name, adr], (err, res)=>{
  if(err) throw err;
  console.log(res);

  con.end(err=>{
    if(err) throw err;
    console.log('Connection is terminated');
  });
});

Save the code above in a file called "mysql_insert.js" and run the file:

node test/mysql_insert.js

Which will give you this result, an object with information about how the query affected the table:

{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}
Connection is terminated.

- For tables with an AUTO_INCREMENT id field, you can get the id of the row you just inserted by reading the "insertId" property of the result object.

console.log('1 record inserted, ID: '+ res.insertId);

Insert Multiple Records

To insert more than one record, make an array containing the values, and insert a question mark in the sql, which will be replaced by the value array:

let sql ='INSERT INTO friends (name, address) VALUES ?';
let values =[
  ['a', 'b'],
  ['a2', 'b2'],
  //...
];

And add the array with values into an array as the second argument in the con.query() function:
- When multiple rows are inserted, the value of the: res.insertId contains the AUTO_INCREMENT ID of the first inserted row.
In this case, to get the AUTO_INCREMENT ID of the last inserted row, use: - The res.affectedRows property contains the number of inserted rows.
Example:

const mysql = require('mysql');

const con = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8' //encoding charset
});

let sql ='INSERT INTO friends (name, address) VALUES ?';
let values = [
  ['Xela', 'Good 71'],
  ['Rotciv', 'Helpful 4'],
  ['Noi', 'Loving st 652'],
  ['Anilehgna', 'Blessing 21']
];

con.query(sql, [values], (err, res)=>{
  if(err) throw err;
  var last_id = res.insertId + res.affectedRows -1;
  console.log('Number of records inserted: '+ res.affectedRows +'\n Id of first  inserted row: '+ res.insertId +'\n Id of last inserted row: '+ last_id);

  con.end(err=>{
    if(err) throw err;
    console.log('Connection is terminated.');
  });
});

Save the code above in a file called "mysql_insert2.js" and run the file:

node test/mysql_insert2.js

Will give you this result:

<div>Web Programming and Development</div>

h2 {
  text-decoration: underline;
}

function someFunction() { alert("CoursesWeb.net"); }
setTimeout("someFunction()", 2000);

$food =["fruits" =>["banana", "apple"), "veggie" =>["collard", "pea"));
$nr_food = count($food, 1);
echo $nr_food;       // 6