NodeJS Tutorial Day 5

Learning Objective: Let’s start Connection with MySQL Database. You need to Install XAMPP to access your mysql using phpmyadmin.

You need to create database

ecomm

Table Creation Sample Query

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 11, 2022 at 05:35 PM
-- Server version: 10.4.22-MariaDB
-- PHP Version: 7.4.26

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `ecom`
--

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

--
-- Table structure for table `res_customer_master`
--

CREATE TABLE `res_customer_master` (
  `cmid` int(11) NOT NULL,
  `cname` varchar(20) NOT NULL,
  `cmobile` varchar(20) NOT NULL,
  `cemail` varchar(100) NOT NULL,
  `cpassword` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `res_customer_master`
--

INSERT INTO `res_customer_master` (`cmid`, `cname`, `cmobile`, `cemail`, `cpassword`) VALUES
(1, 'Adarsh', '9998887771', 'adarsh123@gmail.com', '12345'),
(2, 'Test', '989898989', 'adarsh@gmail.com', 'adarsh');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `res_customer_master`
--
ALTER TABLE `res_customer_master`
  ADD PRIMARY KEY (`cmid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `res_customer_master`
--
ALTER TABLE `res_customer_master`
  MODIFY `cmid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

Now Let’s write Sample NodeJS Code with Select Query Example

var mysql = require('mysql');
var express = require('express');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "ecom"
});

const app = express()
const port = 3000

con.connect(function(err) {
    if (err) throw err;
});

app.get('/', (req, res) => {

    con.query("SELECT * FROM res_customer_master where cmobile='" + req.query.p1 + "' and cpassword='" + req.query.p2 + "'", function (err, result, fields) {
      if (err) throw err;
      //console.log(result[0].cmid);
      //console.log(result[0].cname);
      //console.log(result[0].cmobile);
      //console.log(result[0].cemail);

      res.send(result.length +  result[0].cname + "");

    });
})


app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
}) 

URL to test your code

http://localhost:3000/?p1=989898989&p2=adarsh