Html get data from database

Technology is rapidly revolutionizing the modern business scenario and every organization – small, medium-sized, or large – needs an attractive, informative, and engaging website that can offer it maximum visibility. Needless to say, capturing and retaining the attention of users is an absolute challenge. The simple truth is that users get easily fed up if they are bombarded with an overdose of information and a confusing mish-mash of images. In this context, an interactive website is the best way to provide a meaningful and engaging user experience.

Get Instant Access To 

Coding For Kids eBook

A must read for every parent

What is an Interactive Website?

An interactive website is essentially an Internet page that uses different kinds of software to create a rich, interactive experience for the user i.e. it facilitates the user to be actively engaged with the site.  For e.g. Let’s take the case of a website that displays weather forecasts for a specific region. If the website is interactive, it enables the user to type in his/her location and shows the detailed weather report for that region. The website may also display the weather forecasts for various countries across the globe and allows the user to zoom in and focus on specific regions, or manipulate the globe to get a quick glimpse of the weather in different parts of the world.

Some of the most common types of interactive websites include Blogs, Forums, Wikis, and Social networks. Interactive websites allow users to change the way the website displays, play games, interact with friends online and perform a host of tasks. This includes:

• Online transactions
• Submitting stories, photos, videos, and blogs
• Interactive features, such as calculators and clickable maps, helps the user to explore the various services offered by a company
• Personalization of content and experience based on history and demographics.
• Features, such as live chat, pop-up, and help screens enables context-sensitive, user-friendly help.

In order to create an interactive website, one needs a database connected with the website to provide the required information.

What is a Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just a database.

Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.

What is a Web Database?

A web database is a system for storing information that can then be accessed via a website. For example, an online community may have a database that stores the username, password, and other details of all its members. 

At its most simple level, a web database is a set of one or more tables that contain data. Each table has different fields for storing information of various types. These tables can then be linked together in order to manipulate data in useful or interesting ways. In many cases, a table will use a primary key, which must be unique for each entry and allows for an unambiguous selection of data.

A web database can be used for a range of different purposes. Each field in a table has to have a defined data type. For example, numbers, strings, and dates can all be inserted into a web database. Proper database design involves choosing the correct data type for each field in order to reduce memory consumption and increase the speed of access. Although for small databases this often isn’t so important, big web databases can grow to millions of entries and need to be well designed to work effectively.

Content management systems commonly use web databases to store information such as posts, usernames, and comments. Using a database allows the website to be updated easily and without the need to edit the HTML code for each individual page. Not only is this a much more efficient way of creating and updating a website, but it also makes the process more accessible to people who aren’t fluent in the programming languages of the Internet.

An example of where a web database may be used is for an online forum. Forum software often creates a database with a number of tables, including one for users, posts, and settings. It is important for the relationships between the database tables to be properly set and defined so that posts and users can be linked together easily.

Node.js can be used in database applications. One of the most popular databases is MySQL. For this, you should have MySQL installed on your computer. You can download a free MySQL database at ttps://www.mysql.com/downloads/.

Once you have MySQL up and running on your computer, you can access it by using Node.js.

To access a MySQL database with Node.js, you need a MySQL driver. This tutorial will use the “MySQL” module, downloaded from NPM.

To download and install the “MySQL” module, open the Command Terminal and execute the following:

C:\Users\YourName\npm install mysql

Now you have downloaded and installed a mysql database driver. Node.js can use this module to manipulate the MySQL database:

var mysql = require('mysql');

Creating Connection

The following code is used to connect to a database.

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

Now, save the code above in a file called “demo_db_connection.js” and run the file:

C:\Users\YourName\node demo_db_connection.js

Now you can start querying the database using SQL statements.

Query a Database

Use SQL statements to read from (or write to) a MySQL database. This is also called “to query” the database. The connection object created in the example above has a method for querying the database:

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Result: " + result);
  });
});

The query method takes SQL statements as a parameter and returns the result.

Creating a Database

