🐘 PHP ↔ MySQL 연결 및 다루기 (PDO 방식)

...

MySQLi 와 PDO

현재 PHP에서 MySQL 데이터베이스에 연결하기 위해 사용할 수 있는 API는 다음과 같다.

MySQL improved extension(MySQLi extension)
PHP Data Objects(PDO)

이 두 확장 라이브러리는 각각 장단점이 있으며, 사용 방법과 기능도 조금씩 다르다. 다만 두 가지 API는 각각 장단점을 가지지만, MySQL 환경에서 성능상의 큰 차이를 보이지는 않는다. 따라서 자신이 사용하기 편한 API를 선택하는 것이 좋다.

MySQLi

MySQLi는 MySQL 데이터베이스와 상호작용하기 위한 PHP의 확장 라이브러리이다. MySQLi는 객체 지향적인 접근 방식을 사용하며, 이전 버전의 MySQL과의 호환성도 지원한다. MySQLi의 장점은 다음과 같다.

성능 : MySQLi는 MySQL과 직접 통신하기 때문에 PDO보다 더 빠르다
객체 지향 : MySQLi는 객체 지향적인 접근 방식을 사용하기 때문에 코드의 가독성이 좋아지고, 유지보수가 쉬워진다
보안 : MySQLi는 Prepared Statements와 같은 보안 기능을 지원한다

PDO

PDO는 PHP에서 여러 종류의 데이터베이스와 상호작용하기 위한 추상화 레이어이다. PDO는 MySQL, PostgreSQL, SQLite 등 다양한 데이터베이스에 대해 호환성이 있으며, 같은 방법으로 작성된 코드를 다른 데이터베이스로 쉽게 이전할 수 있다는 특징이 있다. PDO의 장점은 다음과 같다.

호환성 : PDO는 여러 종류의 데이터베이스와 호환성이 있다.
보안 : PDO는 Prepared Statements와 같은 보안 기능을 지원한다.
가독성 : PDO는 바인딩 변수를 사용하여 SQL 쿼리를 작성하기 때문에 가독성이 좋아진다.

PDO 사용법

PDO를 사용하면 MySQL, Oracle, MS SQL, PostgreSQL을 포함한 12개의 데이터 베이스를 같은 방식으로 다룰 수 있게 되어 확장성이 좋다. PDO를 사용하는 또 다른 이유는 준비 구문(Prepare Statements)을 활용할 수 있기 때문이다. 준비 구문을 사용하면 SQL 인젝션 공격을 막을 수 있고, 애플리케이션의 성능이 향상된다.

<?php
    $dbHost = "localhost";    // 호스트 주소(localhost, 120.0.0.1)
    $dbName = "test_db";      // 데이타 베이스(DataBase) 이름
    $dbUser = "tester";       // DB 아이디
    $dbPass = "1q2w3e";       // DB 패스워드
    $dbChar = "utf8";         // 문자 인코딩

    // PDO 객체 생성 & DB 접속
    $pdo = new PDO("mysql:host={$dbHost};dbname={$dbName};charset={$dbChar}", $dbUser, $dbPass);

    // 쿼리를 담은 PDOStatement 객체 생성
    $stmt = $pdo->prepare("SELECT * FROM girl_group WHERE name = :name");

    // PDOStatement 객체가 가진 쿼리의 파라메터에 변수 값을 바인드
    $stmt->bindValue(":name", "나연");

    // PDOStatement 객체가 가진 쿼리를 실행
    $stmt->execute();

    // PDOStatement 객체가 실행한 쿼리의 결과값 가져오기
    $row = $stmt->fetch();

    echo "<pre>";
    print_r($row);
    echo "</pre>";
?>

서버 연결

$변수 = new PDO("데이터베이스 종류:host=호스트;port=포트dbname=데이터베이스;charset=인코딩", 계정, 암호);

MySQL의 경우, 데이터베이스 종류는 "mysql"로 지정한다.

이제 MySQL을 사용하여 특정 데이터베이스에 액세스하기 위해 PDO 오브젝트를 작성한다. 이 후에는 이 개체에서 필요한 메소드를 호출해 나갈 뿐이다.

예외처리

PDO 작성에는 또 한 가지 주의해야 할 점이 있다. 그것은 "예외가 발생할 가능성이 있다"는 점이다.

