- The Result Object
- Output data from Select result in HTML Table

Selecting From a MySQL Table

To select data from a table in MySQL database, use the "SELECT" statement.
- In this example we select all records with ID between 1 and 4 from the "friends"table, and display the return object:
const mysql = require('mysql');

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

let sql ='SELECT * FROM friends WHERE id BETWEEN 1 AND 4';

pool.query(sql, (err, res, cols)=>{
  if(err) throw err;
  console.log(res);
});
Save the code above in a file called "mysql_select.js" and run the file:
node test/mysql_select.js
Will give you a result like this:
[
  { id: 1, name: 'Olpram', address: 'Heaven, Peace 0'},
  { id: 2, name: 'Xela', address: 'Good 71'},
  { id: 3, name: 'Rotciv', address: 'Helpful 4'},
  { id: 4, name: 'Noi', address: 'Loving st 652'}
]

The Result and Fields Objects

1. The result object is an array containing each row as an object.
To return e.g. the address of the second record, just refer to the second array object's address property (first row has index 0):
console.log(res[1].address);
Will give you a result like this:
Good 71

2. The third parameter of the callback function (here "cols") is an array with objects containing information about each field in the result.
If you add this code in the callback function of the query() method:
console.log(cols[0]);
- You'll get an object with informations about first field; like this:
{
  catalog: 'def',
  db: 'nodedb',
  table: 'friends',
  orgTable: 'friends',
  name: 'id',
  orgName: 'id',
  charsetNr: 33,
  length: 11,
  type: 3,
  flags: 16899,
  decimals: 0,
  default: undefined,
  zeroFill: false,
  protocol41: true
}
To get the field name, just refer to the "name" property:
var fname = fields[0].name;

Output data from Select result in HTML Table

Here is another Node.js code that selects data from MySQL, orders results by name, and output results in a html table.
const http = require('http');
const mysql = require('mysql');

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

//html string that will be send to browser
var reo ='<html><head><title>Node.js MySQL Select</title></head><body><h1>Node.js MySQL Select</h1>{${table}}</body></html>';

//sets and returns html table with results from sql select
//Receives sql query and callback function to return the table
function setResHtml(sql, cb){
  pool.getConnection((err, con)=>{
    if(err) throw err;

    con.query(sql, (err, res, cols)=>{
      if(err) throw err;

      var table =''; //to store html table

      //create html table with data from res.
      for(var i=0; i<res.length; i++){
        table +='<tr><td>'+ (i+1) +'</td><td>'+ res[i].name +'</td><td>'+ res[i].address +'</td></tr>';
      }
      table ='<table border="1"><tr><th>Nr.</th><th>Name</th><th>Address</th></tr>'+ table +'</table>';

      con.release(); //Done with mysql connection

      return cb(table);
    });
  });
}

let sql ='SELECT name, address FROM friends WHERE id >1 ORDER BY name';

//create the server for browser access
const server = http.createServer((req, res)=>{
  setResHtml(sql, resql=>{
    reo = reo.replace('{${table}}', resql);
    res.writeHead(200, {'Content-Type':'text/html; charset=utf-8'});
    res.write(reo, 'utf-8');
    res.end();
  });
});

server.listen(8080, ()=>{
  console.log('Server running at //localhost:8080/');
});
Save the code above in a file called "mysql_res_html.js" and run the file:
node test/mysql_res_html.js
Open the browser and access the address: //localhost:8080/
It will show a page like in this image:
HTML Table MySQL Select

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which HTML5 tag is indicated to be used as container for menu with navigation links in Web site?
<section> <nav> <article>
<nav><ul>
 <li><a href="http://coursesweb.net/css/" title="CSS Course">CSS Course</a></li>
 <li><a href="http://www.marplo.net/jocuri/" title="Flash Games">Flash Games</a></li>
</ul></nav>
Which CSS property shifts an item horizontally to the left or right of where it was?
text-align clear float
.some_class {
  width: 30%;
  float: left;
}
Click on the Math object method which returns x, rounded downwards to the nearest integer.
Math.ceil(x) Math.abs(x) Math.floor(x)
var num = 12.34567;
num = Math.floor(num);
alert(num);       // 12
Indicate the PHP function which returns the number of characters in string.
mb_strlen() count() stristr()
$str = "string with utf-8 chars åèö";
$nrchr = mb_strlen($str);
echo $nrchr;        // 30
Select in MySQL, Output results in HTML Table

Last accessed pages

  1. PHP getElementById and getElementsByTagName (32878)
  2. Convert XML to JSON in PHP (9209)
  3. Script Users Register, Login, Online (17008)
  4. Get Mouse coordinates inside a Div or an Image (10435)
  5. PHP OOP - Inheritance, class extends (3945)

Popular pages this month

  1. Making DIV Contents Scroll Horizontally, with multiple Div`s inside (1440)
  2. Contact page - CoursesWeb (1403)
  3. Tabs effect with CSS (1398)
  4. PHP getElementById and getElementsByTagName (660)
  5. Get Attribute (ID, Class, Name, Title, Src) with jQuery (537)