⌨️ PDO 기본을 탄탄하게

[1] school 테이블 구조

CREATE TABLE school (
    id INT AUTO_INCREMENT PRIMARY KEY,
    grade INT NOT NULL,
    class_num INT NOT NULL,
    student_num INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20)
);

 

[2] DB연결

<?php

$host     = 'localhost';
$dbname   = 'school_db';
$username = 'root';
$password = '1234';

try {
    $pdo = new PDO(
        "mysql:host={$host};dbname={$dbname};charset=utf8mb4",
        $username,
        $password
    );

    // SQL 오류 발생 시 예외(Exception) 발생
    $pdo->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

} catch (PDOException $e) {
    die("DB 연결 실패 : " . $e->getMessage());
}

$dsn = "mysql:host=localhost;dbname=your_database;charset=utf8mb4";
$user = "username";
$password = "password";
$pdo = new PDO($dsn, $user, $password);

 

[3] INSERT

$stmt = $pdo->prepare(
    "INSERT INTO 테이블 (필드1, 필드2)
     VALUES (?, ?)"
);

$stmt->execute([$값1, $값2]);

<?php

// 저장할 데이터
$grade       = 2;
$class_num   = 3;
$student_num = 15;
$name        = "홍길동";
$phone       = "010-1234-5678";

// SQL 준비
$stmt = $pdo->prepare(
    "INSERT INTO school
    (grade, class_num, student_num, name, phone)
    VALUES (?, ?, ?, ?, ?)"
);

// 실행
$stmt->execute([
    $grade,
    $class_num,
    $student_num,
    $name,
    $phone
]);

echo "학생 정보 저장 완료";

 

[4] SELECT 여러 건

$stmt = $pdo->prepare(
    "SELECT *
     FROM 테이블
     WHERE 조건필드 = ?"
);

$stmt->execute([$조건]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

<?php

$grade = 2;

// SQL 준비
$stmt = $pdo->prepare(
    "SELECT *
     FROM school
     WHERE grade = ?"
);

// 실행
$stmt->execute([$grade]);

// 모든 결과 가져오기
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 출력
foreach ($students as $student) {
    echo $student['name'] . "<br>";
}

 

[5] SELECT 한 건

$stmt->execute([$조건]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);

$stmt = $pdo->prepare(
    "SELECT *
     FROM school
     WHERE grade = ?
       AND class_num = ?
       AND student_num = ?"
);

$stmt->execute([
    $grade,
    $class_num,
    $student_num
]);

$student = $stmt->fetch(PDO::FETCH_ASSOC);

 

[6] COUNT

$stmt = $pdo->prepare(
    "SELECT COUNT(*)
     FROM 테이블
     WHERE 조건필드 = ?"
);

$stmt->execute([$조건]);
$count = $stmt->fetchColumn();

<?php

$grade = 2;

// SQL 준비
$stmt = $pdo->prepare(
    "SELECT COUNT(*)
     FROM school
     WHERE grade = ?"
);

// 실행
$stmt->execute([$grade]);

// 결과 가져오기
$count = $stmt->fetchColumn();

echo "2학년 학생 수 : " . $count;