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 vario...
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.
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');
})
- Import the express and mysql2 package by requiring it at the beginning of your JavaScript file.
- Create a MySQL connection using the mysql.createConnection() method, passing in the necessary connection details like host, username, password, and database name.
- Use the connection.connect() method to establish the connection to the MySQL database. Handle any connection errors using the callback.
- Import the required packages like body-parser and set up the Express app.
- Use the bodyParser.urlencoded() middleware to parse the form data.
- Set up routes to serve the HTML form and handle the form submission.
- Inside the form submission route (/submit), retrieve the submitted data from req.body.
- Construct an SQL query to insert the data into the MySQL table.
- Use the connection.query() method to execute the insert query.
- Handle errors and success cases appropriately.
- 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.
COMMENTS