To create a database in MySQL, use the “CREATE DATABASE” statement:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});
 
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  con.query("CREATE DATABASE mydb", function (err, result) {
    if (err) throw err;
	console.log("Database created");
  });
});

The above code will create a database with the name mydb.

Save the code above in a file called “demo_create_db.js” and run the file:

Creating a Table

To create a table in MySQL, use the “CREATE TABLE” statement. Make sure you define the name of the database when you create the connection:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))";
  con.query(sql, function (err, result) {
    if (err) throw err;
	console.log("Table created");
  });
});

The above code creates a table with the name customers.

Save the code above in a file called “demo_create_table.js” and run the file:

C:\Users\YourName\node demo_create_table_db.js

Adding Data to a Table

To fill a table in MySQL, use the “INSERT INTO” statement.

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var 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:

C:\Users\YourName\node demo_db_insert.js

Retrieving Data from a Table

To select data from a table in MySQL, use the “SELECT” statement.

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers", function (err, result, fields) {
    if (err) throw err;
	console.log(result);
  });
});

Save the code above in a file called “demo_db_select.js” and run the file:

C:\Users\YourName\node demo_db_select.js

Filtering Records in a Database

When selecting records from a table, you can filter the selection by using the “WHERE” statement:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers WHERE address = 'Park Lane 38'", function (err, result) {
    if (err) throw err;
	console.log(result);
  });
});

The above code will fetch the record(s) with the address ‘Park Lane 38’.

Save the code above in a file called “demo_db_where.js” and run the file:

C:\Users\YourName\node demo_db_where.js

You can also select the records that start, includes, or ends with a given letter or phrase. Use the ‘%’ wildcard to represent zero, one, or multiple characters:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers WHERE address LIKE 'S%'", function (err, result) {
    if (err) throw err;
	console.log(result);
  });
});

The above code will fetch the record(s) where the address starts with the letter ‘S’.

Save the code above in a file called “demo_db_where_s.js” and run the file:

C:\Users\YourName\node demo_db_where_s.js

When query values are variables provided by the user, you should escape the values. This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database. The MySQL module has methods to escape query values:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result);
});

You can also use a ? as a placeholder for the values you want to escape. In this case, the variable is sent as the second parameter in the query() method:

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result) {
  if (err) throw err;
  console.log(result);
});

If you have multiple placeholders, the array contains multiple values, in that order:

var name = 'Amy';
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE name = ? OR address = ?';
con.query(sql, [name, adr], function (err, result) {
  if (err) throw err;
  console.log(result);
});

Sorting the Result

Use the ORDER BY statement to sort the result in ascending or descending order. The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers ORDER BY name", function (err, result) {
    if (err) throw err;
	console.log(result);
  });
});

Save the code above in a file called “demo_db_orderby.js” and run the file:

C:\Users\YourName\node demo_db_orderby.js

Deleting Record from a Table

You can delete records from an existing table by using the “DELETE FROM” statement:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  var sql = "DELETE FROM customers WHERE address = 'Mountain 21'";
  con.query(sql, function (err, result) {
    if (err) throw err;
	console.log("Number of records deleted: " + result.affectedRows);
  });
});

Save the code above in a file called “demo_db_delete.js” and run the file:

C:\Users\YourName\node demo_db_delete.js

Deleting a Table

You can delete an existing table by using the “DROP TABLE” statement:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  var sql = "DROP TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
	console.log("Table deleted");
  });
});

Save the code above in a file called “demo_db_drop_table.js” and run the file:

C:\Users\YourName\node demo_db_drop_table.js

Updating a Table

You can update existing records in a table by using the “UPDATE” statement:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
con.connect(function(err) {
  if (err) throw err;
  var sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'";
  con.query(sql, function (err, result) {
    if (err) throw err;
	console.log(result.affectedRows + " record(s) updated");
  });
});

Save the code above in a file called “demo_db_update.js” and run the file:

C:\Users\YourName\node demo_db_update.js

How show data from database in HTML?

Can HTML connect to database?

How fetch data from database in PHP and display HTML table?

How fetch data from database in Javascript and display HTML table?