Node.js is a popular server-side JavaScript runtime that enables developers to build scalable, high-performance applications. One of the most common use cases for Node.js is building web applications that require real-time updates, such as social media platforms, chat applications, and news portals. These applications often require the ability to fetch and display large amounts of data on-demand, without refreshing the entire page. This is where Ajax comes in.
Ajax stands for Asynchronous JavaScript and XML, and it allows web pages to update content dynamically without requiring a full page reload. In this tutorial, we will explore how to use Ajax with Node.js and MySQL to load more data on-demand, without requiring a full page refresh.
We will build a simple application that fetches and displays post titles from a MySQL database. The user will be able to click a "Load More" button to fetch additional post titles on-demand, without refreshing the page.
Prerequisites
To follow along with this tutorial, you should have a basic understanding of Node.js and MySQL. You will also need to have Node.js and MySQL installed on your machine.
Setting up the project
Create a new directory for your project and navigate to it in the terminal. Initialize a new Node.js project by running the following command:
npm init
Follow the prompts to set up your project. Once you have initialized your project, install the following dependencies:
npm install express mysql
First we have to create one table in your MySQL database, so for create table you can run below .SQL script which will create post and insert sample data into that table. So you can easily practice this tutorial in your local computer.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `post`
--
CREATE TABLE `post` (
`id` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
`post_title` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `post`
--
INSERT INTO `post` (`id`, `post_title`) VALUES
(1, 'Insert Update Delete using Stored Procedure in Mysql and PHP - 1'),
(2, 'Insert Update Delete using Stored Procedure in Mysql and PHP - 2'),
(3, 'Insert Update Delete using Stored Procedure in Mysql and PHP - 3 '),
(4, 'Auto Refresh Div Content Using jQuery and AJAX'),
(5, 'How to generate simple random password in php?'),
(6, 'Rename uploaded image in php with upload validation'),
(7, 'How to check Username availability using php, Ajax, Jquery and Mysql'),
(8, 'How To Insert Data Using Stored Procedure In Php Mysql'),
(9, 'How to merge two PHP JSON Array'),
(10, 'How to check Multiple value exists in an Array in PHP'),
(11, 'Create Simple Image using PHP'),
(12, 'How to Add Watertext or Watermark to an Image using PHP GD Library'),
(13, 'Make SEO Friendly or Clean Url in PHP using .htaccess'),
(14, 'Live Table Add Edit Delete using Ajax Jquery in PHP Mysql'),
(15, 'Export MySQL data to Excel in PHP - PHP Tutorial'),
(16, 'Load More Data using Ajax Jquery with PHP MySql'),
(17, 'Dynamically Add / Remove input fields in PHP with Jquery Ajax'),
(18, 'PHP Introduction - PHP Tutorial for Beginner'),
(19, 'PHP Variables - Echo, Print statement - PHP Tutorial for Beginner'),
(20, 'How to Import Excel Data into MySQL Database using PHP'),
(21, 'Convert Currency using Google Finance in PHP'),
(22, 'Dynamic Dependent Select Box using Jquery and Ajax in PHP'),
(23, 'How to use HTML tag into PHP tag'),
(24, 'How to use PHP code into HTML Tag'),
(25, 'How to get Multiple Checkbox values in php'),
(26, 'Ajax Live Data Search using Jquery PHP MySql'),
(27, 'Auto Save Data using Ajax, Jquery, PHP and Mysql'),
(28, 'How to Use Ajax with PHP for login with shake effect'),
(29, 'How to get Time Ago like Facebook in PHP'),
(30, 'Upload Resize Image using Ajax Jquery PHP without Page Refresh'),
(31, 'How to Search for Exact word match FROM String using LIKE'),
(32, 'How To Create A Show Password Button using Jquery'),
(33, 'Use Marquee HTML tag with PHP and Mysql - PHP Tutorial'),
(34, 'How to Enter PHP Array within MySQL Database'),
(35, 'Use Ajax with Jquery in PHP to check Session Expired'),
(36, 'Ajax Delete multiple data with checkboxes in PHP Jquery Mysql'),
(37, 'Automatic Logout after 15 minutes of user Inactivity using PHP'),
(38, 'PHP Login Script with Remember me Login Details'),
(39, 'Arithmetic Operators - PHP Beginner Tutorial'),
(40, 'Shorten Dynamic Comment with Jquery PHP Mysql'),
(41, 'If, If else Conditional Statement in PHP'),
(42, 'If, Else if Conditional statement in PHP'),
(43, 'How to Create Zip File using PHP Code'),
(44, 'Increment / Decrement Operator - PHP Beginner Tutorial'),
(45, 'How to Generate CSV File from PHP Array'),
(46, 'Upload CSV and Insert Data into Mysql Using PHP'),
(47, 'Multiple Images Upload using PHP Jquery Ajax'),
(48, 'Fetch JSON Data & Insert into Mysql table in PHP'),
(49, 'Comparison Operators - PHP Beginner Tutorial'),
(50, 'Create Simple Progress bar using HTML, CSS with jquery'),
(51, 'Append Data to JSON File using PHP'),
(52, 'Form Submit with Fade Out Message using Jquery Ajax PHP'),
(53, 'String Operators - PHP Beginner Tutorial'),
(54, 'Load Records on Select box using Ajax Jquery Mysql and PHP'),
(55, 'Fetch Data from Two or more Table Join using PHP and MySql'),
(56, 'Convert Data from Mysql to JSON Formate using PHP'),
(57, 'How to use Mysql View in PHP Code'),
(58, 'Upload and Extract a Zip File in PHP'),
(59, 'Stylish Switch Button using CSS3 and Jquery'),
(60, 'How to create Spinner or Loaders with CSS3'),
(61, 'How to Auto Resize a textarea html field by jQuery'),
(62, 'Insert data into Table using OOPS in PHP'),
(63, 'Select or Fetch Data from Mysql Table using OOPS in PHP'),
(64, 'Update or Edit Data from Mysql Table using OOPS in PHP'),
(65, 'Delete Data from Mysql Table using OOP in PHP'),
(66, 'PHP Login Script using OOP'),
(67, 'How to load Product on price change using Ajax Jquery with PHP Mysql'),
(68, 'How to Make Simple Pagination using PHP MySql'),
(69, 'PHP MySQL Insert record if not exists in table'),
(70, 'How to search multiple words at a time in Mysql php'),
(71, 'How to load data from json file in php'),
(72, 'Load JSON Data using Ajax getJSON Method'),
(73, 'Add smooth scrolling by using Jquery Animate method'),
(74, 'Ajax Autocomplete textbox using jQuery, PHP and MySQL'),
(75, 'Jquery Insert Form Data using Ajax serialize() method with php mysql'),
(76, 'Insert Form data using Jquery Post() Method with PHP Mysql'),
(77, 'Connect MySQL Database in PHP via PDO - PHP Data Object'),
(78, 'Insert data into Mysql in PHP using PDO - PHP Data Object'),
(79, 'Fetch Data from Mysql in PHP using PDO - PHP Data Object'),
(80, 'PDO (PHP Data Object) - Edit / Update Mysql Table data in PHP'),
(81, 'PDO (PHP Data Object) - Delete Mysql Table data in PHP'),
(82, 'PHP Login Script using PDO with Session'),
(83, 'PHP Jquery Ajax : Insert Radio Button value on click'),
(84, 'How to Count a Array of Specific Words from String in PHP'),
(85, 'Create dynamic JSON file in PHP Mysql'),
(86, 'Insert Checkbox values using Ajax Jquery in PHP'),
(87, 'How to Remove file from Server in PHP Programming Language'),
(88, 'PHP Tutorial - Find the number of days, hours, minutes and seconds between two dates'),
(89, 'Displaying text in Vertical Direction by using CSS with HTML'),
(90, 'Show JSON Data in Jquery Datatables'),
(91, 'Sliding Text on Images by using Jquery'),
(92, 'How to Make Horizontal Menu using CSS and Jquery'),
(93, 'How to make flashing button with CSS3 keyframes Animation'),
(94, 'Get Maximum Numeric value from Associative Array in PHP'),
(95, 'How to make Zebra Stripes Table with PHP Mysql'),
(96, 'How to Get File Extension in PHP'),
(97, 'Server Side Form Validation in PHP Programming Language'),
(98, 'PHP Array Functions - array() Function'),
(99, 'PHP Array Functions - array_change_key_case() Function'),
(100, 'PHP Array Functions - array_chunk() Function');
COMMIT;
We will use the Express.js framework to create a simple web server, and the mysql package to connect to our MySQL database.
Create a new file called server.js, and add the following code:
const express = require('express');
const mysql = require('mysql');
const application = express();
const connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database : 'testing'
});
application.get("/", (request, response) => {
response.sendFile(__dirname + '/index.html');
});
application.get("/get_data", (request, response) => {
const start_index = request.query.start_index;
const number_of_record = request.query.num_record;
const sql = `SELECT post_title FROM post ORDER BY id ASC LIMIT ${start_index}, ${number_of_record}`;
connection.query(sql, (error, results) => {
response.json(results);
});
});
application.listen(3000, () => {
console.log('Server started on port 3000');
});
This code sets up a new Express.js application and creates a new MySQL connection. It also sets up two routes - the "/" route, which serves our HTML file, and the "/get_data" route, which fetches post titles from the database and sends them back to the client as a JSON response.
Create a new file called index.html, and add the following code:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Load More Data using Ajax with Node.js & MySQL</title>
<link href="https://getbootstrap.com/docs/5.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>
<body>
<div class="container mt-5 mb-5">
<h1 class="text-primary text-center"><b>Load More Data using Ajax with Node.js & MySQL</b></h1>
<div class="mt-3 mb-3">
<div class="card">
<div class="card-header">Post Data</div>
<div class="card-body">
<ul class="list-group mb-3" id="post_data">
</ul>
<button type="button" class="btn btn-primary" id="load_button">Load More</button>
</div>
</div>
</div>
</div>
<script>
//npm install express mysql
const post_data = document.querySelector('#post_data');
const load_button = document.querySelector('#load_button');
var start_index = 0;
var number_of_record = 10;
load_data();
load_button.addEventListener('click', () => {
load_data();
});
function load_data()
{
load_button.innerHTML = 'wait...';
load_button.disabled = true;
setTimeout(function(){
const request = new XMLHttpRequest();
request.open('GET', `/get_data?start_index=${start_index}&num_record=${number_of_record}`);
request.onload = () => {
const results = JSON.parse(request.responseText);
let html = '';
if(results.length > 0)
{
results.forEach(result => {
html += '<li class="list-group-item">' + result.post_title + '</li>';
start_index++;
console.log(start_index);
});
load_button.innerHTML = 'Load More';
load_button.disabled = false;
}
else
{
html += '<li class="list-group-item active">No More Data Found</li>';
load_button.remove();
}
post_data.innerHTML = post_data.innerHTML + html;
window.scrollTo(0, document.body.scrollHeight);
};
request.send();
}, 1000);
}
</script>
</body>
</html>
To make use of this code, you need to create an HTML file where you will include the necessary JavaScript code. In this example, we have created an index.html file that includes the Bootstrap CSS library.
The HTML file has a container with a card that has a header called "Post Data." Under the header, we have a list group with an ID called "post_data" where we will append the fetched data. Below the list group, we have a button with an ID called "load_button" that will be used to load more data.
In the JavaScript code, we start by selecting the list group and the button element using the document.querySelector method. We also set the start_index and number_of_record variables to 0 and 10, respectively, and then we call the load_data function with these parameters.
The load_data function sets the load_button text to "wait..." and disables the button to prevent multiple clicks. We then create a new XMLHttpRequest object and set the request URL to "/get_data" with the start_index and num_record parameters. We also set the onload event handler to parse the JSON response and append the results to the list group. We then set the load_button text to "Load More" and enable the button. If the results array is empty, we append a message to the list group indicating that there is no more data to load and remove the button.
Finally, we append the HTML content to the list group and scroll to the bottom of the page. When the user clicks the load_button, the load_data function is called again, but with an updated start_index parameter, which fetches the next set of data.
In conclusion, by using Node.js and MySQL together, you can easily load more data with Ajax. This technique can be useful when working with large datasets or when you want to minimize the initial load time of your page. By following the steps outlined in this article and by modifying the code to fit your specific needs, you can create a dynamic and responsive website that will impress your users.