PDO를 사용하여 데이터베이스에 액세스할 때 문제가 발생하면 PDOException 예외가 보내진다.

그래서 new PDO 및 데이터베이스 액세스, 실제로는 다음과 같은 형태로 작성한다.

<?php
    $servername = "localhost"; // 호스트 주소(localhost, 120.0.0.1)
    $dbname = "testDB";  // 데이타 베이스(DataBase) 이름
    $user = "choi"; // DB 아이디
    $password = "0219"; // DB 패스워드
    //$port = '3306';

    try
    {
       // 서버 이름, 데이터베이스 이름, 사용자명과 비밀번호를 전달하여 새로운 PDO 객체를 생성
       $connect = new PDO('mysql:host=$servername;dbname=$dbname', $user, $password);
       // 생성된 PDO 객체에 에러 모드(error mode)를 설정
       // 이렇게 에러 모드를 설정하면, PDO 생성자는 에러가 발생할 때마다 PDOException 예외를 던질 것이다.
       $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

       echo "서버와의 연결 성공!";
    }
    catch(PDOException $ex)
    {
        echo "서버와의 연결 실패! : ".$ex->getMessage()."<br>";
    }
?>

서버 연결 종료

이렇게 생성된 서버와의 연결은 PHP 스크립트가 끝나면 자동으로 같이 종료됩니다.

하지만 PHP 스크립트가 끝나기 전에 서버와의 연결을 종료하고 싶다면, 다음 예제처럼 PDO 객체에 null을 대입하면 됩니다.

<?php
    $connect= null;
?>

쿼리 실행하는 2가지 방법

쿼리 바로 실행

인수에 실행하는 쿼리의 텍스트를 지정해 실행하면, 실행 결과가 반환된다.

$변수 = $pdo->query(쿼리);

<?php
$result = "";

try {
    $pdo = new PDO("mysql:host=localhost:3306;dbname=mysampledata;charset=utf8", "root","1234");
    $statement = $pdo->query("select * from sampletable");

    while($record = $statement->fetch(PDO::FETCH_ASSOC)){
        $result .= "<tr>";
        foreach($record as $column){
            $result .= "<td>" . $column . "</td>";
        }
        $result .= "</tr>";
    }
} catch(PDOException $e){
    $result = "#ERR:" . $e->getMessage();
}

$pdo = null; // 객체에 null 을 줘서 데이터베이스 연결 끊기. unset($pdo) 와 같다.
?>

쿼리 준비하고 실행

쿼리 준비는 PDO::prepare 메소드를 이용한다.

PDO::prepare 메소드는 PDOStatement 객체를 반환한다.

<?php
     $pdoStatement = $pdo->prepare("SELECT * FROM member WHERE name = :name OR email = :email");
?>

① PDO::prepare로 준비하는 SQL구문은 파라미터를 가질 수 있다.

② 파라미터는 이름을 붙여주거나 물음표로 표시할 수 있다.

③ 이름을 붙인 파라미터는 위의 예제에서 보듯이 이름 앞에 : 를 붙여서 만든다.

④ 하나의 SQL 문장에 여러 파라미터가 있는 경우 모두 이름을 붙여주거나 모두 물음표로 표기해야 한다.

(물음표보다는 이름을 붙인 파라미터가 내용을 이해하기 쉬우므로 될 수 있는대로 이름을 붙인 파라미터를 상용하는 것이 좋다.)

<?php
      $name = "이름";
      $email = "이메일";

      // 이름 붙인 파라미터를 사용할 때
      $pdoStatement = $pdo->prepare("SELECT * FROM member WHERE name = :name OR email = :email");
      $pdoStatement = bindValue(":name", $name);
      $pdoStatement = bindValue(":email", $email);

      // 물음표를 사용할때 파라미터가 많아지면 물음표(?)의 경우 순서를 파악하기 어려워 진다.
      $pdoStatement = $pdo->prepare("SELECT * FROM member WHERE name = ? OR email = ?");
      $pdoStatement = bindValue(1, $name);
      $pdoStatement = bindValue(2, $email);
?>

쿼리 파라미터 값 지정

