Saturday, 18 March 2023

Node.js and MySQL: How to Load More Data with Ajax

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.


Node.js and MySQL: How to Load More Data with Ajax


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.

Tuesday, 14 March 2023

Ajax Live Search using Node.js with MySQL


In today's fast-paced world, users expect web applications to be responsive and efficient. One of the ways to achieve this is by implementing an Ajax live search feature on your website. This feature allows users to search for content on your website without having to reload the page. In this article, we will show you how to implement an Ajax live search feature using Node.js and MySQL.

Node.js is an open-source, cross-platform JavaScript runtime environment that allows developers to build server-side applications with JavaScript. MySQL is a popular open-source relational database management system that is widely used in web development. Together, they provide a powerful combination for building web applications.

To get started, you will need to have Node.js and MySQL installed on your machine. You will also need to have a basic understanding of JavaScript, HTML, and CSS.


Ajax Live Search using Node.js with MySQL


Step 1: Setting up the Database


The first step is to set up the database. You will need to create a table to store the data that will be searched. For this example, we will create a table called "customers" with columns for "customer_id", "customer_first_name", "customer_last_name", "customer_email" and "customer_gender". Below you can find .sql script for create customers table and it will insert some sample data into that table.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`customer_id`, `customer_first_name`, `customer_last_name`, `customer_email`, `customer_gender`) VALUES
(1, 'Mathilda', 'Garnam', 'mgarnam0@rediff.com', 'Female'),
(2, 'Tymon', 'McEniry', 'tmceniry1@free.fr', 'Male'),
(3, 'Margarette', 'Danilyak', 'mdanilyak2@joomla.org', 'Female'),
(4, 'Ermanno', 'Huebner', 'ehuebner3@wikipedia.org', 'Male'),
(5, 'Agnes', 'Barabich', 'abarabich4@delicious.com', 'Female'),
(6, 'Hamil', 'De Zuani', 'hdezuani5@hostgator.com', 'Male'),
(7, 'Corrie', 'Althorpe', 'calthorpe6@themeforest.net', 'Female'),
(8, 'Ricard', 'Dumelow', 'rdumelow7@china.com.cn', 'Male'),
(9, 'D\'arcy', 'Crayton', 'dcrayton8@furl.net', 'Male'),
(10, 'Kayle', 'Stonier', 'kstonier9@thetimes.co.uk', 'Female'),
(11, 'Julissa', 'Bogeys', 'jbogeysa@w3.org', 'Female'),
(12, 'Saul', 'Hyam', 'shyamb@senate.gov', 'Male'),
(13, 'Saleem', 'Pettengell', 'spettengellc@si.edu', 'Male'),
(14, 'Ives', 'Osmon', 'iosmond@forbes.com', 'Male'),
(15, 'Wendie', 'Acton', 'wactone@army.mil', 'Female'),
(16, 'Ches', 'Redd', 'creddf@un.org', 'Male'),
(17, 'Sarine', 'Cossum', 'scossumg@hubpages.com', 'Female'),
(18, 'Farly', 'Arsmith', 'farsmithh@linkedin.com', 'Male'),
(19, 'Althea', 'Tavernor', 'atavernori@printfriendly.com', 'Female'),
(20, 'Gayla', 'Billes', 'gbillesj@miitbeian.gov.cn', 'Female'),
(21, 'Teodoor', 'Shreenan', 'tshreenank@hp.com', 'Male'),
(22, 'Rich', 'Frusher', 'rfrusherl@cbc.ca', 'Male'),
(23, 'Lissie', 'Glazier', 'lglazierm@elpais.com', 'Female'),
(24, 'Glyn', 'Dealey', 'gdealeyn@unesco.org', 'Male'),
(25, 'Kathy', 'Swansborough', 'kswansborougho@amazon.de', 'Female'),
(26, 'Dudley', 'Hopkyns', 'dhopkynsp@mozilla.com', 'Male'),
(27, 'Byran', 'Ealles', 'beallesq@engadget.com', 'Male'),
(28, 'Kenon', 'MacGray', 'kmacgrayr@stumbleupon.com', 'Male'),
(29, 'Sibilla', 'Norres', 'snorress@smugmug.com', 'Female'),
(30, 'Melita', 'Redman', 'mredmant@a8.net', 'Female'),
(31, 'Ki', 'Brydson', 'kbrydsonu@google.ca', 'Female'),
(32, 'Bernardine', 'Follis', 'bfollisv@unc.edu', 'Female'),
(33, 'Farleigh', 'Sudy', 'fsudyw@unc.edu', 'Male'),
(34, 'Belle', 'Dearness', 'bdearnessx@google.co.uk', 'Female'),
(35, 'Onfre', 'Kee', 'okeey@archive.org', 'Male');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;


