[AWS] Apache2 다운로드 후, mysql을 통해 만든 테이블을 띄우기

1. WEB 테스트 하기

web을 띄우기 위해서 웹 서버 소프트웨어 apache2 다운 받기

(요즘 너무 nginx만 많이 써봤기 때문에 apache2로 선정!)

apache2 다운 받기

sudo apt update

sudo apt install apache2 -y

sudo apt install php libapache2-mod-php php-mysql -y

docker kubernetes 공인 IP로 접속

2. php 파일 생성

cd /var/www/html

여기에 새 파일 생성

#################################################################################

vi index.php

파일을 열고 아래 항목 입력

<?php
session_start();

// 로그인 여부 확인
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

// RDS 데이터베이스 연결 설정
$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x
# 이 부분 수정하기

// MySQL 연결 생성
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
    die("Connection failed: " . mysqli_connect_error());
}

// 상품 목록 가져오기 쿼리
$sql = "SELECT id, product_name, description, price, image_url FROM products";
$result = $conn->query($sql);

?>

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>상품 목록</title>
    <style>
        .product {
            border: 1px solid #ccc;
            padding: 10px;
            margin: 10px;
            width: 250px;
            float: left;
        }
        .products {
            display: flex;
            flex-wrap: wrap;
        }
        .product img {
            width: 100px;
            height: 100px;
        }
    </style>
</head>
<body>
    <h1>안녕하세요, <?php echo $_SESSION['username']; ?>님!</h1>
    <h2>상품 목록</h2>

    <div class="products">
        <?php if ($result->num_rows > 0): ?>
            <?php while($row = $result->fetch_assoc()): ?>
                <div class="product">
                    <h3><?php echo $row['product_name']; ?></h3>
                    <p><?php echo $row['description']; ?></p>
                    <p>Price: $<?php echo $row['price']; ?></p>
                    <img src="<?php echo $row['image_url']; ?>" alt="Product Image"><br><br>

                    
                    <form action="add_to_cart.php" method="POST">
                        <input type="hidden" name="product_id" value="<?php echo $row['id']; ?>">
                        <button type="submit">Add to Cart</button>
                    </form>
                </div>
            <?php endwhile; ?>
        <?php else: ?>
            <p>상품이 없습니다.</p>
        <?php endif; ?>
    </div>

    <div style="clear: both;"></div>
    <a href="cart.php">장바구니 보기</a>
</body>
</html>

<?php
$conn->close();
?>

*RDS endpoint 찾기

systemctl restart apache2
apt install mysql-client -y

mysql -h rds-jw.ct640yeesig2.ap-northeast-2.rds.amazonaws.com -u admin -pdkagh1..
##master user를 사용하는게 따로 설정 안해줘서 편함 create user를 하면 권한을 따로 줘야함

dbname 은 RDS 이름이 아니라 DB name이어야 함!

############################################################################

vi /var/www/html/index.php

<?php
session_start();

// 로그인 여부 확인
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

// RDS 데이터베이스 연결 설정
$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x

// MySQL 연결 생성
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
    die("Connection failed: " . mysqli_connect_error());
}

// 상품 목록 가져오기 쿼리
$sql = "SELECT id, product_name, description, price, image_url FROM products";
$result = $conn->query($sql);

?>

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>상품 목록</title>
    <style>
        .product {
            border: 1px solid #ccc;
            padding: 10px;
            margin: 10px;
            width: 250px;
            float: left;
        }
        .products {
            display: flex;
            flex-wrap: wrap;
        }
        .product img {
            width: 100px;
            height: 100px;
        }
    </style>
