MySQL 데이터베이스에서 mysqli(MySQL Improved) 사용법

PHP에서 MySQL 데이터베이스에 연결해서 작업하는데 사용되는 API 가 세 종류 있습니다.

 

1. mysql API : 가장 오래전 부터 사용되어져 오는 것으로 mysql_ 로 시작하는 함수들로 구성되어 있습니다. 현재는 deprecated 되어서 더이상 사용하지 않는 것이 좋습니다.

 

2. mysqli (mysql improved) API (PHP 5, PHP 7) : mysql 함수를 개선한 것으로 prepared statement 를 사용할 수 있습니다.

 

3. PDO (PHP Data Object) API (PHP 5 >= 5.1.0, PHP 7) : PDO 는 PHP 를 사용하여 서로 다른 종류의 데이터베이스에 접근하는 공통 API 를 제공하는 것을 목표로 만들어 졌습니다. 객체 기반의 API 이고, prepared statement를 제공합니다.

 

이 중에서 mysqli 를 사용하는 방법을 알아 보겠습니다. mysqli 에서는 prepared statement 를 사용할 수 있게 되어서, 입력값에 대한 복잡한 제거나, 치환 작업 없이 SQL 인젝션 공격에 대한 방어를 할 수 있습니다. mysqli 는 객체지향 스타일(객체 메소드 호출 형태)의 API 와 절차지향 스타일(함수 호출 형태)의  API 를 제공합니다. 여기에서는 절차지향 스타일의 예를 보도록 하겠습니다.

 

 

1. 데이터베이스 연결하기

 

<?php
$db_conn = @mysqli_connect("localhost", "my_userid", "my_password", "my_db");

if (!$db_conn) {
	$error = mysqli_connect_error(); data-ke-type="codeblock"
	$errno = mysqli_connect_errno();
	print "$errno: $error\n";
	exit();
}

mysqli_close($db_conn);
?>

 

mysqli_connect 함수의 인자는 다음과 같습니다.

"localhost" : 데이터베이스 서버의 IP 또는 도메인 주소
"my_userid" : 데이터베이스 사용자 아이디
"my_password" : 데이터베이스 사용자 비밀번호
"my_db" : 사용할 데이터베이스명

 

이외에 기본 포트 3306 을 다른 것으로 변경하였다면, 다섯번째 인자에 새 포트를 입력하면 됩니다.

 

2. 간단한 조회 쿼리 사용하기

 

<?php
$query = "SELECT no, title, content FROM board";
$result = mysqli_query($db_conn, $query);

if ( $result ) {
	echo "조회된 행의 수 : ".mysqli_num_rows($result)."<br />";
	while ($row = mysqli_fetch_assoc($result)) {
		printf ("%s : %s <br />", $row["no"], $row["title"]);
	}

	// 결과 해제
	mysqli_free_result($result);
    
} else {
	echo "Error : ".mysqli_error($db_conn);
}
?>

 

$result로 부터 결과를 가져오는데는 다음 세 가지 함수를 사용할 수 있습니다.

 

- $row = mysqli_fetch_assoc($result); :  연관배열을 반환합니다. 컬럼명 ( $row['no'] ) 으로 값을 가져 올 수 있습니다. 컬럼명은 대소문자를 구분합니다.
- $row = mysqli_fetch_row($result); : 숫자 인덱스의 배열을 반환합니다. ( $row[0] )
- $row = mysqli_fetch_array($result); :  연관배열과 숫자 인덱스 배열을 모두 사용할 수 있습니다.

 

 

3. 파라미터를 가지는 조회 (prepared statement 사용하기)

 

<?php
$query = "SELECT no, title FROM board WHERE title LIKE ? AND no > ?";

$keyword = "%제목%";
$no = 5;

$stmt = mysqli_prepare($db_conn, $query);
if($stmt === false) {
	echo('Statement 생성 실패 : ' . mysqli_error($db_conn));
	exit();
}

$bind = mysqli_stmt_bind_param($stmt, "si", $keyword, $no);
if($bind === false) {
	echo('파라미터 바인드 실패 : ' . mysqli_error($db_conn));
	exit();
}

$exec = mysqli_stmt_execute($stmt);
if($exec === false) {
	echo('쿼리 실행 실패 : ' . mysqli_error($db_conn));
	exit();
}

$result = mysqli_stmt_get_result($stmt);
if ( $result ) {

	echo "조회된 행의 수 : ".mysqli_num_rows($result)."<br />";
    
	while ($row = mysqli_fetch_assoc($result)) {
		printf ("%s : %s <br />", $row["no"], $row["title"]);
	}

	// 결과 해제
	mysqli_free_result($result);

	// statement 해제
	mysqli_stmt_close($stmt);
    
} else {
	echo "Error : " . mysqli_error($db_conn);
}
?>

 

mysqli_stmt_bind_param($stmt, "si", $keyword, $no); 에서 처럼 파라미터 타입을 나타내는 문자열이  두 번째 인자로 주어지고, 그 위에 파라미터들을 입력합니다. $keyword 는 문자열이므로 "s", $no 는 정수이므로 "i" 를 각각 지정합니다.

 

파라미터 타입 문자열에 사용되는 값은 다음과 같습니다.

- i : integer
- d : double
- s : string
- b : blob

 

 

4. 입력, 수정, 삭제 쿼리 수행하기

 

<?php
$query = "INSERT board (no, title) values (?,?)";

$no = 5;
$title = "제목";

$stmt = mysqli_prepare($db_conn, $query);
if($stmt === false) {
	echo('Statement 생성 실패 : ' . mysqli_error($db_conn));
	exit();
}

$bind = mysqli_stmt_bind_param($stmt, "is", $no, $title);
if($bind === false) {
	echo('파라미터 바인드 실패 : ' . mysqli_error($db_conn));
	exit();
}

$exec = mysqli_stmt_execute($stmt);
if($exec === false) {
	echo('쿼리 실행 실패 : ' . mysqli_error($db_conn));
	exit();
}

echo "영향을 받은 행의 수 : ".mysqli_stmt_affected_rows($stmt)."<br />";

// statement 해제
mysqli_stmt_close($stmt);
?>

 

5. 자동증가 값 알아내기

 

$newNo = mysqli_insert_id($db_conn);

 

mysqli_stmt_execute($stmt) 가 성공하면 바로 호출하면 됩니다.

 

6. 트랜잭션 사용하기

 

PHP 5 이상 이고, MySQL은  InnoDB 엔진을 사용해야 합니다.

 

- 자동 커밋을 끄면 트랜잭션의 시작이 됩니다.

mysqli_autocommit($db_conn, FALSE);

- 성공하면 다음을 호출하여 커밋합니다.
mysqli_commit($db_conn);

 

- 실패하면 다음을 호출하여 롤백 합니다.
mysqli_rollback($db_connd);