Nodejs Course

The query() method of the "mysql" module can perform SQL queries that update and delete records in mysql table.
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).

Node.js MySQL Update

You can update existing records in a table by using the "UPDATE" statement.
To get the number of affected /updated rows, use the affectedRows property of the result object.

- In this example we overwrite the "address" column of the row with id 5, using mysql.escape() method to escape data in SQL query:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update with escape()
var adr ='Happy 12';
var id =5;
let sql ='UPDATE friends SET address ='+ mysql.escape(adr) +' WHERE id ='+ mysql.escape(id);

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
- Here is the same update sql query, with placeholders:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update using placeholders
let sql ='UPDATE friends SET address =? WHERE id =?';
var placeh =['Happy 12', 5]; //values for placeholders

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, placeh, (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
- Another method with placeholders, adding the column names and values in an objects:
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//update using placeholders
let sql ='UPDATE friends SET ? WHERE ?';
var adr ={address:'Happy 12'};
var id ={id:5};

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, [adr, id], (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Record(s) updated: '+ res.affectedRows);
  });
});
Results:
Record(s) updated: 1

Node.js MySQL Delete

You can delete records from an existing table by using the "DELETE FROM" statement.
To get the number of affected /deleted rows, use the affectedRows property of the result object.
const mysql = require('mysql');

const pool = mysql.createPool({
  host: '127.0.0.1',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//deleted with placeholders
let sql ='DELETE FROM friends WHERE id =? OR name =?';
var id =2;
var name='Olpram';

pool.getConnection((err, con)=>{
  if(err) throw err;

  con.query(sql, [id, name], (err, res)=>{
    if(err) throw err;
    con.release(); //release the connection
    console.log('Rows deleted: '+ res.affectedRows);
  });
});
Results:
Rows deleted: 2

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which attribute is used in <a> tag for the address of the link?
src href rel
<a href="http://coursesweb.net/" title="CoursesWeb.net">CoursesWeb.net</a>
Which CSS property sets the type of the text font?
font-family text-decoration font-size
h2 {
  font-family:"Calibri",sans-serif;
}
What instruction selects all the <div> tags with class="cls"?
querySelector("div.cls") getElementsByTagName("div") querySelectorAll("div.cls")
var elm_list = document.querySelectorAll("div.cls");
var nr_elms = elm_list.length;       // number of selected items
alert(nr_elms);
Indicate the function that can be used to get the sum of values in an array.
array_sum() array_diff() array_shift()
$arr =[1, 2, 3, 4);
$arr_sum = array_sum($arr);
echo $arr_sum;       // 10
Update and Delete in MySQL Table

Last accessed pages

  1. Display data from PHP Array, or MySQL in HTML table (21912)
  2. SHA1 Encrypt data in JavaScript (22480)
  3. Using Variable and Function with Name from String in JavaScript (754)
  4. Node.js Move and Copy file (22405)
  5. html2canvas - Save page screenshoot on server (2997)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (813)
  2. SHA256 Encrypt hash in JavaScript (734)
  3. Create simple Website with PHP (690)
  4. Read Excel file data in PHP - PhpExcelReader (689)
  5. PHP Unzipper - Extract Zip, Rar Archives (684)