How to Retrieve Data From The MySQL Database using AJAX in Node

 MySQL is a relational database management system based on SQL – Structured Query Language, and managing data in MySQL is not that difficult.

In this detailed guide, we will find out how to retrieve data from the MySQL database using AJAX in the Node js app. We will build an HTML dropdown element in which we will get the records from the database.

We will install some packages from the NPM registry, and these packages will help us connect Node to the MySQL database and build the API routes that will interact with the database.

Furthermore, we will create the node script, which will help us run the node server.

Let us find out how to work with MySQL database through a node environment.

How to Get Data from MySQL Database using Node Js and jQuery Ajax

  • Step 1: Create Node App
  • Step 2: Add NPM Modules
  • Step 3: Create Table in Database
  • Step 4: Database Connection
  • Step 5: Create Server File
  • Step 6: Render and Show Data in HTML
  • Step 7: Serve Node Project

Create Node App

Type the mkdir command followed by your project name and hit enter to create a new folder.

mkdir node-vlog

Then, move into application folder.

cd node-vlog

Use the npm init command, this command creates the package.json file, where your project’s meta information stays.

npm init

In the project’s root create an app.js file, register the file name in scripts section so that this node script can be invoked using the command-line tool.

{
  "main": "app.js",
}

Add NPM Modules

We are going to install the given below packages from the npm registry, execute the command and install the modules simultaneously.

npm install ejs express cors mysql body-parser nodemon

Create Table in Database

In your database, you need to have a table with some records, so that you can get the data from the database.

If you don’t have table created, run the given sql command from the sql tab.

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

Database Connection

In your node project, make the database.js file and then inside the given code, add your database credentials in order to connect the database to the node app.

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

Create Server File

Open the app.js script file, in this file put the given code.

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

The following script is responsible to set up the ejs view engine, define the cors setting, declare the route that will communicate with database and define the application port.

Render and Show Data in HTML

At the root of your application, make the index.ejs file, this file will handle the view of your node app. It will display the records that you get from the database.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

Serve Node Project

Ultimately, you need to evoke the suggested command to start the node application.

nodemon

You require to use this url to test the app:

http://localhost/:5555

 

Conclusion

In this guide, we looked at the process of getting the results from the MySQL database into the Node js app.

We covered how to fetch the data from the MySQL database and display the records in the HTML select dropdown in a Node js app using the external dependencies.

We hope you liked this guide and share it with others. 

Source: https://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax 

What is GEEK

Buddha Community

How to Retrieve Data From The MySQL Database using AJAX in Node
Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

I am Developer

1599190536

How to Store Form Data in Database using PHP -2020

In this php code to insert form data into mysql database. I will show you simple way of how to create an html form that stores data in a mysql database using php.

#How to store form data in database using php

  1. Create html form
  2. Create mysql database connection file
  3. Create php file to insert form data into mysql database

https://www.tutsmake.com/php-code-insert-data-into-mysql-database-from-form/

#php code for inserting data into database from form #how to insert data in mysql using php form #how to insert data into database in php using xampp #how to save data from html form to a database using php #how to save data in database on button click in php

 iOS App Dev

iOS App Dev

1625133780

SingleStore: The One Stop Shop For Everything Data

  • SingleStore works toward helping businesses embrace digital innovation by operationalising “all data through one platform for all the moments that matter”

The pandemic has brought a period of transformation across businesses globally, pushing data and analytics to the forefront of decision making. Starting from enabling advanced data-driven operations to creating intelligent workflows, enterprise leaders have been looking to transform every part of their organisation.

SingleStore is one of the leading companies in the world, offering a unified database to facilitate fast analytics for organisations looking to embrace diverse data and accelerate their innovations. It provides an SQL platform to help companies aggregate, manage, and use the vast trove of data distributed across silos in multiple clouds and on-premise environments.

**Your expertise needed! **Fill up our quick Survey

#featured #data analytics #data warehouse augmentation #database #database management #fast analytics #memsql #modern database #modernising data platforms #one stop shop for data #singlestore #singlestore data analytics #singlestore database #singlestore one stop shop for data #singlestore unified database #sql #sql database

