Flash cards
Review the key moves
What is the main idea behind Node.js MySQL Insert Into?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
Which statement best captures the main point of this lesson?
Complete the missing token from the example code.
___ mysql = require('mysql');Put the learning moves in the order that makes the concept easiest to apply.
Insert Into Table
To fill a table in MySQL, use the "INSERT INTO" statement.
Example
Insert a record in the "customers" table:
let mysql = require('mysql');
let con = mysql.createConnection({
host: "localhost", user: " yourusername ", password: " yourpassword ",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
let sql = "INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});
});Save the code above in a file called "demo_db_insert.js", and run the file:
Run "demo_db_insert.js"
C:\Users\
Your Name
>node demo_db_insert.jsWhich will give you this result
Connected! 1 record insertedInsert 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: INSERT INTO customers (name, address) VALUES ?
Example
Fill the "customers" table with data:
let mysql = require('mysql');
let con = mysql.createConnection({
host: "localhost", user: " yourusername ",
password: " yourpassword ", database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
let sql = "INSERT INTO customers (name, address) VALUES ?";
let values = [ ['John', 'Highway 71'], ['Peter', 'Lowstreet 4'],
['Amy', 'Apple st 652'], ['Hannah', 'Mountain 21'],
['Michael', 'Valley 345'], ['Sandy', 'Ocean blvd 2'],
['Betty', 'Green Grass 1'], ['Richard', 'Sky st 331'],
['Susan', 'One way 98'], ['Vicky', 'Yellow Garden 2'],
['Ben', 'Park Lane 38'], ['William', 'Central st 954'],
['Chuck', 'Main Road 989'], ['Viola', 'Sideway 1633']
];
con.query(sql, [values] , function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
});Save the code above in a file called "demo_db_insert_multple.js", and run the file:
Run "demo_db_insert_multiple.js"
C:\Users\
Your Name
>node demo_db_insert_multiple.jsWhich will give you this result
Connected! Number of records inserted: 14The Result Object
When executing a query, a result object is returned.
The result object contains information about how the query affected the table.
The result object returned from the example above looks like this:
{
fieldCount: 0, affectedRows: 14, insertId: 0,
serverStatus: 2, warningCount: 0, message: '\'Records:14 Duplicated: 0 Warnings: 0', protocol41: true,
changedRows: 0
}The values of the properties can be displayed like this:
Return the number of affected rows
console.log(result.affectedRows)Which will produce this result
14Get Inserted ID
For tables with an auto increment id field, you can get the id of the row you just inserted by asking the result object.
Note
To be able to get the inserted id, only one row can be inserted.
Example
Insert a record in the "customers" table, and return the ID:
let mysql = require('mysql');
let con = mysql.createConnection({
host: "localhost", user: " yourusername ", password: " yourpassword ",
database: "mydb"
});
con.connect(function(err) {
if (err) throw err;
let sql = "INSERT INTO customers (name, address) VALUES ('Michelle', 'Blue Village 1')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted, ID: " + result.insertId );
});
});Save the code above in a file called "demo_db_insert_id.js", and run the file:
Run "demo_db_insert_id.js"
C:\Users\
Your Name
>node demo_db_insert_id.jsWhich will give you something like this in return:
1 record inserted, ID: 15