Step 2: Setting up the Server


Next, we will set up the server using Node.js. We will use the Express framework to handle the HTTP requests and responses. You can install Express using npm, the package manager for Node.js, by running the following command in your terminal:


cd f:

In my computer, We will create node application in f: drive, so we have goes into this f: drive.


cd node

After this we have goes into node in which we will create Ajax Live data Search Node Application.


mkdir ajax_live_data_search

This command will create ajax_live_data_search directory under node directory.


cd ajax_live_data_search

After this we have goes inti this ajax_live_data_search directory in which we will download different node module.


npm install express mysql

This command will download Node Express Module and MySQL Module under this ajax_live_data_search directory.





After installing Express, create a new file called "app.js" and add the following code:

app.js

const express = require('express');

const mysql = require('mysql');

const app = express();

const port = 3000;

const pool = mysql.createPool({
	connectionLimit : 10,
	host : 'localhost',
	user : 'root',
	password : '',
	database : 'testing'
});

app.get('/', (request, response) => {

	response.sendFile(__dirname + '/index.html');

});

app.listen(port, () => {

	console.log(`Server listening on port ${port}`);

});


This code sets up a basic Express server that listens on port 3000. You can test that the server is working by running "node app.js" in your terminal and navigating to "http://localhost:3000" in your browser and it will load HTML content of index.html file.

Step 3: Implementing the Ajax Live Search


Now we will implement the Ajax live search feature. We will use Vanilla JavaScript to make Ajax requests to the server and update the search results in real-time. You can include Vanilla JavaScript in your index.html file by adding the following code:

index.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Ajax Live Data Search in Node.js with 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>Ajax Live Data Search in Node.js with MySql</b></h1>
        <div class="mt-3 mb-3">
            <div class="card">
                <div class="card-header">Customer Data</div>
                <div class="card-body">
                    <div class="mb-3">
                        <input type="text" id="search" placeholder="Search..." class="form-control" autocomplete="off">
                        <table class="table table-bordered mt-3">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Email</th>
                                    <th>Gender</th>
                                </tr>
                            </thead>
                            <tbody id="results">

                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
<script type="text/javascript">

const searchInput = document.querySelector('#search');

const results_body = document.querySelector('#results');

load_data();

function load_data(query = '')
{
    const request = new XMLHttpRequest();

    request.open('GET', `/search?q=${query}`);

    request.onload = () => {

        const results = JSON.parse(request.responseText);

        let html = '';

        if(results.length > 0)
        {
            results.forEach(result => {
                html += `
                <tr>
                    <td>`+result.customer_id+`</td>
                    <td>`+result.customer_first_name+`</td>
                    <td>`+result.customer_last_name+`</td>
                    <td>`+result.customer_email+`</td>
                    <td>`+result.customer_gender+`</td>
                </tr>
                `;
            });
        }
        else
        {
            html += `
            <tr>
                <td colspan="5" class="text-center">No Data Found</td>
            </tr>
            `;
        }

        results_body.innerHTML = html;

    };

    request.send();
}

searchInput.addEventListener('input', () => {

    const query = searchInput.value;

    load_data(query);

});

</script>


This code listens for keyup events on the search input field and makes an Ajax request to the server with the search query. The server responds with a JSON object containing the search results, which are then displayed in the search results list.

To handle the Ajax requests on the server, add the following code to "app.js":

app.js

app.get('/search', (request, response) => {

	const query = request.query.q;

	var sql = '';

	if(query != '')
	{
		sql = `SELECT * FROM customers WHERE customer_first_name LIKE '%${query}%' OR customer_last_name LIKE '%${query}%' OR customer_email LIKE '%${query}%'`;
	}
	else
	{
		sql = `SELECT * FROM customers ORDER BY customer_id`;
	}

	pool.query(sql, (error, results) => {

		if (error) throw error;

		response.send(results);

	});

});