I am Developer

1611112146

Codeigniter 4 Autocomplete Textbox From Database using Typeahead JS - Tuts Make

Autocomplete textbox search from database in codeigniter 4 using jQuery Typeahead js. In this tutorial, you will learn how to implement an autocomplete search or textbox search with database using jquery typehead js example.

This tutorial will show you step by step how to implement autocomplete search from database in codeigniter 4 app using typeahead js.

Autocomplete Textbox Search using jQuery typeahead Js From Database in Codeigniter

  • Download Codeigniter Latest
  • Basic Configurations
  • Create Table in Database
  • Setup Database Credentials
  • Create Controller
  • Create View
  • Create Route
  • Start Development Server

https://www.tutsmake.com/codeigniter-4-autocomplete-textbox-from-database-using-typeahead-js/

#codeigniter 4 ajax autocomplete search #codeigniter 4 ajax autocomplete search from database #autocomplete textbox in jquery example using database in codeigniter #search data from database in codeigniter 4 using ajax #how to search and display data from database in codeigniter 4 using ajax #autocomplete in codeigniter 4 using typeahead js

How to Retrieve Data From The MySQL Database using AJAX in Node

 MySQL is a relational database management system based on SQL – Structured Query Language, and managing data in MySQL is not that difficult.

In this detailed guide, we will find out how to retrieve data from the MySQL database using AJAX in the Node js app. We will build an HTML dropdown element in which we will get the records from the database.

We will install some packages from the NPM registry, and these packages will help us connect Node to the MySQL database and build the API routes that will interact with the database.

Furthermore, we will create the node script, which will help us run the node server.

Let us find out how to work with MySQL database through a node environment.

How to Get Data from MySQL Database using Node Js and jQuery Ajax

  • Step 1: Create Node App
  • Step 2: Add NPM Modules
  • Step 3: Create Table in Database
  • Step 4: Database Connection
  • Step 5: Create Server File
  • Step 6: Render and Show Data in HTML
  • Step 7: Serve Node Project

Create Node App

Type the mkdir command followed by your project name and hit enter to create a new folder.

mkdir node-vlog

Then, move into application folder.

cd node-vlog

Use the npm init command, this command creates the package.json file, where your project’s meta information stays.

npm init

In the project’s root create an app.js file, register the file name in scripts section so that this node script can be invoked using the command-line tool.

{
  "main": "app.js",
}

Add NPM Modules

We are going to install the given below packages from the npm registry, execute the command and install the modules simultaneously.

npm install ejs express cors mysql body-parser nodemon

Create Table in Database

In your database, you need to have a table with some records, so that you can get the data from the database.

If you don’t have table created, run the given sql command from the sql tab.

CREATE DATABASE nodedb;

CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);
INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

Database Connection

In your node project, make the database.js file and then inside the given code, add your database credentials in order to connect the database to the node app.

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

Create Server File

Open the app.js script file, in this file put the given code.

var express = require('express')
var path = require('path')
var createError = require('http-errors')
var cors = require('cors')
var bodyParser = require('body-parser')
var app = express()

var dbMySQLNode = require('./database')
// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

app.use(cors())
app.get('/', (req, res) => {
  res.render('index')
})
app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})
app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})
module.exports = app

The following script is responsible to set up the ejs view engine, define the cors setting, declare the route that will communicate with database and define the application port.

Render and Show Data in HTML

At the root of your application, make the index.ejs file, this file will handle the view of your node app. It will display the records that you get from the database.

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

Serve Node Project

Ultimately, you need to evoke the suggested command to start the node application.

nodemon

You require to use this url to test the app:

http://localhost/:5555

 

Conclusion

In this guide, we looked at the process of getting the results from the MySQL database into the Node js app.

We covered how to fetch the data from the MySQL database and display the records in the HTML select dropdown in a Node js app using the external dependencies.

We hope you liked this guide and share it with others. 

Source: https://www.positronx.io/node-ajax-retrieve-records-from-mysql-database-tutorial/

#mysql #database #node #ajax