Saturday 2 December 2023

Building a React.js CRUD Application with Vite, PHP API and MySQL

Building a React.js CRUD Application with Vite, PHP API, and MySQL


In this tutorial, we'll walk through the process of creating a full-stack CRUD (Create, Read, Update, Delete) application using React.js, Vite for the frontend, PHP for the backend, and MySQL as the database. This project will help you understand how to set up a modern development environment and integrate the different components seamlessly.

Prerequisites


Before getting started, make sure you have the following tools installed:

  • Node.js
  • npm (Node Package Manager)
  • PHP
  • Composer
  • MySQL

Steps for Create PHP React CRUD Application


  1. Installation
  2. Set Router for Fetch Data
  3. Create Fetch Data API
  4. Make Insert Data Component & Set Route
  5. Submit Form Data
  6. Set Router for Edit Data Component
  7. Make Update Data API
  8. Create Delete Data API

Step 1 - Installation


Here we have make CRUD Application by using React.js with PHP API. So first we want to download and install React.js on our local computer. So here we have use Vite tool and it is a front end tool which is used for building fast and optimized web application.

Now we want to download React Framework by using Vite. So first we have goes to command prompt and And here we have goes into directory from which we can run PHP application and for download and install React.js we have to run following command in terminal.


npm create vite@latest phpreactcrud


When you have run this command then it will create phpreactcrud directory and then after it will display JavaScript framework and from this list of framework we have to select React framework by pressing down key. And after selecting React and press enter then it has again ask for selecting JavaScript variant. So by pressing down key we have to select JavaScript and press enter.

After this, we have goes into phpreactcrud directory by run following command.


cd phpreactcrud


Inside your project directory, you'll need to install the project's dependencies using npm:


npm install


So after run this command it will start download React framework in phpreactcrud directory. Once download is complete, so we need to check React is properly install or not. So we have need to run following command.


npm run dev


When we have run this command then it will start React development server and provide us base url of our React Application. So from this url we can open React Application in the browser.

If React web page open in browser that means React is properly installed in our computer.

Directory Structure of React

Below you can find directory structure of React.js framework.


Directory Structure of React.js CRUD Application


Step 2 - Set Router for Fetch Data


This is second step for create PHP React.js CRUD application and under this step we have to set Router for Fetch Data. But before set Router for fetch data, first we have to install Bootstrap 5 library under this React CRUD Application.

So To use Bootstrap 5 in your React application, you need to install the Bootstrap package from npm. Bootstrap 5 comes with Sass, so you can take advantage of its customization features. Run the following command to install Bootstrap:


npm install bootstrap


So this command will download and install Bootstrap 5 library under this our React.js Application. Next we have to use Bootstrap library under this React CRUD Application.

So we have to open src/App.jsx file and for import the Bootstrap CSS at the top of the file:


import 'bootstrap/dist/css/bootstrap.min.css';


Next for display user data, we have to create Component/Userlist.jsx file and under this file, we want to import React, so we have to write following statement under this file.


import React from 'react';


So it will import React dependencies under this file. After this we have create React component for this Userlist by writing following code.

Component/Userlist.jsx

function Userlist(){
	return (
		<div className="card">
			<div className="card-header">
				<div className="row">
					<div className="col-md-6"><b>User Data</b></div>
					<div className="col-md-6">

					</div>
				</div>
			</div>
			<div className="card-body">
				<table className="table table-bordered">
					<thead>
						<tr>
							<th>First Name</th>
							<th>Last Name</th>
							<th>Email</th>
							<th>Action</th>
						</tr>
					</thead>
					<tbody>
					
					</tbody>
				</table>
			</div>
		</div>
	);
}


And for export this component, we have to write following code at the end of this file. So after write this code we can import this component in other file also.


export default Userlist;


Next we have open App.jsx file and under this file we have to first import React router dom library. So we have goes to command prompt and run following command. This command will download and install React router dom library under this App.jsx file.


npm install react-router-dom


After installing this library we can able to import React router dom library component like BrowserRouter, Routes, Route, Link by writing following statement under App.jsx file.


import { BrowserRouter, Routes, Route, Link } from 'react-router-dom';


BrowserRouter - Here BrowserRouter component is represents the router for your application, And it is the key component from react router dom, which provided routing functionality to react application.

Routes - Routes component is typically used to define collection of routes.

Route - Route component is used to define, individual route in your application.

Link - Link component is used for creating hyperlink, that navigate to different parts of your application.

Next we want to import Component/Userlist.jsx file under this App.jsx file. So we have to add following statement under App.jsx file.


import Userlist from './Component/Userlist';


After import React router dom library and Userlist component, in below code you can find how to define route for fetch data.

src/App.jsx

import { useState } from 'react';
import 'bootstrap/dist/css/bootstrap.min.css';
import { BrowserRouter, Routes, Route, Link } from 'react-router-dom';
import Userlist from './Component/Userlist';

function App() {
    return(
        <div className="container">
            <h1 className="mt-5 mb-5 text-center"><b>PHP React.js CRUD Application - <span className="text-primary">Create Delete Data API - 8</span></b></h1>
            <BrowserRouter>
                <Routes>
                    <Route path="/" element={<Userlist />} />
                </Routes>
            </BrowserRouter>
        </div>
    )
}

export default App



src/Component/Userlist.jsx

import React from 'react';

function Userlist(){

	return (
		<div className="card">
			<div className="card-header">
				<div className="row">
					<div className="col-md-6"><b>User Data</b></div>
					<div className="col-md-6">
						
					</div>
				</div>
			</div>
			<div className="card-body">
				<table className="table table-bordered">
					<thead>
						<tr>
							<th>First Name</th>
							<th>Last Name</th>
							<th>Email</th>
							<th>Action</th>
						</tr>
					</thead>
					<tbody>
					
					</tbody>
				</table>
			</div>
		</div>
	);
}

export default Userlist;




Step 3 - Create Fetch Data API


After set Router for Fetch Data, now we want to make PHP API for fetch data from MySQL table. But before this we have to write JavaScript under Component/Userlist.jsx file for send fetch data request to PHP API.

Source Code of Create Fetch Data API

src/Component/Userlist.jsx

import React, { useEffect, useState } from 'react';

function Userlist(){
	const [users, setUsers] = useState([]);

	useEffect(() => {
		const apiUrl = 'http://localhost/tutorial/phpreactcrud/api/action.php'; //This URL change according to path of your PHP Script

		fetch(apiUrl)
		.then((response) => response.json())
		.then((data) => {
			setUsers(data);
		});

	}, []);

	return (
		<div className="card">
			<div className="card-header">
				<div className="row">
					<div className="col-md-6"><b>User Data</b></div>
					<div className="col-md-6">
						
					</div>
				</div>
			</div>
			<div className="card-body">
				<table className="table table-bordered">
					<thead>
						<tr>
							<th>First Name</th>
							<th>Last Name</th>
							<th>Email</th>
							<th>Action</th>
						</tr>
					</thead>
					<tbody>
					{users.map((user, index) => (
						<tr key={index}>
							<td>{user.first_name}</td>
							<td>{user.last_name}</td>
							<td>{user.email}</td>
							<td>
								
							</td>
						</tr>
					))}
					</tbody>
				</table>
			</div>
		</div>
	);
}