This code creates a MySQL connection and listens for GET requests to "/search". It retrieves the search query from the GET request URL and executes a SQL query to search for customers whose first name, last name or email matches the query. The results are returned as a JSON object.

Step 4: Run Node Application


So here our code is ready now for check output in the browser, we have goes to terminal and run following command.


node app.js


This command will start node server and we can access this node application in browser by this http://localhost:3000 url.

Conclusion


In this article, we have shown you how to implement an Ajax live search feature using Node.js and MySQL. By using these technologies, you can build responsive and efficient web applications that provide a great user experience. With a few lines of code, you can easily add this feature to your web application and improve its functionality.

Complete Source Code


app.js



const express = require('express');

const mysql = require('mysql');

const app = express();

const port = 3000;

const pool = mysql.createPool({
	connectionLimit : 10,
	host : 'localhost',
	user : 'root',
	password : '',
	database : 'testing'
});

app.get('/', (request, response) => {

	response.sendFile(__dirname + '/index.html');

});

app.get('/search', (request, response) => {

	const query = request.query.q;

	var sql = '';

	if(query != '')
	{
		sql = `SELECT * FROM customers WHERE customer_first_name LIKE '%${query}%' OR customer_last_name LIKE '%${query}%' OR customer_email LIKE '%${query}%'`;
	}
	else
	{
		sql = `SELECT * FROM customers ORDER BY customer_id`;
	}

	pool.query(sql, (error, results) => {

		if (error) throw error;

		response.send(results);

	});

});

app.listen(port, () => {

	console.log(`Server listening on port ${port}`);

});


index.html



<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Ajax Live Data Search in Node.js with 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>Ajax Live Data Search in Node.js with MySql</b></h1>
        <div class="mt-3 mb-3">
            <div class="card">
                <div class="card-header">Customer Data</div>
                <div class="card-body">
                    <div class="mb-3">
                        <input type="text" id="search" placeholder="Search..." class="form-control" autocomplete="off">
                        <table class="table table-bordered mt-3">
                            <thead>
                                <tr>
                                    <th>ID</th>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Email</th>
                                    <th>Gender</th>
                                </tr>
                            </thead>
                            <tbody id="results">

                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
<script type="text/javascript">

const searchInput = document.querySelector('#search');

const results_body = document.querySelector('#results');

load_data();

function load_data(query = '')
{
    const request = new XMLHttpRequest();

    request.open('GET', `/search?q=${query}`);

    request.onload = () => {

        const results = JSON.parse(request.responseText);

        let html = '';

        if(results.length > 0)
        {
            results.forEach(result => {
                html += `
                <tr>
                    <td>`+result.customer_id+`</td>
                    <td>`+result.customer_first_name+`</td>
                    <td>`+result.customer_last_name+`</td>
                    <td>`+result.customer_email+`</td>
                    <td>`+result.customer_gender+`</td>
                </tr>
                `;
            });
        }
        else
        {
            html += `
            <tr>
                <td colspan="5" class="text-center">No Data Found</td>
            </tr>
            `;
        }

        results_body.innerHTML = html;

    };

    request.send();
}

searchInput.addEventListener('input', () => {

    const query = searchInput.value;

    load_data(query);

});

</script>



Sunday, 12 March 2023

How to check if an email address already exists with Node.js & MySQL

As the popularity of web applications continues to grow, it has become increasingly important to implement robust authentication systems that can verify user identities. One critical aspect of user authentication is ensuring that users do not create multiple accounts using the same email address. In this blog, we will explore how to use Node.js and MySQL to check if an email address already exists in a database.

Create Working Directory


First we have to make Node.js working envrionment, so first we have goes to directory from where we have to run node.js application. So in command prompt we have to run following command.


f:
cd node
mkdir email_exists_or_not


So this command will first goes to F: drive and then after it will goes into node directory and after this it will create email_exists_or_not directory in which we will make node.js application for check email address exists or not in MySQL database.

Next we want to download Node.js Express module, so in command prompt we have to run following command.


npm install express


So this command will download Node.js Express module under this email_exists_or_not directory. Now we can open this email_exists_or_not directory in text editor for create Node.js Email Exists or not with MySQL Database.


How to check if an email address already exists with Node.js & MySQL


MySQL database Structure