</head>
<body>
    <h1>안녕하세요, <?php echo $_SESSION['username']; ?>님!</h1>
    <h2>상품 목록</h2>

    <div class="products">
        <?php if ($result->num_rows > 0): ?>
            <?php while($row = $result->fetch_assoc()): ?>
                <div class="product">
                    <h3><?php echo $row['product_name']; ?></h3>
                    <p><?php echo $row['description']; ?></p>
                    <p>Price: $<?php echo $row['price']; ?></p>
                    <img src="<?php echo $row['image_url']; ?>" alt="Product Image"><br><br>

                    
                    <form action="add_to_cart.php" method="POST">
                        <input type="hidden" name="product_id" value="<?php echo $row['id']; ?>">
                        <button type="submit">Add to Cart</button>
                    </form>
                </div>
            <?php endwhile; ?>
        <?php else: ?>
            <p>상품이 없습니다.</p>
        <?php endif; ?>
    </div>

    <div style="clear: both;"></div>
    <a href="cart.php">장바구니 보기</a>
</body>
</html>

<?php
$conn->close();
?>

##################################################################################

vi /var/www/html/signup.php

<?php
$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x

// MySQL 연결 생성
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$error_message = "";
$success_message = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $username = trim($_POST["username"]);
    $email = trim($_POST["email"]);
    $password = trim($_POST["password"]);
    $confirm_password = trim($_POST["confirm_password"]);

    if (empty($username) || empty($email) || empty($password) || empty($confirm_password)) {
        $error_message = "모든 필드를 입력해주세요.";
    } elseif ($password !== $confirm_password) {
        $error_message = "비밀번호가 일치하지 않습니다.";
    } else {
        $hashed_password = password_hash($password, PASSWORD_DEFAULT);
        $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";

        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("sss", $username, $email, $hashed_password);
            if ($stmt->execute()) {
                $success_message = "회원가입이 성공적으로 완료되었습니다.";
            } else {
                $error_message = "회원가입 중 오류가 발생했습니다. " . $conn->error;
            }
            $stmt->close();
        }
    }
}

$conn->close();
?>

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>회원가입</title>
</head>
<body>
    <h1>회원가입</h1>

    <?php if (!empty($error_message)): ?>
        <p style="color: red;"><?php echo $error_message; ?></p>
    <?php endif; ?>

    <?php if (!empty($success_message)): ?>
        <p style="color: green;"><?php echo $success_message; ?></p>
    <?php endif; ?>

    <form action="signup.php" method="POST">
        <label for="username">사용자 이름:</label><br>
        <input type="text" id="username" name="username" required><br><br>

        <label for="email">이메일:</label><br>
        <input type="email" id="email" name="email" required><br><br>

        <label for="password">비밀번호:</label><br>
        <input type="password" id="password" name="password" required><br><br>

        <label for="confirm_password">비밀번호 확인:</label><br>
        <input type="password" id="confirm_password" name="confirm_password" required><br><br>

        <input type="submit" value="회원가입">
    </form>
    <a href="login.php">로그인 페이지로 이동</a>
</body>
</html>

#######################################################################################

vi /var/www/html/login.php

<?php
session_start();

$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$error_message = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $email = trim($_POST["email"]);
    $password = trim($_POST["password"]);

    if (!empty($email) && !empty($password)) {
        $sql = "SELECT id, username, password FROM users WHERE email = ?";

        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("s", $email);
            $stmt->execute();
            $stmt->store_result();

            if ($stmt->num_rows == 1) {
                $stmt->bind_result($id, $username, $hashed_password);
                $stmt->fetch();

                if (password_verify($password, $hashed_password)) {
                    $_SESSION['user_id'] = $id;
                    $_SESSION['username'] = $username;
                    header("Location: index.php");
                    exit();
                } else {
                    $error_message = "비밀번호가 일치하지 않습니다.";
                }
            } else {
                $error_message = "해당 이메일을 찾을 수 없습니다.";
            }
            $stmt->close();
        }
    } else {
        $error_message = "이메일과 비밀번호를 입력해주세요.";
    }
}

$conn->close();
?>

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>로그인</title>
</head>
<body>
    <h1>로그인</h1>

    <?php if (!empty($error_message)): ?>
        <p style="color: red;"><?php echo $error_message; ?></p>
    <?php endif; ?>

    <form action="login.php" method="POST">
        <label for="email">이메일:</label><br>
        <input type="email" id="email" name="email" required><br><br>

        <label for="password">비밀번호:</label><br>
        <input type="password" id="password" name="password" required><br><br>

        <input type="submit" value="로그인">
    </form>