export default Userlist;


1 - Import Statements:


import React, { useEffect, useState } from 'react';


  • The code imports the necessary functionalities from the react library.
  • useEffect is a hook used for side effects in functional components, and useState is a hook for managing state.

2 - Component Definition:


function Userlist(){


  • The functional component named Userlist is defined.

3 - State Initialization:


const [users, setUsers] = useState([]);


  • This line initializes a state variable users using the useState hook. The setUsers function is used to update the state.

4 - Data Fetching using useEffect:


useEffect(() => {
    const apiUrl = 'http://localhost/tutorial/phpreactcrud/api/action.php';

    fetch(apiUrl)
    .then((response) => response.json())
    .then((data) => {
        setUsers(data);
    });

}, []);


  • The useEffect hook is used to perform side effects in functional components. In this case, it fetches data from a specified API endpoint when the component mounts ([] as the dependency array means it runs once when the component mounts).
  • The data fetched is expected to be in JSON format, and it updates the users state with the received data.

5 - Rendering JSX:


return (
    <div className="card">
        {/* ... */}
    </div>
);


  • The return statement contains JSX code that represents the structure of the component.

6 - JSX Structure:

  • The component renders a Bootstrap-styled card with a header and body.
  • The body contains a table displaying user data with columns for "First Name," "Last Name," "Email," and an "Action" column.

7 - Mapping Through User Data:


{users.map((user, index) => (
    <tr key={index}>
        <td>{user.first_name}</td>
        <td>{user.last_name}</td>
        <td>{user.email}</td>
        <td>
            {/* Action content */}
        </td>
    </tr>
))}


  • The component maps through the users array and renders a table row (</tr>) for each user.
  • User details such as first name, last name, and email are displayed in the corresponding columns.

8 - Export Statement:


export default Userlist;


  • The Userlist component is exported as the default export of this module, making it available for use in other parts of the application.
api/action.php

<?php

header("Access-Control-Allow-Origin:* ");

header("Access-Control-Allow-Headers:* ");

header("Access-Control-Allow-Methods:* ");

$connect = new PDO("mysql:host=127.0.0.1;dbname=testing", "root", "password");

$method = $_SERVER['REQUEST_METHOD']; //return GET, POST, PUT, DELETE

if($method === 'GET')
{
	//fetch all user

		$query = "SELECT * FROM sample_users ORDER BY id DESC";

		$result = $connect->query($query, PDO::FETCH_ASSOC);

		$data = array();

		foreach($result as $row)
		{
			$data[] = $row;
		}

		echo json_encode($data);
	
}


?>


This PHP code is designed to handle HTTP requests, specifically for the GET method, and it interacts with a MySQL database to retrieve data. Let's break down the code:

1 - Cross-Origin Resource Sharing (CORS) Headers:


header("Access-Control-Allow-Origin:* ");
header("Access-Control-Allow-Headers:* ");
header("Access-Control-Allow-Methods:* ");


  • These lines set up CORS headers, allowing cross-origin requests from any origin (*). CORS headers are necessary for web applications hosted on one domain to make requests to a different domain.

2 - Database Connection:


$connect = new PDO("mysql:host=127.0.0.1;dbname=testing", "root", "password");


  • It establishes a connection to a MySQL database named "testing" on the local server (127.0.0.1). The username is "root," and the password is "password." This connection is created using the PDO (PHP Data Objects) extension.

3 - Request Method Check:


$method = $_SERVER['REQUEST_METHOD'];


  • It retrieves the HTTP request method (GET, POST, PUT, DELETE) from the $_SERVER superglobal.

4 - Handling GET Requests:


if ($method === 'GET') {
    //fetch all user
    $query = "SELECT * FROM sample_users ORDER BY id DESC";
    $result = $connect->query($query, PDO::FETCH_ASSOC);
    
    $data = array();
    foreach ($result as $row) {
        $data[] = $row;
    }
    echo json_encode($data);
}


  • If the request method is GET, the code executes a SELECT query to retrieve all records from the "sample_users" table, ordering them by the "id" column in descending order.
  • The result of the query is fetched using the query method, and the data is extracted using a foreach loop.
  • The retrieved data is then encoded as JSON using json_encode and echoed back to the client.

This PHP code handles GET requests, connects to a MySQL database, retrieves all records from a specific table, and returns the data in JSON format with appropriate CORS headers to allow cross-origin requests.




Step 4 - Make Insert Data Component & Set Route


Once we have fetch data from MySQL database and display on web page under this React.js CRUD Application. Next We will create insert data component in which we will create Add user data form, and then after we will set router of that insert data component under this React.js PHP CRUD Application.

src/Component/Add.jsx

import React, { useState } from 'react';
import { Link, useNavigate } from 'react-router-dom';

function Add(){

	let navigate = useNavigate();

	const [user, setUser] = useState({
		first_name : '',
		last_name : '',
		email : ''
	});

	const handleChange = (event) => {
		const { name, value } = event.target;

		setUser({
			...user,
			[name] : value
		});
	};

	return (
		<div className="card">
			<div className="card-header">
				<div className="row">
					<div className="col-md-6">Add User</div>
					<div className="col-md-6">
						<Link to="/" className="btn btn-success btn-sm float-end">View All</Link>
					</div>
				</div>
			</div>
			<div className="card-body">
				<div className="row">
					<div className="col-md-4">&nbsp;</div>
					<div className="col-md-4">
						<form method="POST">
							<div className="mb-3">
								<label>First Name</label>
								<input type="text" name="first_name" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<label>Last Name</label>
								<input type="text" name="last_name" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<label>Email</label>
								<input type="email" name="email" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<input type="submit" className="btn btn-primary" value="Add" />
							</div>
						</form>
					</div>
				</div>
			</div>
		</div>
	);
}

export default Add;


This React.js code defines a functional component called Add that represents a form for adding a new user. It uses the useNavigate hook from react-router-dom to programmatically navigate between different views. Let's break down the code:

1 - Import Statements:


import React, { useState } from 'react';
import { Link, useNavigate } from 'react-router-dom';


  • The code imports the necessary functionalities from the react library, including the useState hook.
  • It also imports Link and useNavigate from react-router-dom. Link is used to create navigation links, and useNavigate is a hook used for programmatic navigation.

2 - Component Definition:


function Add(){


  • The functional component named Add is defined.

3 - Navigation Setup:


let navigate = useNavigate();


  • The useNavigate hook is used to get the navigate function, which can be used to navigate between different views in a React application.

4 - State Initialization:


const [user, setUser] = useState({
    first_name : '',
    last_name : '',
    email : ''
});


  • This line initializes a state variable user using the useState hook. The state represents the user data with properties first_name, last_name, and email.

5 - Event Handler Function:


const handleChange = (event) => {
    const { name, value } = event.target;

    setUser({
        ...user,
        [name] : value
    });
};


  • The handleChange function is an event handler for input changes. It uses object destructuring to extract the name and value from the changed input element.
  • The setUser function is then used to update the user state by spreading the existing state and updating the property corresponding to the changed input.

6 - Rendering JSX:


return (
    <div className="card">
        {/* ... */}
    </div>
);


  • The return statement contains JSX code that represents the structure of the component.

7 - JSX Structure:

  • The component renders a Bootstrap-styled card with a header and body.
  • The body contains a form with input fields for "First Name," "Last Name," and "Email," along with a submit button labeled "Add."

8 - Navigation Link:


<Link to="/" className="btn btn-success btn-sm float-end">View All</Link>


  • This is a Link component that creates a link to the root path ("/"). It's styled as a Bootstrap button and is labeled "View All."
  • Clicking on this link will navigate the user to the specified path.

9 - Form Input Fields:


<input type="text" name="first_name" className="form-control" onChange={handleChange} />
<input type="text" name="last_name" className="form-control" onChange={handleChange} />
<input type="email" name="email" className="form-control" onChange={handleChange} />


  • These input fields are controlled components, meaning their values are controlled by the state (user).
  • The onChange event is set to the handleChange function, ensuring that any changes to the input fields update the state.

10 - Submit Button:


<input type="submit" className="btn btn-primary" value="Add" />


  • This is a submit button for the form. It triggers the submission of the form data.

11 - Export Statement:


export default Add;


  • The Add component is exported as the default export of this module, making it available for use in other parts of the application.

This component provides a form for adding a new user. It captures input changes, updates the component state accordingly, and allows for the submission of the form data.

After creating this src/Component/Add.jsx file, now we want to import in src/App.jsx file.

src/App.jsx

import { useState } from 'react'
import 'bootstrap/dist/css/bootstrap.min.css';
import { BrowserRouter, Routes, Route, Link } from 'react-router-dom';
import Userlist from './Component/Userlist';
import Add from './Component/Add';

function App() {
    return(
        <div className="container">
            <h1 className="mt-5 mb-5 text-center"><b>PHP React.js CRUD Application - <span className="text-primary">Create Delete Data API - 8</span></b></h1>
            <BrowserRouter>
                <Routes>
                    <Route path="/" element={<Userlist />} />
                    <Route path="/add" element={<Add />} />
                </Routes>
            </BrowserRouter>
        </div>
    )
}

export default App



1 - Import Statements:


import Add from './Component/Add';


  • Component Add are imported from separate files.

2 - React Router Setup:


<BrowserRouter>
    <Routes>
        /* ... */
        <Route path="/add" element={<Add />} />
    </Routes>
</BrowserRouter>


  • The BrowserRouter component from react-router-dom is used to set up routing for the application.
  • Inside it, the Routes component defines different routes using the Route component. Two routes are defined:
    • The path /add renders the Add component.

So this way we can create Add user form component and import into App.jsx file.




Step 5 - Submit Form Data


Now you can move to How to submit form data by using React handle submit function and then after you will learn how to make PHP API for Insert Form data under this React CRUD Application.

src/Component/Add.jsx

import React, { useState } from 'react';
import { Link, useNavigate } from 'react-router-dom';

function Add(){

	let navigate = useNavigate();

	const [user, setUser] = useState({
		first_name : '',
		last_name : '',
		email : ''
	});

	const handleChange = (event) => {
		const { name, value } = event.target;

		setUser({
			...user,
			[name] : value
		});
	};

	const handleSubmit = (event) => {

		event.preventDefault();

		fetch('http://localhost/tutorial/phpreactcrud/api/action.php', {
			method : 'POST',
			headers : {
				'Content-Type' : 'application/json'
			},
			body : JSON.stringify(user)
		})
		.then((response) => response.json())
		.then((data) => {
			navigate("/");
		})

	};

	return (
		<div className="card">
			<div className="card-header">
				<div className="row">
					<div className="col-md-6">Add User</div>
					<div className="col-md-6">
						<Link to="/" className="btn btn-success btn-sm float-end">View All</Link>
					</div>
				</div>
			</div>
			<div className="card-body">
				<div className="row">
					<div className="col-md-4">&nbsp;</div>
					<div className="col-md-4">
						<form method="POST" onSubmit={handleSubmit}>
							<div className="mb-3">
								<label>First Name</label>
								<input type="text" name="first_name" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<label>Last Name</label>
								<input type="text" name="last_name" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<label>Email</label>
								<input type="email" name="email" className="form-control" onChange={handleChange} />
							</div>
							<div className="mb-3">
								<input type="submit" className="btn btn-primary" value="Add" />
							</div>
						</form>
					</div>
				</div>
			</div>
		</div>
	);
}

export default Add;


1 - Event Handler Function (handleSubmit):


const handleSubmit = (event) => {
    event.preventDefault();

    fetch('http://localhost/tutorial/phpreactcrud/api/action.php', {
        method : 'POST',
        headers : {
            'Content-Type' : 'application/json'
        },
        body : JSON.stringify(user)
    })
    .then((response) => response.json())
    .then((data) => {
        navigate("/");
    });
};


  • The handleSubmit function is an event handler for form submission. It prevents the default form submission behavior using event.preventDefault().
  • It then makes a POST request to the specified API endpoint ('http://localhost/tutorial/phpreactcrud/api/action.php') with the user data in JSON format.
  • Upon successful submission, it navigates the user back to the root path ("/") using the navigate function.

2 - Form onSubmit Event Handler:


<form method="POST" onSubmit={handleSubmit}>


  • The onSubmit attribute is set to the handleSubmit function.
  • This means that when the form is submitted (either by clicking a submit button or pressing Enter within a form field), the handleSubmit function will be called.
api/action.php

<?php

header("Access-Control-Allow-Origin:* ");

header("Access-Control-Allow-Headers:* ");

header("Access-Control-Allow-Methods:* ");

$connect = new PDO("mysql:host=127.0.0.1;dbname=testing", "root", "password");

$method = $_SERVER['REQUEST_METHOD']; //return GET, POST, PUT, DELETE

if($method === 'GET')
{	
	//fetch all user

	$query = "SELECT * FROM sample_users ORDER BY id DESC";

	$result = $connect->query($query, PDO::FETCH_ASSOC);

	$data = array();

	foreach($result as $row)
	{
		$data[] = $row;
	}

	echo json_encode($data);
}

if($method === 'POST')
{
	$form_data = json_decode(file_get_contents('php://input'));

	$data = array(
		':first_name'		=>	$form_data->first_name,
		':last_name'		=>	$form_data->last_name,
		':email'			=>	$form_data->email
	);

	$query = "
	INSERT INTO sample_users (first_name, last_name, email) VALUES (:first_name, :last_name, :email);
	";

	$statement = $connect->prepare($query);

	$statement->execute($data);

	echo json_encode(["success" => "done"]);
}

?>


This PHP code is a simple server-side script that handles HTTP requests, specifically for the POST method. It interacts with a MySQL database to insert data submitted through a JSON-encoded payload.


if($method === 'POST') {
    // Process POST data

    $form_data = json_decode(file_get_contents('php://input'));

    $data = array(
        ':first_name'		=>	$form_data->first_name,
        ':last_name'		=>	$form_data->last_name,
        ':email'			=>	$form_data->email
    );

    $query = "
    INSERT INTO sample_users (first_name, last_name, email) VALUES (:first_name, :last_name, :email);
    ";

    $statement = $connect->prepare($query);

    $statement->execute($data);

    echo json_encode(["success" => "done"]);
}


  • If the request method is POST, the code proceeds to handle the incoming data.
  • It uses json_decode to parse the JSON data received from the client via the php://input stream.
  • The parsed data is then used to construct an associative array $data with keys corresponding to database columns.
  • A SQL query is prepared to insert the data into the "sample_users" table.
  • The query is executed using the prepared statement, and a success message is echoed back as a JSON response.

In summary, this PHP script checks for incoming POST requests, parses the JSON data, inserts it into a MySQL database, and responds with a JSON success message.

Remaining tutorial will be published very soon.




Conclusion


Congratulations! You've successfully built a full-stack CRUD application using React.js, Vite, PHP, and MySQL. This project provides a solid foundation for more complex applications, and you can further enhance it by adding features like authentication, error handling, and improved user interfaces.

Remember to follow best practices for security, such as validating and sanitizing user input on the server side, securing your database connections, and implementing proper authentication mechanisms. Happy coding!

Saturday 7 October 2023

A Comprehensive Guide to Resetting the Root Password in MySQL 8.0

A Comprehensive Guide to Resetting the Root Password in MySQL 8.0


In the unfortunate event of forgetting or misplacing your MySQL root password, it becomes crucial to regain access to your database. The root password is securely stored in the users table, necessitating a method to bypass MySQL authentication and update the password record.

Fortunately, there's a straightforward solution, and this tutorial will walk you through the process of recovering or resetting the root password in MySQL 8.0.

As per the official MySQL documentation, there are two primary methods to reset the root MySQL password, both of which we will cover in detail.

Method 1: Reset MySQL Root Password Using --init-file


One approach to reset the root password involves creating a local file and initiating the MySQL service with the --init-file option. Follow these steps:

1. Create a file, for example, /home/user/init-file.txt, and ensure it's readable by the MySQL user.

2. Within the file, insert the following command, replacing 'new_password' with your desired password:


ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';


3. Stop the MySQL service:


# systemctl stop mysqld.service     # for systems using systemd 
# /etc/init.d/mysqld stop           # for systems using init


4. Start the MySQL service with the following command:


# mysqld --user=mysql --init-file=/home/user/init-file.txt --console


5. The MySQL service will start, and the init-file you created will execute, updating the root user's password. Be sure to delete the file once the password has been reset.

6. Stop the server and restart it normally:


# systemctl stop mysqld.service        # for systems using systemd 
# systemctl restart mysqld.service     # for systems using systemd 

# /etc/init.d/mysqld stop              # for systems using init
# /etc/init.d/mysqld restart           # for systems using init


You should now be able to connect to the MySQL server as root using the new password:


# mysql -u root -p


Method 2: Reset MySQL Root Password Using --skip-grant-tables


The second method involves starting the MySQL service with the --skip-grant-tables option. This approach is less secure, as it allows all users to connect without a password while the service is running in this mode. However, it can be useful in certain situations. Follow these steps:

1. Ensure the MySQL service is stopped:


# systemctl stop mysqld.service     # for systems using systemd 
# /etc/init.d/mysqld stop           # for systems using init


2. Start the MySQL service with the --skip-grant-tables option:


# mysqld --skip-grant-tables --user=mysql &


3. Connect to the MySQL server:


# mysql


4. Since account management is disabled with --skip-grant-tables, you must reload the privileges:


# FLUSH PRIVILEGES;





5. Update the root password with the following command, replacing 'new_password' with your desired password:


# ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';


6. Stop the MySQL server and start it normally:


# systemctl stop mysqld.service        # for systems using systemd 
# systemctl restart mysqld.service     # for systems using systemd 

# /etc/init.d/mysqld stop              # for systems using init
# /etc/init.d/mysqld restart           # for systems using init


You should now be able to connect with your new password:


# mysql -u root -p


Conclusion


In this comprehensive guide, you've learned how to reset the root password for your MySQL 8.0 server. We hope this step-by-step process has made it easy for you to regain control of your database.

Display Dynamic MySQL Data in Bootstrap 5 Offcanvas Using Node.js: A Step-by-Step Guide


Introduction


In the world of web development, creating dynamic and interactive user interfaces is essential. Bootstrap 5, a popular front-end framework, provides the tools to do just that. Node.js, a server-side runtime, allows us to handle server logic efficiently. In this step-by-step guide, we will combine the power of Bootstrap 5 and Node.js to display dynamic data from a MySQL database within a Bootstrap 5 Offcanvas component.

Prerequisites:


  • Basic knowledge of HTML, CSS, and JavaScript
  • Node.js and npm (Node Package Manager) installed on your system
  • A MySQL database with some sample data

Let's dive into the process of building a dynamic web application that fetches data from a MySQL database and presents it beautifully in a Bootstrap 5 Offcanvas.


Display Dynamic MySQL Data in Bootstrap 5 Offcanvas Using Node.js: A Step-by-Step Guide


Table of Contents


  1. Setting Up Your Development Environment
  2. Creating the MySQL Database
  3. Building the Node.js Server
  4. Fetching Data from the MySQL Database
  5. Creating the Bootstrap 5 Offcanvas
  6. Displaying Dynamic Data in the Offcanvas
  7. Testing and Troubleshooting
  8. Conclusion and Next Steps

1. Setting Up Your Development Environment


Before we start coding, let's ensure that our development environment is properly configured. We'll need Node.js, npm, and any code editor of your choice. Once you have these in place, we can proceed to create our MySQL database.

So for Setting Up Development Environment, first we have goes to terminal and goes into our working directory and run this command which will download and install Express framework and Mysql2 Node.js module.


npm install express mysql2


2. Creating the MySQL Database


We'll design a MySQL database to store the data we want to display on our website. We'll cover database schema, table creation, and data insertion. So by executing below .sql script will create tbl_employee table with sample data in your MySQL database.


--
-- Table structure for table `tbl_employee`
--

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `gender` varchar(10) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `images` varchar(150) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_employee`
--

INSERT INTO `tbl_employee` (`id`, `name`, `address`, `gender`, `designation`, `age`, `images`) VALUES
(7, 'Antonio J. Forbes', '403 Snyder Avenue\r\nCharlotte, NC 28208', 'Male', 'Faller', 28, 'image_36.jpg'),
(8, 'Charles D. Horst', '1636 Walnut Hill Drive\r\nCincinnati, OH 45202', 'Male', 'Financial investigator', 29, 'image_37.jpg'),
(174, 'Martha B. Tomlinson', '4005 Bird Spring Lane, Houston, TX 77002', 'Female', 'Systems programmer', 28, 'image_44.jpg'),
(162, 'Jarrod D. Jones', '3827 Bingamon Road, Garfield Heights, OH 44125', 'Male', 'Manpower development advisor', 24, 'image_3.jpg'),
(192, 'Flora Reed', '4000 Hamilton Drive Cambridge, MD 21613', 'Female', 'Machine offbearer', 27, 'image_41.jpg'),
(193, 'Donna Parker', '4781 Apple Lane Peoria, IL 61602', 'Female', 'Machine operator', 26, '15900.jpg'),
(194, 'William Lewter', '168 Little Acres Lane Decatur, IL 62526', 'Male', 'Process engineer', 25, 'image_46.jpg'),
(195, 'Nathaniel Leger', '3200 Harley Brook Lane Meadville, PA 16335', 'Male', 'Nurse', 21, 'image_34.jpg'),
(183, 'Steve John', '108, Vile Parle, CL', 'Male', 'Software Engineer', 29, 'image_47.jpg');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=212;


3. Building the Node.js Server


In this section, we'll set up a Node.js server using Express.js, a popular Node.js framework, and configure it to communicate with our MySQL database.

server.js

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const path = require('path');

const app = express();

//app.use(bodyParser.urlencoded({ extended :true }));

// Configure body parser to handle JSON data
app.use(bodyParser.json());

// Serve static files from the "images" folder
app.use(express.static(path.join(__dirname, 'images')));

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

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


This code will create node server and include required dependencies and then after we have make MySQL database connection also.

4. Fetching Data from the MySQL Database


Learn how to use Node.js to retrieve data from the MySQL database and prepare it for display.

So first we have to create data.html and under this, we have to create HTML table structure and then after we have to write JavaScript code for fetch data from MySQL table and display on web page in tabular format.

data.html

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

        <title>Node.js Display Dynamic MySQL Data in Bootstrap 5 Offcanvas</title>
    </head>
    <body>
        <div class="container">
            <h1 class="text-primary mt-5 text-center">Node.js Display Dynamic MySQL Data in Bootstrap 5 Offcanvas</h1>
            <div class="card mt-5">
                <div class="card-header">
                    <b>Node.js Display Dynamic MySQL Data in Bootstrap 5 Offcanvas</b>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-bordered table-hover">
                            <thead>
                                <tr>
                                    <th>Employee Name</th>
                                    <th>Position</th>
                                </tr>
                            </thead>
                            <tbody id="employee_table"></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

    </body>
</html>

<script>

    loadData();

    function loadData(id = ''){
        const request = {
            method : 'POST',
            headers : {
                'Content-Type': 'application/json'
            },
            body : JSON.stringify({ id })
        };

        fetch('/fetchData', request)
        .then((response) => {
            return response.json();
        })
        .then((data) => {
            let html = '';
            if(id === ''){
                data.result.map((row) => {
                    html += `
                    <tr onclick="loadData('${row.id}');" style="cursor:pointer">
                        <td>${row.name}</td>
                        <td>${row.designation}</td>
                    </tr>
                    `;
                });

                const tableBody = document.querySelector('#employee_table');

                tableBody.innerHTML = html;
            }
        })
    }

</script>


server.js

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/data.html');
});

app.post('/fetchData', (request, response) => {
	const id = request.body.id;

	let query;

	if(id === ''){
		query = `SELECT id, name, designation FROM tbl_employee ORDER BY id DESC`;
	} else {
		query = `SELECT * FROM tbl_employee WHERE id = ${id}`;
	}

	connection.query(query, (error, result) => {
		response.json({result});
	});
});

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});


5. Creating the Bootstrap 5 Offcanvas


Here, we'll dive into the front-end development aspect and create a Bootstrap 5 Offcanvas component that will elegantly slide in and out to display our dynamic data.

data.html

<div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvas_component" style="width: 250px;">
            <div class="offcanvas-header">
                <h5 class="offcanvas-title">Employee Details</h5>
                <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
            </div>
            <div class="offcanvas-body"></div>
        </div>


6. Displaying Dynamic Data in the Offcanvas


We'll integrate the data fetched from the database into our Bootstrap 5 Offcanvas, making it interactive and user-friendly.

data.html

<script>

    let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);

    loadData();

    function loadData(id = ''){
        const request = {
            method : 'POST',
            headers : {
                'Content-Type': 'application/json'
            },
            body : JSON.stringify({ id })
        };

        fetch('/fetchData', request)
        .then((response) => {
            return response.json();
        })
        .then((data) => {
            let html = '';
            if(id === ''){
                data.result.map((row) => {
                    html += `
                    <tr onclick="loadData('${row.id}');" style="cursor:pointer">
                        <td>${row.name}</td>
                        <td>${row.designation}</td>
                    </tr>
                    `;
                });

                const tableBody = document.querySelector('#employee_table');

                tableBody.innerHTML = html;
            } else {
                html += `<div class="card">`;
                data.result.map((row) => {
                    html += `
                    <img src="/${row.images}" class="rounded-circle" />
                    <div class="card-body">
                        <h5 class="card-title">${row.name}</h5>
                        <p class="card-text">${row.address}</p>
                    </div>
                    <ul class="list-group list-group-flush">
                        <li class="list-group-item"><b>Gender : </b>${row.gender}</li>
                        <li class="list-group-item"><b>Designation : </b>${row.designation}</li>
                        <li class="list-group-item"><b>Age : </b>${row.age}</li>
                    </ul>
                    `;
                });
                html += `</div>`;

                offcanvas_body.innerHTML = html;

                offcanvas.show();
            }
        })
    }

</script>





7. Testing and Troubleshooting


Let's test our application, identify and troubleshoot any issues that may arise, and ensure that everything works as expected.

So we have goes to terminal and run following command, which will start our node server.


node server.js


After run this command it will start node server and provide us base url of our node application.


http://localhost:3000/


So by open this command in the browser we can check our How to display dynamic MySQL data in Bootstrap 5 Offcanvas with Node.js Application.

8. Conclusion and Next Steps


In the final section, we'll recap what we've learned and discuss potential next steps, such as adding additional features or deploying the application to a live server.

By the end of this step-by-step guide, you'll have a fully functional web application that can display dynamic data from a MySQL database in a Bootstrap 5 Offcanvas using Node.js. Let's get started!

Complete Source Code


data.html

<script>

    let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);

    loadData();

    function loadData(id = ''){
        const request = {
            method : 'POST',
            headers : {
                'Content-Type': 'application/json'
            },
            body : JSON.stringify({ id })
        };

        fetch('/fetchData', request)
        .then((response) => {
            return response.json();
        })
        .then((data) => {
            let html = '';
            if(id === ''){
                data.result.map((row) => {
                    html += `
                    <tr onclick="loadData('${row.id}');" style="cursor:pointer">
                        <td>${row.name}</td>
                        <td>${row.designation}</td>
                    </tr>
                    `;
                });

                const tableBody = document.querySelector('#employee_table');

                tableBody.innerHTML = html;
            } else {
                html += `<div class="card">`;
                data.result.map((row) => {
                    html += `
                    <img src="/${row.images}" class="rounded-circle" />
                    <div class="card-body">
                        <h5 class="card-title">${row.name}</h5>
                        <p class="card-text">${row.address}</p>
                    </div>
                    <ul class="list-group list-group-flush">
                        <li class="list-group-item"><b>Gender : </b>${row.gender}</li>
                        <li class="list-group-item"><b>Designation : </b>${row.designation}</li>
                        <li class="list-group-item"><b>Age : </b>${row.age}</li>
                    </ul>
                    `;
                });
                html += `</div>`;

                offcanvas_body.innerHTML = html;

                offcanvas.show();
            }
        })
    }

</script>


server.js

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const path = require('path');

const app = express();

// Configure body parser to handle JSON data

app.use(bodyParser.json());

// Serve static files from the "images" folder
app.use(express.static(path.join(__dirname, 'images')));

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

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

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/data.html');
});

app.post('/fetchData', (request, response) => {
	const id = request.body.id;

	let query;

	if(id === ''){
		query = `SELECT id, name, designation FROM tbl_employee ORDER BY id DESC`;
	} else {
		query = `SELECT * FROM tbl_employee WHERE id = ${id}`;
	}

	connection.query(query, (error, result) => {
		response.json({result});
	});
});

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});

Friday 1 September 2023

Efficient Node.js CRUD App with MySQL: Enhanced by Bootstrap Offcanvas for a Seamless User Experience

Efficient Node.js CRUD App with MySQL: Enhanced by Bootstrap Offcanvas for a Seamless User Experience


In today's fast-paced world of web development, creating applications that can seamlessly manage data is a must. That's where CRUD (Create, Read, Update, Delete) applications come into play, enabling users to interact with data stored in databases efficiently. In this article, we'll guide you through building an efficient Node.js CRUD application, supercharged by MySQL as the database, and enhanced with Bootstrap Offcanvas for an exceptional user experience.

Getting Started with Node.js and MySQL


Before diving into the Offcanvas enhancements, let's set the foundation by creating a basic Node.js CRUD application with MySQL. We'll go step by step:

Step 1: Setting Up Your Development Environment


Make sure you have Node.js and MySQL installed on your system. If not, you can download and install them from their respective websites.

Step 2: Project Initialization


Create a new directory for your project and navigate to it in your terminal. Run the following command to initialize a Node.js project:


npm init -y

Step 3: Installing Dependencies


Install the necessary dependencies: express, mysql2, and body-parser using the following command:


npm install express mysql2 body-parser


Step 4: Creating the Server


Now, create a server.js file and set up your Express server. Import the required modules and configure your server:


const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const app = express();

app.use(bodyParser.urlencoded({ extended : true }));

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

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

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});


Step 5: Setting Up CRUD Routes


Implement your CRUD operations by defining routes for creating, reading, updating, and deleting data in your MySQL database. Here's a simplified example for the "read" operation:

Index Route

When someone navigates to the root URL of our server, we serve an HTML file named form.html. We're essentially setting up the initial interface for users to interact with.


app.get('/', (request, response) => {
	response.sendFile(__dirname + '/form.html');
});


Select or Fetch Data


This route will handle requests for fetching data from our MySQL database.


app.get('/fetchData', (request, response) => {
	const { draw, start, length, order, columns, search } = request.query;

	const column_index = order && order[0] && order[0].column;

	const column_sort_order = order === undefined ? 'desc' : request.query.order[0]['dir'];

	const column_name = column_index ? columns[column_index] : 'id';

	const search_value = search.value;

	const search_query = search_value ? ` WHERE name LIKE '%${search_value}%' OR email LIKE '%${search_value}%'` : '';

	const query1 = `SELECT id, name, email FROM user ${search_query} ORDER BY ${column_name} ${column_sort_order} LIMIT ${start}, ${length}`;

	const query2 = `SELECT COUNT(*) AS Total FROM user`;

	const query3 = `SELECT COUNT(*) AS Total FROM user ${search_query}`;

	connection.query(query1, (dataError, dataResult) => {

		connection.query(query2, (totalDataError, totalDataResult) => {

			connection.query(query3, (totalFilterDataError, totalFilterDataResult) => {

				response.json({
					draw : request.query.draw,
					recordsTotal : totalDataResult[0]['Total'],
					recordsFiltered : totalFilterDataResult[0]['Total'],
					data : dataResult
				});

			})

		})

	})
});


Insert Update Delete Data


This route will handle requests for Insert Update Delete data from our MySQL database.


app.post('/submitData', (request, response) => {
	const id = request.body.id;
	const name = request.body.name;
	const email = request.body.email;
	const action = request.body.action;
	let query;
	let data;
	let message;
	if(action === 'Insert'){
		query = `INSERT INTO user (name, email) VALUES (?, ?)`;
		data = [name, email];
		message = 'Data has been inserted';
	}

	if(action === 'Edit'){
		query = `UPDATE user SET name = ?, email = ? WHERE id = ?`;
		data = [name, email, id];
		message = 'Data has been updated';
	}

	if(action === 'Delete'){
		query = `DELETE FROM user WHERE id = ?`;
		data = [id];
		message = 'Data has been deleted';
	}

	connection.query(query, data, (error, result) => {
		response.json({'message' : message});
	});
});


Fetch Single Data


This route will be used for fetch single data from MySQL Database.


app.get('/fetchData/:id', (request, response) => {
	const query = `SELECT * FROM user WHERE id = ?`;

	connection.query(query, [request.params.id], (error, result) => {
		response.json(result[0]);
	});
});





Enhancing User Experience with Bootstrap Offcanvas


Now that we have our Node.js CRUD application up and running, it's time to enhance the user experience with Bootstrap Offcanvas. The Offcanvas component provides a sleek and non-intrusive way to interact with data.

Step 1: Adding Bootstrap to Your Project


Include Bootstrap CSS and JavaScript files in your HTML. You can either download them and host them locally or use a Content Delivery Network (CDN). Here's an example of using CDN links:


<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
        <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <title>Node.js CRUD with MySQL & Bootstrap Offcanvas</title>
    </head>
    <body>

        <script src="https://code.jquery.com/jquery-3.7.0.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

    </body>
</html>


Step 2: Creating an Offcanvas


Now, let's implement an Offcanvas for editing user data. Add a button to trigger the Offcanvas:


<button type="button" class="btn btn-primary btn-sm float-end" onclick="addData()">Add</button>


Create the Offcanvas element with a form for editing user data:


<div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvas_component">
            <div class="offcanvas-header">
                <h5 class="offcanvas-title" id="offcanvasLabel">Add Data</h5>
                <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
            </div>
            <div class="offcanvas-body">

            </div>
        </div>


Step 3: Implementing the Edit Form


Inside the Offcanvas body, add a form for adding or editing user data. So for this, we have make form field by using javascript function which we can you for both Insert or updata of data also.


function makeForm(id = '', name = '', email = '', action = 'Insert')
    {
        const output = `
        <div class="mb-3">
            <label for="name">Name:</label>
            <input type="text" name="name" id="name" class="form-control" value="${name}" />
        </div>
        <div class="mb-3">
            <label for="email">Email:</label>
            <input type="email" name="email" id="email" class="form-control" value="${email}" />
        </div>
        <input type="hidden" name="action" id="action" value="${action}" />
        <input type="hidden" name="id" id="id" value="${id}" />
        <input type="submit" class="btn btn-primary" onclick="submitForm();" value="${action}" />
        `;
        return output;
    }


Step 4: JavaScript for Offcanvas Interaction


Finally, use JavaScript to handle the Offcanvas interactions. Add an event listener to open the Offcanvas when the "Edit User" button is clicked:


let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    let offcanvasLabel = document.querySelector('#offcanvasLabel');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);


You can further enhance this by pre-populating the form with user data for editing and implementing the update functionality.

Complete Source Code


server.js

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const app = express();

app.use(bodyParser.urlencoded({ extended : true }));

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

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

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/form.html');
});

app.get('/fetchData', (request, response) => {
	const { draw, start, length, order, columns, search } = request.query;

	const column_index = order && order[0] && order[0].column;

	const column_sort_order = order === undefined ? 'desc' : request.query.order[0]['dir'];

	const column_name = column_index ? columns[column_index] : 'id';

	const search_value = search.value;

	const search_query = search_value ? ` WHERE name LIKE '%${search_value}%' OR email LIKE '%${search_value}%'` : '';

	const query1 = `SELECT id, name, email FROM user ${search_query} ORDER BY ${column_name} ${column_sort_order} LIMIT ${start}, ${length}`;

	const query2 = `SELECT COUNT(*) AS Total FROM user`;

	const query3 = `SELECT COUNT(*) AS Total FROM user ${search_query}`;

	connection.query(query1, (dataError, dataResult) => {

		connection.query(query2, (totalDataError, totalDataResult) => {

			connection.query(query3, (totalFilterDataError, totalFilterDataResult) => {

				response.json({
					draw : request.query.draw,
					recordsTotal : totalDataResult[0]['Total'],
					recordsFiltered : totalFilterDataResult[0]['Total'],
					data : dataResult
				});

			})

		})

	})
});

app.post('/submitData', (request, response) => {
	const id = request.body.id;
	const name = request.body.name;
	const email = request.body.email;
	const action = request.body.action;
	let query;
	let data;
	let message;
	if(action === 'Insert'){
		query = `INSERT INTO user (name, email) VALUES (?, ?)`;
		data = [name, email];
		message = 'Data has been inserted';
	}

	if(action === 'Edit'){
		query = `UPDATE user SET name = ?, email = ? WHERE id = ?`;
		data = [name, email, id];
		message = 'Data has been updated';
	}

	if(action === 'Delete'){
		query = `DELETE FROM user WHERE id = ?`;
		data = [id];
		message = 'Data has been deleted';
	}

	connection.query(query, data, (error, result) => {
		response.json({'message' : message});
	});
});

app.get('/fetchData/:id', (request, response) => {
	const query = `SELECT * FROM user WHERE id = ?`;

	connection.query(query, [request.params.id], (error, result) => {
		response.json(result[0]);
	});
});

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});




form.html

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
        <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <title>Node.js CRUD with MySQL & Bootstrap Offcanvas</title>
    </head>
    <body>
        <div class="container">
            <h1 class="text-danger text-center mt-3"><b>Node.js CRUD with MySQL & Bootstrap Offcanvas - Delete Data</b></h1>
            <div class="card mt-5">
                <div class="card-header">
                    <div class="row">
                        <div class="col-md-11"><b>Node.js CRUD with MySQL & Bootstrap Offcanvas</b></div>
                        <div class="col-md-1">
                            <button type="button" class="btn btn-primary btn-sm float-end" onclick="addData()">Add</button>
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>Name</th>
                                    <th>Email</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvas_component">
            <div class="offcanvas-header">
                <h5 class="offcanvas-title" id="offcanvasLabel">Add Data</h5>
                <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
            </div>
            <div class="offcanvas-body">

            </div>
        </div>

        <!-- Optional JavaScript; choose one of the two! -->

        <!-- Option 1: Bootstrap Bundle with Popper -->
        


        <script src="https://code.jquery.com/jquery-3.7.0.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

    </body>
</html>

<script>

$(document).ready(function(){

    $('#sample_data').DataTable({
        ajax : '/fetchData',
        processing : true,
        serverSide : true,
        serverMethod : 'GET',
        order : [],
        columns : [
            { data : 'name' },
            { data : 'email' },
            {
                data : null,
                render : function (data, type, row){
                    return `<button class="btn btn-warning btn-sm" onclick="fetchData(${data.id})">Edit</button>&nbsp;<button class="btn btn-danger btn-sm" onclick="deleteData(${data.id})">Delete</button>`;
                }
            }
        ]
    });

});

    let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    let offcanvasLabel = document.querySelector('#offcanvasLabel');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);

    function addData()
    {
        offcanvas_body.innerHTML = makeForm();
        offcanvas.show();
    }

    function makeForm(id = '', name = '', email = '', action = 'Insert')
    {
        const output = `
        <div class="mb-3">
            <label for="name">Name:</label>
            <input type="text" name="name" id="name" class="form-control" value="${name}" />
        </div>
        <div class="mb-3">
            <label for="email">Email:</label>
            <input type="email" name="email" id="email" class="form-control" value="${email}" />
        </div>
        <input type="hidden" name="action" id="action" value="${action}" />
        <input type="hidden" name="id" id="id" value="${id}" />
        <input type="submit" class="btn btn-primary" onclick="submitForm();" value="${action}" />
        `;
        return output;
    }

    function submitForm()
    {
        const id = document.querySelector('#id').value;
        const name = document.querySelector('#name').value;
        const email = document.querySelector('#email').value;
        const action = document.querySelector('#action').value;

        $.ajax({
            url : '/submitData',
            method : 'POST',
            data : {id : id, name : name, email : email, action : action},
            dataType : 'JSON',
            success : function(data){
                $('#sample_data').DataTable().ajax.reload();
                offcanvas.hide();
                alert(data.message);
            }
        });
    }

    function fetchData(id)
    {
        $.ajax({
            url : '/fetchData/'+id,
            dataType : 'JSON',
            success : function(data){
                offcanvas_body.innerHTML = makeForm(data.id, data.name, data.email, 'Edit');
                offcanvasLabel.innerHTML = 'Edit Data';
                offcanvas.show();
            }
        });
    }

    function deleteData(id){
        let output = `
        <div class="text-center">
            <h3 class="text-danger mb-4">Are you sure you want to remove this data?</h3>
            <input type="hidden" id="id" value="${id}" />
            <input type="hidden" id="action" value="Delete" />
            <input type="hidden" id="name" value="" />
            <input type="hidden" id="email" value="" />
            <button type="button" class="btn btn-info" onclick="submitForm()">OK</button>
            <button type="button" class="btn btn-default" data-bs-dismiss="offcanvas">Cancel</button>
        </div>
        `;

        offcanvas_body.innerHTML = output;
        offcanvasLabel.innerHTML = 'Delete Data Confirmation';
        offcanvas.show();
    }

</script>


Run Node Application


For run Node.js Application, we have goes to terminal and goes into our working directory and run following command.


node server.js


This command will start our Node server and for check output in browser, we have to open this url in the browser window.


http://localhost:3000/


Conclusion


In this article, we've walked through the process of building an efficient Node.js CRUD application with MySQL. We've also explored how to enhance the user experience by integrating Bootstrap Offcanvas for a seamless and visually appealing interface. By combining the power of Node.js, MySQL, and Bootstrap Offcanvas, you can create dynamic and user-friendly applications that excel in data management.

Now, it's your turn to explore and expand upon these concepts to build feature-rich CRUD applications tailored to your specific requirements. Happy coding!

Wednesday 23 August 2023

How to Get Last Inserted ID in Node.js using MySQL


When working with databases in web applications, it's common to need the ID of the last inserted record. This ID is often used for various purposes, such as linking related data or displaying a confirmation message. In this tutorial, we'll explore how to retrieve the last inserted ID using Node.js and MySQL.


How to Get Last Inserted ID in Node.js using MySQL


Prerequisites


Before you begin, make sure you have Node.js and MySQL installed on your system. You'll also need a basic understanding of JavaScript and SQL queries.

Setting Up the Project


1. Initializing the Project:


Open your terminal and create a new project folder. Navigate to this folder and run the following command to initialize a new Node.js project:


npm install express


2. Installing Dependencies:


We'll need the mysql2 package to interact with the MySQL database. Install it using the following command:


npm install mysql2


Set up MySQL Table


In this tutorial, we will inserted HTML form data into MySQL table. So first we need to create user sql table in your database. So for create user table, we have to run this query.


--
-- Database: `testing`
--

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

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


Create HTML Form


Create an HTML form where users can submit data. Make sure to set the action attribute of the form to the URL where your server will handle the form submission.

form.html

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">

        <title>How to Get Last Inserted ID in Node.js</title>
    </head>
    <body>
        <div class="container">
            <div class="card mt-5">
                <div class="card-header">How to Get Last Inserted ID in Node.js</div>
                <div class="card-body">
                    <form method="post" action="/submit">
                        <div class="mb-3">
                            <label for="name">Name:</label>
                            <input type="text" name="name" class="form-control" required>
                        </div>
                        <div class="mb-3">
                            <label for="email">Email:</label>
                            <input type="email" name="email" class="form-control" required>
                        </div>
                        <input type="submit" name="submit" value="Submit" class="btn btn-primary">
                    </form>
                </div>
            </div>
        </div>

        <!-- Optional JavaScript; choose one of the two! -->

        <!-- Option 1: Bootstrap Bundle with Popper -->
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

    </body>
</html>





Server-side Node.js Code:


Set up your Node.js server to handle the form submission and insert data into the MySQL table.

server.js

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const app = express();

app.use(bodyParser.urlencoded({extended : true}));

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

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

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/form.html');
});

app.post('/submit', (request, response) => {
	const name = request.body.name;
	const email = request.body.email;

	const insertQuery = `INSERT INTO user (name, email) VALUES (?, ?)`;

	connection.query(insertQuery, [name, email], (error, result) => {
		if(error){
			response.send('Error inserting data');
			return;
		}

		const lastInsertedId = result.insertId;

		response.send(`Data inserted successfully. Last inserted ID:${lastInsertedId}`);
	});
});

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
})


  1. Import the express and mysql2 package by requiring it at the beginning of your JavaScript file.
  2. Create a MySQL connection using the mysql.createConnection() method, passing in the necessary connection details like host, username, password, and database name.
  3. Use the connection.connect() method to establish the connection to the MySQL database. Handle any connection errors using the callback.
  4. Import the required packages like body-parser and set up the Express app.
  5. Use the bodyParser.urlencoded() middleware to parse the form data.
  6. Set up routes to serve the HTML form and handle the form submission.
  7. Inside the form submission route (/submit), retrieve the submitted data from req.body.
  8. Construct an SQL query to insert the data into the MySQL table.
  9. Use the connection.query() method to execute the insert query.
  10. Handle errors and success cases appropriately.
  11. Retrieve the last inserted ID from the result object using result.insertId.

Conclusion:


Congratulations! You've successfully learned how to get the last inserted ID in Node.js using MySQL. This technique is essential for various scenarios where you need to track and manage your database records effectively. By understanding these fundamentals, you'll be better equipped to build powerful web applications that interact with databases.

This tutorial provides a foundation for more advanced database interactions, so feel free to explore further and enhance your Node.js skills.