Below you can find MySQL database structure for create Node.js Email Exists or not Application with MySQL database.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, 'John Smith', 'john_smith@gmail.com', '$2y$10$8am2/JEN1ARvndVsgl.YvegRpbrPZuhG0nA79k8sYNk5RdDNKNcS2', 'ksxo8HGQLFpUenlAFKfTfWMqk2cJuDhqrnoBa6ogZBf1ZbRwkNzAxE4AFn7o', '2018-03-05 00:29:44', '2018-03-05 00:29:44');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;


After this under email_exists_or_not directory, we have to create index.html file and under this file we have to create one HTML form for get email address details from user and after this we have to write JavaScript for send Ajax request to node.js application for check email exists or not in MySQL Database.

index.html - (Client-side (HTML with AJAX))

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Email Availability Checker</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">
        <h3>How to check if email address is already in use or not in Node.js ?</h3>
        <div class="mt-3 mb-3">
            <div class="card">
                <div class="card-header">Enter your eMail</div>
                <div class="card-body">
                    <form id="email-form">
                        <div class="mb-3">
                            <label for="email-input">Enmail Address</label>
                            <input type="email" id="email-input" class="form-control" />
                            <span id="email-message"></span>
                        </div>
                        <button type="submit" class="btn btn-primary" id="submit-btn">Check Email</button>
                    </form>
                </div>
            </div>
        </div>
    </div>
</body>
</html>
<script>

const form = document.querySelector('#email-form');

const message = document.querySelector('#email-message');

form.addEventListener('submit', (event) => {

    event.preventDefault();

    const email = document.querySelector('#email-input').value;

    if(email != '')
    {
        const xhr = new XMLHttpRequest();

        xhr.open('POST', 'http://localhost:3000/check-email', true);

        xhr.setRequestHeader('Content-Type', 'application/json');
        
        xhr.onload = () => {

            if(xhr.status === 200)
            {
                const response = JSON.parse(xhr.responseText);

                if(response.emailExists)
                {
                    message.innerHTML = '<span class="badge bg-danger">Email is already in use</span>';
                }
                else
                {
                    message.innerHTML = '<span class="badge bg-success">Email is available</span>';
                }
            }
            else
            {
                message.innerHTML = '<span class="badge bg-info">Error checking email availability</span>';
            }
        };

        xhr.onerror = () => {
            message.innerHTML = '<span class="badge bg-info">Error checking email availability</span>';
        };

        xhr.send(JSON.stringify({ email }));
    }
    else
    {
        message.innerHTML = '<span class="badge bg-info">Please Enter Email</span>';
    }

});

</script>





Below you can find description of JavaScript Code part.


const form = document.querySelector('#email-form');

This line of code selects the HTML form element with an id of email-form and stores it in the form variable.


const message = document.querySelector('#email-message');

This line of code selects the HTML element with an id of email-message and stores it in the message variable.


form.addEventListener('submit', (event) => {

});

This line of code adds a listener to the submit event of the form element. When the form is submitted, the anonymous function inside the parentheses will be executed.


event.preventDefault();

This line of code prevents the default behavior of the form when it is submitted. By default, the form would submit the data to a server and reload the page. This code prevents that behavior so that we can use AJAX to check if the email is already in use without reloading the page.


const email = document.querySelector('#email-input').value;

This line of code selects the HTML input element with an id of email-input, gets its value (the email entered by the user), and stores it in the email variable.


if(email != '')

This line of code checks if the email variable is not an empty string.


const xhr = new XMLHttpRequest();

This line of code creates a new instance of the XMLHttpRequest object, which allows us to make HTTP requests from the browser.


xhr.open('POST', 'http://localhost:3000/check-email', true);

This line of code initializes the AJAX request. We are sending a POST request to the URL http://localhost:3000/check-email, which is the endpoint that will check if the email is already in use. The true parameter specifies that the request should be asynchronous.


xhr.setRequestHeader('Content-Type', 'application/json');

This line of code sets the Content-Type header of the request to application/json, which tells the server that we are sending JSON data in the request body.


