Javascript (자바스크립트) Query Table Join (aircraft 06.11)

2020. 7. 1. 15:08

1. app.js

var express = require('express');
var http = require('http');
var app = express();
var server = http.createServer(app).listen(80);
var mysql = require('mysql');
console.log("Server is running...")

var bodyParser = require("body-parser");
app.use(bodyParser.urlencoded({limit: '50mb', extended: false}));
app.use(bodyParser.json({limit: '50mb'}));

var connection = mysql.createConnection({
  host: 'localhost',
  port: 3308,
  user:'root',
  password: '1234',
  database: 'javascript'
});

app.get('/', function (req, res) {
  res.sendfile("main.html");
});
app.get('/main', function (req, res) {
  res.sendfile("main.html");
});
app.get('/insertAircraftPage', function (req, res) {
  res.sendfile("insertAircraft.html");
});
app.get('/insertFlightPage', function (req, res) {
  res.sendfile("insertFlight.html");
});

// insertFlight 페이지 접속시 aircraft 테이블의 정보를 가져오는 라우터 (aircraft 테이블의 aircraftCode)
app.get('/aircraft', function (req, res) {
	var q = `select aircraftCode, aircraftName from aircraft;`;
	connection.query(q,
		function (err, rows, fields) {
			if (err) throw err;
			res.send(rows);
		}
	);
});

// insertAircraft 페이지에서 aircraft 테이블에 정보를 입력시켜주기 위한 라우터
app.post('/aircraft', function (req, res) {
	var aircraftCode = req.body.aircraftCode;
	var aircraftName = req.body.aircraftName;
	var seats = req.body.seats;

	var q = `insert into aircraft (aircraftCode, aircraftName, seats) values ("${aircraftCode}", "${aircraftName}", ${seats});`;
  connection.query(q,
    function (err, rows, fields) {
      if (err) throw err;
      res.send(rows);
    }
  );
});

// main 페이지에서 aircraft 테이블과 flight 테이블의 정보를 가져와 조인 시켜 response를 보내주기 위한 라우터
app.get('/flight', function (req, res) {
	var q = `	select * from flight f, aircraft a where f.aircraftCode = a.aircraftCode;`;
	connection.query(q,
		function (err, rows, fields) {
			if (err) throw err;
			res.send(rows);
		}
	);
});

// insertFlight 페이지에서 flight 테이블에 정보를 입력시켜주기 위한 라우터
app.post('/flight', function (req, res) {
	var flightName = req.body.flightName;
	var aircraftCode = req.body.aircraftCode;
	var departure = req.body.departure;
	var arrival = req.body.arrival;
	var departTime = req.body.departTime;
	var arriveTime = req.body.arriveTime;

	var q = `insert into flight (flightName, aircraftCode, departure, arrival, departTime, arriveTime)
	values
	("${flightName}", "${aircraftCode}", "${departure}", "${arrival}", "${departTime}", "${arriveTime}");`;
  connection.query(q,
    function (err, rows, fields) {
      if (err) throw err;
      res.send(rows);
    }
  );


});