쿼리 파라미터에 값을 대입할 때는 PDOStatement:bindValue, PDOStatement::bindParam 두 가지 메소드를 이용한다.

binValue 는 값을 집접 대입하고, bindParam 은 변수를 대입한다.

// bindValue를 사용한 경우
<?php
    $name = "Kei";

    $stmt = $pdo->prepare("SELECT * FROM girl_group WHERE name = :name");

    $stmt->bindValue(":name", $name);	// 변수에 바인딩 하기 위해 bindValue을 사용
    $name = "서지수";			     	// 쿼리를 실행하기전에 $name 변수의 값을 변경한다.
    $stmt->execute();		  		    // WHERE name = "Kei"으로 실행된다.
    $row = $stmt->fetch();		    	// 객체가 실행한 쿼리의 결과값 가져오기

    echo "<pre>";
    print_r($row);
    echo "</pre>";
?>

※ binvValue는 값 자체를 대입한다. 그래서 위와같이 변수에 새로운 값을 넣어도 반영되지 않는다.

// bindParam를 사용한 경우
<?php
    $name = "유지애";

    $stmt = $pdo -> prepare("SELECT * FROM girl_group WHERE name = :name");

    $stmt->bindParam(":name", $name);	// 변수에 바인딩 하기 위해 bindParam을 사용
    $name = "정예인";				    // 쿼리를 실행하기전에 $name 변수의 값을 변경한다.
    $stmt->execute();			    	// WHERE name = "정예인"으로 실행된다.
    $row = $stmt->fetch();			    // 객체가 실행한 쿼리의 결과값 가져오기

    echo "<pre>";
    print_r($row);
    echo "</pre>";
?>

※ bindParam은 변수를 대입한것이기 때문에 대입한 이후에 값을 변경할 수 있다.

준비된 쿼리 실행

$stmt->execute();

데이터 가져오기

PDOStatement:fetch()

PDOStatement::fetch() 메소드를 한번 실행하면 쿼리 결과에서 한 행을 가져온다.

그래서 다음과 같이 반복문으로 모든 행을 가져와서 처리하는 경우가 많다.

<?php
    $dbHost = "localhost";
    $dbName = "test_db";
    $dbUser = "tester";
    $dbPass = "1q2w3e";
    $dbChar = "utf8";

    $dsn = "mysql:host={$dbHost};dbname={$dbName};charset={$dbChar}";
    $pdo = new PDO($dsn, $dbUser, $dbPass);

    $stmt = $pdo -> prepare("SELECT * FROM girl_group WHERE name = :name");
    $stmt -> bindValue(":name", "사나");
    $stmt -> execute();

    $row = $stmt -> fetch(PDO::FETCH_ASSOC);  // 결과모드 : PDO::FETCH_ASSOC

	echo "그룹&nbsp;:&nbsp;".$row['group_name'];
	echo "<br/>";
	echo "이름&nbsp;:&nbsp;".$row['name'];
	echo "<br/>";
	echo "포지션&nbsp;:&nbsp;".$row['position'];
	echo "<br/>";
	echo "회사&nbsp;:&nbsp;".$row['company'];
?>

PDOStatement:fetchAll()

(PDOStatement::fetchAll()을 사용할 때 데어티베이스에서 조회한 값이 많으면 메모리 부족으로 웹 서버가 다운될 수 있다. 조회 결과가 적다는 확신이 있는 때에만 신중하게 사용해야 한다.)

<?
    ...
    $stmt -> execute();
    
    $result = $stmt -> fetchAll(); // 결과모드 : PDO::FETCH_BOTH

    echo "<pre>";
    print_r($result);
    echo "</pre>";
?>

PDOStatement::fetchColumn()

PDOStatement::fetchColumn()은 결과값 중 하나의 컬럼값만 가져오는 메소드 이다.

데이터 수를 조회할 경우 처럼 단 하나의 컬럼값만 필요한 경우 유용하게 사용할 수 있다.

<?php
   $dbHost = "localhost";

   $dbName = "test_db";
   $dbUser = "tester";
   $dbPass = "1q2w3e";
   $dbChar = "utf8";

   $dsn = "mysql:host={$dbHost};dbname={$dbName};charset={$dbChar}";
   $pdo = new PDO($dsn, $dbUser, $dbPass);

   $stmt = $pdo->prepare("SELECT COUNT(*) AS count FROM girl_group WHERE group_name = :group");
   $stmt->bindValue(":group", "TWICE");
   $stmt->execute();

   $row = $stmt->fetchColumn();

   echo "트와이스&nbsp;인원&nbsp;:&nbsp;" . $row;
