- 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.
To avoid SQL Injection, you should escape any user provided data before using it inside a SQL query. You can do so using the mysql.escape() method.
Alternatively, you can use ? characters as placeholders for values you would like to have escaped, and add the values into an array as second argument of query() method (in the same order as placeholders in 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:
con.query(sql, [values], (err, res)=>{});

- 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:
var last_id = res.insertId + res.affectedRows -1;
- 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:
Number of records inserted: 4
Id of first inserted row: 2
Id of last inserted row: 5
Connection is terminated.

If your mysql connection string has defined "localhost" in the "host" property, and you get the error:
Error: getaddrinfo ENONET localhost:3306
- Try to use: "127.0.0.1"

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which tag adds a new line into a paragraph?
<b> <br> <p>
First line ...<br>
Other line...
Which CSS property can be used to add space between letters?
text-size word-spacing letter-spacing
#id {
  letter-spacing: 2px;
}
What JavaScript function can be used to get access to HTML element with a specified ID?
getElementById() getElementsByTagName() createElement()
var elm = document.getElementById("theID");
var content = elm.innerHTML;
alert(content);
Click on the "echo" correct instruction.
echo "CoursesWeb.net" echo "CoursesWeb.net"; echo ""CoursesWeb.net";
echo "Address URL: http://CoursesWeb.net";
Create Table in MySQL Database and Insert data

Last accessed pages

  1. Butterfly Kyodai (29651)
  2. Ajax-PHP Chat Script (31506)
  3. Qwop (30373)
  4. CSS Course - Free lessons (7754)
  5. JavaScript trim, rtrim and ltrim (3884)

Popular pages this month

  1. Qwop (4647)
  2. Bubbles3 (3751)
  3. Butterfly Kyodai (3120)
  4. Drag Racer V3 (3078)
  5. Zuma Deluxe (2225)