2. main.html (http://localhost/)

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>
  </head>
  <body>
    <div id="flightArea"></div>
    <br>
    <input type="button" value="항공기 입력" id="goToInsertAircraftPage">
    <input type="button" value="운항 입력" id="goToInsertFlightPage">
  </body>

  <script src="http://code.jquery.com/jquery-latest.min.js"></script>
  <script type="text/javascript">
    $.ajax({
      url:'/flight',
      data:{
      },
      success:function(res){
        console.log(res);
        $("#flightArea").append(`편명 / 항공기명 / 좌석수 / 출발지 / 목적지 / 출발시각 / 도착시각<br><br>`)
        for(var i=0;i<res.length;i++) {
          var flight = res[i];
          $("#flightArea").append(`${flight.flightName} / ${flight.aircraftName} / ${flight.seats}\
             / ${flight.departure} / ${flight.arrival} / ${flight.departTime} / ${flight.arriveTime} <br>`)
        }
      }
    });

    $("#goToInsertAircraftPage").click(function(){
      location.href = "/insertAircraftPage";
    });

    $("#goToInsertFlightPage").click(function(){
      location.href = "/insertFlightPage";
    });
  </script>
</html>

3. aircraftPost.html (http://localost/aircraftPostPage)

<!DOCTYPE html>
<html lang="en" dir="ltr">

<head>
  <title></title>
</head>

<body>
<h3> 항공기정보 입력을 해주세요 </h3>
  항공코드<input type="text" id="aircraftCode">
  <br>
  항공기명<input type="text" id="aircraftName">
  <br>
  좌석수<input type="text" id="seats">
  <br><br>
  <input type="button" value="항공기 정보 입력" id="postBtn">
  <input type="button" value="메인으로" id="mainBtn" onclick = "switchBtn()">


  <span id="airArea"></span>
</body>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
  $("#postBtn").click(function() {
    var aircraftCode = $("#aircraftCode").val();
    var aircraftName = $("#aircraftName").val();
    var seats = $("#seats").val();
    console.log(aircraftCode, aircraftName, seats);
    $.ajax({
      url: '/aircraftPost',
      type: 'POST',
      data: {
        aircraftCode: aircraftCode,
        aircraftName: aircraftName,
        seats: seats
      },
      success: function(response) {
        console.log(response);
        alert('항공기 정보를 입력했습니다.\n메인 페이지로 이동합니다.');
        location.href = '/aircraftPage';
      }
    });
  });

  function switchBtn() {
    location.href = "/aircraftPage"
  }
</script>

</html>

4. insertFlight.html (http://localhost/insertFlightPage)

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <title></title>
  </head>
  <body>
    편명<input type="text" id="flightName"><br>
    항공기명 <select id="aircraftCode">
    </select><br>
    출발지<input type="text" id="departure"><br>
    목적지<input type="text" id="arrival"><br>
    출발시각<input type="text" id="departTime"><br>
    도착시각<input type="text" id="arriveTime"><br>
    <input type="button" value="운항 입력" id="insertFlightBtn">
    <input type="button" value="메인으로" id="backToMainBtn">
  </body>

  <script src="http://code.jquery.com/jquery-latest.min.js"></script>
  <script type="text/javascript">
    $.ajax({
      url:'/aircraft',
      data:{
      },
      success:function(res){
        for(var i=0;i<res.length;i++) {
          var aircraft = res[i];
          $("#aircraftCode").append(`<option value="${aircraft.aircraftCode}">${aircraft.aircraftName}</option>`)
        }
      }
    });

    $("#insertFlightBtn").click(function(){
      $.ajax({
        url:'/flight',
        type:"POST",
        data:{
          flightName: $("#flightName").val(),
          aircraftCode: $("#aircraftCode").val(),
          departure: $("#departure").val(),
          arrival: $("#arrival").val(),
          departTime: $("#departTime").val(),
          arriveTime: $("#arriveTime").val(),
        },
        success:function(res){
          alert("운항편이 입력되었습니다.");
          location.href = "/";
        }
      });

    });

    $("#backToMainBtn").click(function(){
      location.href = "/";
    });

  </script>
</html>

입력 후 메인페이지

DB

Database : MySQL / Database Name : javascript / Table Name : aircraft

CREATE TABLE javascript.aircraft (
	`no` INT auto_increment NOT NULL,
	aircraftCode varchar(100) NOT NULL,
	aircraftName varchar(100) NOT NULL,
	seats varchar(100) NOT NULL,
	CONSTRAINT aircraft_PK PRIMARY KEY (`no`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

Database : MySQL / Database Name : javascript / Table Name : flight

CREATE TABLE javascript.flight (
	`no` INT auto_increment NOT NULL,
	flightName varchar(100) NOT NULL,
	aircraftCode varchar(100) NOT NULL,
	departure varchar(100) NOT NULL,
	arrival varchar(100) NOT NULL,
	departTime varchar(100) NOT NULL,
	arriveTime varchar(100) NOT NULL,
	CONSTRAINT flight_PK PRIMARY KEY (`no`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

'개발자 > Javascript' 카테고리의 다른 글

Javascript (자바스크립트) 네이버 주식 DB 저장, 구글 차트 06.25 (0)	2020.07.01
Javascript (자바스크립트) setInterval, setTimeout 06.18 (0)	2020.07.01
★ Javascript (자바스크립트) Request Type 분리 get, post, put, delete (newsList 06.04) (0)	2020.06.25
Javascript (자바스크립트) 콜백 함수 (newsList 05.28) (0)	2020.06.25
Javascript (자바스크립트) 구글차트 (0)	2020.06.18

좋은 개발자가 되자

Javascript (자바스크립트) Query Table Join (aircraft 06.11)

1. app.js

2. main.html (http://localhost/)

3. aircraftPost.html (http://localost/aircraftPostPage)

4. insertFlight.html (http://localhost/insertFlightPage)

DB

'개발자 > Javascript' 카테고리의 다른 글

+ Recent posts

티스토리툴바