Update and Delete in MySQL Table

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

<<-Select in MySQL, Output ..

Daily Test with Code Example

HTML

CSS

JavaScript

PHP-MySQL

Which meta tag provides a short description of the page?

<meta name="description" content="70-160 characters that describes the content of the page" />

Which CSS property is used to stop the wrapping effect of the "float"?

clear text-align position

#some_id {
  clear: both;
}

Click on the method which gets an array with all the elements in the document that have a specified tag name.

getElementsByName() getElementById() getElementsByTagName()

var divs = document.getElementsByTagName("div");
var nr_divs = divs.length;
alert(nr_divs);

Indicate the PHP function which returns the number of elements in array.

is_[) count() strlen()

$arr =[7, 8, "abc", 10);
$nri = count($arr);
echo $nri;        // 4