?>

PDO:lastInsertId()

데이터베이스에 새로운 데이터를 입력하고 id 혹은 index값을 바로 사용해야 하는경우가 있다.

PDO::lastInsertId()를 객체를 이용하면 마지막으로 입력한 데이터의 id 혹은 index 값을 확인 할 수 있다.

※ PDOStatement가 아닌 PDO 객체를 사용하는것에 주의

<?php
   $dbHost = "localhost";

   $dbName = "test_db";
   $dbUser = "tester";
   $dbPass = "1q2w3e";
   $dbChar = "utf8";

   $dsn = "mysql:host={$dbHost};dbname={$dbName};charset={$dbChar}";
   $pdo = new PDO($dsn, $dbUser, $dbPass);

   $stmt = $pdo -> prepare("INSERT INTO girl_group (name, position, group_name, company, birthday) VALUE (:name, :position, :group, :company, :birthday)");

   $stmt -> bindValue(":name", "정채연");
   $stmt -> bindValue(":position", "센터, 서브보컬");
   $stmt -> bindValue(":group", "DIA");
   $stmt -> bindValue(":company", "MBK엔터테인먼트");
   $stmt -> bindValue(":birthday", "1997-12-01 00:00:00");

   $stmt -> execute();

   $taskIdx = $pdo->lastInsertId();

	echo "정채연&nbsp;등록번호&nbsp;:&nbsp;".$taskIdx;
?>

결과값 조회 - 모드 지정

결과를 가져오는 모드는 PDO::FETCH_로 시작하는 예약 상수를 PDOStatement::fetch(), PDOStatment::fetchAll() 메소드의 인수로 넘겨주어 지정한다.

자주 사용하는 예약 상수는 아래와 같다.

PDO::FETCH_BOTH

- PDOStatement::fetch(), PODStatement::fetchAll() 메소드에 가져오기 모드를 지정해주지 않으면 PDO:FETCH_BOTH 모드로 결과를 가져온다. 기본값

- PDO::FETCH_BOTH는 결과값을 가져올 때 데이터베이스의 칼럼 이름을 키로 사용하는 배열과 칼럼의 순서를 키로 사용하는 배열, 둘 다 만드는 방식이다.

- 두가지 배열을 만들기 때문에 당연히 성능은 좋지 않다.

PDO::FETCH_ASSOC

- 컬럼명을 키로 사용하는 연관 배열을 반환한다.

- 가져온 데이터는 $row['id']와 같은 식으로 사용한다.

PDO::FETCH_NUM

- 컬럼의 순서를 키로 사용하는 배열을 반환한다.

- 가져온 데이터는 $row[0] 과 같은 식으로 사용한다.

PDO::FETCH_OBJ

- 객체로 반환한다.

- 반환된 객체는 데이터베이스 컬럼명에 해당하는 프로퍼티를 갖고 있다.

- 가져온 데이터는 $row->id와 같이 사용할 수 있다.

PDO::FETCH_CLASS

- 지정한 클래스의 객체로 반환한다.

- PDO::FETCH_OBJ와 마찬가지로 컬럼명에 해당하는 프로퍼티에 값을 가진다.

예제)

<?php
	class iDol {
		private $idx;
		private $name;
		private $position;
		private $group;
		private $company;
		private $birthDay;

		public function getIdx() {
			return $this -> idx;
		}
		public function getName() {
			return $this -> name;
		}
		public function getPosition() {
			return $this -> position;
		}
		public function getGroup() {
			return $this -> group_name;
		}
		public function getCompany() {
			return $this -> company;
		}
		public function getBirthDay() {
			return $this -> birthDay;
		}
	}


    $dbHost = "localhost";

    $dbName = "test_db";
    $dbUser = "tester";
    $dbPass = "1q2w3e";
    $dbChar = "utf8";

    $dsn = "mysql:host={$dbHost};dbname={$dbName};charset={$dbChar}";
    $pdo = new PDO($dsn, $dbUser, $dbPass);

    $stmt = $pdo -> prepare("SELECT * FROM girl_group WHERE name = :name");
    $stmt -> bindValue(":name", "다현");
    $stmt -> execute();

	$stmt -> setFetchMode(PDO::FETCH_CLASS, "iDol"); // 위에서 지정한 Class형식에 따라 반환
	$row = $stmt -> fetch();

	echo $row -> getGroup();
	echo "<br/>";
	echo $row -> getName();
	echo "<br/>";
	echo $row -> getPosition();
	echo "<br/>";
	echo $row -> getCompany();
