- 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 defines the clickable areas inside the image map?
<map> <img> <area>
<img src="image.jpg" usemap="#map1">
<map name="map1">
  <area shape="rect" coords="9, 120, 56, 149" href="#">
  <area shape="rect" coords="100, 200, 156, 249" href="#">
</map>
Which CSS property defines what is done if the content in a box is too big for its defined space?
display overflow position
#id {
  overflow: auto;
}
Click on the event which is triggered when the mouse is positioned over an object.
onclick onmouseover onmouseout
document.getElementById("id").onmouseover = function(){
  document.write("Have Good Life");
}
Indicate the PHP variable that contains data added in URL address after the "?" character.
$_SESSION $_GET $_POST
if(isset($_GET["id"])) {
  echo $_GET["id"];
}
Create Table in MySQL Database and Insert data

Last accessed pages

  1. HTML5 Quick Tutorial (3645)
  2. Create simple Website with PHP (15916)
  3. Mahjong Gardens (7308)
  4. Dolphin Olympics 2 (1830)
  5. Get the value of the selected /checked checkboxes in a form (28843)

Popular pages this month

  1. Qwop (10555)
  2. Drag Racer V3 (6224)
  3. Flash Games - Free online Games (2162)
  4. List with All the Games (2073)
  5. Bubbles3 (1996)