⌨️ PDO 기본을 탄탄하게

[0] 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)
);

 

[1] 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 "학생 정보 저장 완료";

 

[2] 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>";
}

 

[3] 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);

 

[4] 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;