?>

레코드

레코드 추가

레코드 조회 쿼리를 실행하면 데이터베이스에서 레코드의 데이터가 반환된다.

이 레코드의 추가는 단순히 명령을 쓰기만 하기 때문에, 결과 데이터를 받을 필요가 없다.

이러한 쿼리의 실행은 query 대신 "exec"라는 메소드를 이용한다.

$sql = "INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum) VALUES(5, '이순신', '2016-02-16', 1108)";

$connect->exec($sql); // 반환 값은 업데이트된 레코드 수를 나타내는 int 값
// exec는 데이터의 추가 이외에도 여러가지 처리도 할 수도 있기 때문에, 
// "얼마나 많은 레코드가 업데이트 되었는지"를 알게 된다. 아무것도 변하지 않으면 반환 값은 0이 된다.

여러 레코드 추가

여러 레코드를 한 번에 추가하고 싶을 때는 beginTransaction() 메소드로 새로운 트랜젝션을 시작할 수 있다.

그리고 commit() 메소드로 해당 트랜젝션을 커밋(commit)할 수 있다.

트랜젝션(transaction)이란 데이터베이스에서 데이터의 일관성을 보장하기 위해 사용하는 가장 작은 작업 단위입니다.

커밋(commit)이란 해당 트랜젝션의 내용을 데이터베이스에 영구히 반영하는 것을 의미합니다.즉, 커밋(commit)된 이후에는 데이터베이스의 내용을 해당 트랜젝션 이전으로 되돌릴 수 없습니다.

// beginTransaction() 메소드를 호출하여 자동 커밋 모드(autocommit mode)의 사용을 중지
$connect->beginTransaction(); // 새로운 트랜젝션을 시작함.

$connect->exec("INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum) VALUES(1, '홍길동', '2016-01-05', 2014)");
$connect->exec("INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum) VALUES(2, '임꺽정', '2016-02-12', 918)");
$connect->exec("INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum) VALUES(3, '장길산', '2016-01-16', 1208)");

$connect->commit(); // 쌓아놓은 쿼리들을 커밋(commit)함.

레코드 수정

// SET 절을 통해 전달한 데이터로 Reservation 테이블의 레코드를 수정하는 SQL 구문

$sql = "UPDATE Reservation SET RoomNum = 2002 WHERE Name = '홍길동'";
$statement = $connect->prepare($sql); // prepare() 메소드는 인수로 전달받은 SQL 구문을 준비된 상태로 설정하고, 이에 해당하는 PDOStatement 객체를 반환
$statement->execute(); // execute() 메소드는 준비된 SQL 구문을 실행하고, 해당 구문에 의해 영향을 받은 레코드의 개수를 반환

echo $statement->rowCount()."개의 레코드 수정 성공!";

레코드 삭제

$sql = "DELETE FROM Reservation WHERE Name = '홍길동'";
$connect->exec($sql);

데이터베이스 생성

$sql = "CREATE DATABASE Hotel"; // Hotel이라는 이름의 데이터베이스를 생성하는 SQL 구문
$connect->exec($sql); // exec() 메소드는 인수로 전달받은 SQL 구문을 실행하고, 해당 구문에 의해 영향을 받은 레코드의 개수를 반환합니다.

테이블 생성

$sql = "CREATE TABLE Reservation
(
    ID INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    ReservDate DATE NOT NULL,
    RoomNum INT
)";

$connect->exec($sql);

# 참고자료

http://tcpschool.com/php/php_mysql_connection

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sunrc&logNo=221211680619

https://wickedmagica.tistory.com/16

https://araikuma.tistory.com/188