xhr.onload = () => {

This line of code sets the onload event handler for the AJAX request. When the request is complete and a response has been received, the anonymous function inside the parentheses will be executed.


if (xhr.status === 200) {

This line of code checks if the response status code is 200, which means that the request was successful.


const response = JSON.parse(xhr.responseText);

This line of code parses the response text from JSON format into a JavaScript object and stores it in the response variable.


if (response.emailExists) {

This line of code checks if the emailExists property of the response object is true, which means that the email is already in use.


xhr.send(JSON.stringify({ email }));

This lines of code will send send data to http://localhost:3000/check-email this url.

After this, we have to create app.js file and under this file, we have to write Node JS Code for check Email Already exists or not in MySQL database.

app.js (Server-side (Node.js with Express and MySQL)))

const express = require('express');

const mysql = require('mysql');

const cors = require('cors');

const app = express();

const port = 3000;

const connection = mysql.createConnection({
	host : 'localhost',
	user : 'root',
	password : '',
	database : 'testing'
});

connection.connect((error) => {
	if(error)
	{
		console.log('Error connecting to MySQL database:', error);
		return;
	}
	console.log('Connected to MySQL database');
});

app.use(cors());

app.use(express.json());

app.post('/check-email', (request, response) => {

	const email = request.body.email;

	const sql = 'SELECT COUNT(*) AS count FROM users WHERE email = ?';

	connection.query(sql, [email], (error, results) => {

		const count = results[0].count;

		const emailExists = count === 1;

		response.json({ emailExists });

	});
});

app.listen(port, () => {
	console.log(`Server running on port ${port}`);
});


In this code, we create an Express app, set up a connection to a MySQL database, and define a route to check the email availability. The route expects a POST request with a JSON payload containing an email property. We use the mysql library to query the database and check if the email already exists in the users table. We then send a JSON response with a emailExists property that indicates whether the email is already in use or not.

Now for run above application, first we have to run app.js file. So we have goes to command prompt and run following command.


node app.js

This command will start Node.js server and for check output in the browser, we have to open index.html file in the browser.


file:///F:/node/email_exist_or_not/index.html

So now you can check email address has been exists or not in MySQL database by entering email address in textbox by using Node.js with MySQL database.

Conclusion


In this blog, we explored how to use Node.js and MySQL to check if an email address already exists in a database. We discussed the steps involved in setting up a MySQL database, installing the mysql module for Node.js, connecting to the MySQL database, and checking if an email address exists in the database. With these tools and techniques, you can implement a robust user authentication system that ensures each user has a unique email address.

Monday, 6 March 2023

Free PHP Project on Society Management System using PHP and MySQL


Introduction:


Managing a society can be a complex and challenging task, especially for large-scale societies with numerous members. Keeping track of various aspects such as member registration, event scheduling, fee management, complaint handling, and visitor management can be quite challenging for society administrators. Therefore, a comprehensive society management system can streamline the management process and make it more efficient. In this article, we will discuss a free PHP project on Society Management System using PHP and MySQL that can help manage society affairs more efficiently.

Free PHP Project on Society Management System using PHP and MySQL

Overview:


The Society Management System is a web-based application developed using PHP and MySQL, providing society administrators with a centralized platform to manage different society-related activities such as member registration, event management, fee management, complaint handling, visitor management, and notification management. The system is designed to handle all the necessary functions that a society management system requires. The system provides two different user types, i.e., administrator and member. The administrator has complete control over the system, while the member has access to their personal information and events.

Features:


Member Registration:


The system allows society administrators to register new members by capturing their personal details, contact information, and occupation. The system also generates a unique membership ID for each member.

Fee Management:


The system allows society administrators to manage membership fees, track payment history, and generate payment receipts.

Complaint Handling:


The system provides a complaint handling feature that allows members to register complaints related to society management. Society administrators can view and resolve complaints in the system.

Visitor Management:


The system allows society administrators to keep track of visitors entering and leaving the society. The system captures visitor details such as name, phone number, address, and person to meet.

Notification Management:


The system provides a notification management feature that allows society administrators to send notifications to members for different events, payments, and complaints.





Technology Stack:


The technology stack used to develop the Society Management System includes PHP, MySQL, HTML/CSS, and JavaScript. PHP is a popular server-side scripting language that is widely used for web application development. MySQL is an open-source relational database management system that is used to store and retrieve data. Bootstrap 5 is used to create the user interface of the system, while JavaScript is used to add interactivity to the system.

Conclusion:


In conclusion, the Society Management System is a comprehensive solution for managing society affairs. The system provides essential features such as member registration, event management, fee management, complaint handling, visitor management, and notification management. The use of PHP and MySQL as the technology stack provides a robust and scalable platform for future development. With the installation process being relatively easy, society administrators can get up and running in no time. The Society Management System can streamline the management process and make it more efficient, saving valuable time and resources.





Monday, 21 November 2022

School Fees Management System in PHP MySQL with Source Code

School Fees Management System in PHP MySQL with Source Code

Today, the education sector has become highly digitized. Students and parents expect seamless processes across all digital touchpoints, anytime, anywhere. In this scenario, schools need to come up with cost-effective solutions that not only keep their operating costs in check but also improve the experience of students and parents. Education institutes have multiple tuition fee collection channels such as offline cash payments, online payments through payment gateway, e-wallets and so on. However, this fragmented system creates a headache for school as well as parents whenever a student get fees details. On the other hand, an efficient school fees management system can help you collect monthly fees from students conveniently. Moreover, it will also save you time and money by automating fee collection and tracking procedures at every step. So In this blog post, we will discuss - PHP based School Management System.

Why Should You Use an Efficient School Fees Management System?


Schools find it difficult to manage fees across multiple channels, especially when students are attending courses from different institutes. Also, they lack a robust system that can help them track payments, understand cash flow, and manage fees across different channels. This can lead to inefficient fee management, which can affect your business in many ways. For instance, you may have difficulty in tracking and collecting school fees from students belonging to different channels. This can also lead to more manual errors and a lower level of customer satisfaction.

How Can an Efficient School Fees Management System Help?


A well-crafted school fees management system can help you collect fees from students at different channels. It can also help you manage fees and payments at all stages. You can use this system to collect payments from your existing students. You can also use the same platform to enroll new students and collect the fees on a monthly basis. Investing in a robust system will help you improve the overall experience of your fees collection and manage fees data. This will also help you in gathering valuable insight from the data that the system generates. You can also use this system to generate reports, and get insights about the number of students registered with your school, the total amount collected, and so on. This will help you understand your business performance, and take necessary steps for improvement.





PHP Based School Fee Management System


Fees management is yet another important role of the school management system. This is the one place where you can track the student or course fees details. If you are managing your fees data through a web application, you can connect it with your school management software to access the data. The Fees management will help you track the fees collection by different date filter. This will let you know where you need to focus on and how many fees collection has be increased. You can also generate reports based on the data gathered by the fees management module. This will let you know the total amount of fees.





Key Features of an Efficient School Fees Payment Platform


To ensure that your school or institution student get a seamless experience, you need to ensure that your school fees payment platform is efficient. Some of the key features of an efficient school fees payment platform include - An easy to use interface that allows users to collect fees in simple easy steps with few click. A robust system with safety and security features to protect your fees data from cyber attacks. A detailed user dashboard that allows users to manage their account and payment history.

  1. Multi User Fees Management System
  2. Single Login for Master and Sub User
  3. Fully Dynamic Fees Management System
  4. Admin can Add, Update, Disable and Enable Sub User
  5. Admin can Add, Update, Disable and Enable Academic Year Data
  6. Admin can Add, Update, Disable and Enable Course Data
  7. Admin can Add, Update Student Master Data
  8. Admin can define Student Standard and then after update, disable and enable that data
  9. Admin can Add, Update, Disable and Enable Fees Master Data
  10. Admin or Sub User can collect fees
  11. Fees Receipts can be open in PDF format also
  12. Admin or Sub User can filter fees collection data and get data of how many student has paid and how many student are still pending of fees

Web Technology used for Fees Management System


For Develop Web based Fees Management, we have use following open source web technology for build School Fees Management System.

Server Side


  • PHP
  • MySQL

Client Side


  • Vanill JavaScript
  • jQuery
  • Ajax
  • Bootstrap 5
  • jQuery DataTables




Database Structure of School Fees Management System


Below you can find database structure of PHP based School Management System.


Database Structure of School Fees Management System


Conclusion


The education sector is highly competitive, and businesses have to keep up with the latest technology to stay relevant. With growing use of internet and mobile devices, customers expect seamless experiences across all digital touchpoints. A school management system can help you come up with cost-effective solutions that not only keep your operating costs in check but also improve the experience of students and parents. If you are looking for an efficient school fees management system, you can consider PHP-based school fee management systems. These systems offer robust features, and you can use them to manage fees and payments at different stages.

Note - Source code will be added under this page after every publishing of video tutorial of this Fees Management System.