</body>
</html>

###############################################################################

vi /var/www/html/add_to_cart.php

<?php
session_start();

// 로그인 여부 확인
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

// RDS 데이터베이스 연결 설정
$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x
// MySQL 연결 생성
$conn = new mysqli($servername, $username, $password, $dbname);

// 연결 확인
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// POST로 전송된 상품 ID와 사용자 ID
$product_id = $_POST['product_id'];
$user_id = $_SESSION['user_id'];

// 장바구니에 상품 추가 (이미 있으면 수량 증가)
$sql = "INSERT INTO cart (user_id, product_id, quantity) VALUES (?, ?, 1)
        ON DUPLICATE KEY UPDATE quantity = quantity + 1";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $user_id, $product_id);

if ($stmt->execute()) {
    echo "장바구니에 상품이 추가되었습니다.";
} else {
    echo "장바구니에 상품을 추가하는 중 오류가 발생했습니다.";
}

$stmt->close();
$conn->close();

// 상품 목록 페이지로 리다이렉트
header("Location: index.php");
exit();
?>

#########################################################################################

vi /var/www/html/cart.php

<?php
session_start();

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

$servername = "RDS ENDPOINT";
$username = "master 계정";
$password = "master 계정 비번";
$dbname = "db이름"; //RDS 이름 x

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$user_id = $_SESSION['user_id'];
$sql = "SELECT products.product_name, products.price, cart.quantity
        FROM cart
        JOIN products ON cart.product_id = products.id
        WHERE cart.user_id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();
?>

<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>장바구니</title>
</head>
<body>
    <h1>장바구니</h1>

    <?php if ($result->num_rows > 0): ?>
        <ul>
            <?php while($row = $result->fetch_assoc()): ?>
                <li>
                    <?php echo $row['product_name']; ?> -
                    수량: <?php echo $row['quantity']; ?> -
                    가격: $<?php echo $row['price']; ?>
                </li>
            <?php endwhile; ?>
        </ul>
    <?php else: ?>
        <p>장바구니가 비어 있습니다.</p>
    <?php endif; ?>
</body>
</html>

<?php
$stmt->close();
$conn->close();
?>

이렇게 php 파일들 만들기

3. DB 생성하는 방법

일단 mysql 다운로드

apt install mysql-client -y

mysql -h "rds주소" -u admin -pdkagh1..

*mydql -h 하고 rds endpoint -u 유저 이름 -p하고 띄어쓰기 없이 비밀번호 입력

하면 mysql에 로그인 됨

이후로 데이터베이스 만들어주기

CREATE DATABASE shop_rds2;

USE shop_rds2;

*shop_rds 입장

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT NOT NULL DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

내용 입력

INSERT INTO products (product_name, description, price, image_url) VALUES
('Laptop', 'A powerful laptop', 1200.00, 'laptop.jpg'),
('Smartphone', 'A high-quality smartphone', 800.00, 'smartphone.jpg'),
('Tablet', 'A lightweight tablet', 500.00, 'tablet.jpg');

하기

quit으로 mysql 나오기

4. 공인 IP로 접속해보기

db에 저장된 데이터가 나와야 함

저작자표시 비영리 변경금지

'클라우드 > AWS' 카테고리의 다른 글

[AWS] SSH로 접속하여 AWS configure 하는 방법 (0)	2024.12.05
[AWS] IAM 새 사용자 (Full Access) 생성 및 Access Key 생성 (0)	2024.12.05
[AWS] Bastion Host, Docker Kubernetes EC2서버 생성하는 방법 (0)	2024.11.30
[AWS] Nginx의 간단한 개념과 WEB EC2에 SSH로 원격 접속해서 Nginx 다운받는 방법 (0)	2024.11.30
[AWS] 기본 세팅(VPC 생성부터 Subnet, EC2 생성, Route table, SSH 접속 확인까지) (0)	2024.11.13