lib_pdo.php

1 개요[ | ]

lib_pdo.php

lib_my.php를 PDO에 맞추어 개작한 것
프리페어드 스테이트먼트(prepared statement) 지원

2 소스 코드[ | ]

<?php
$dbconn = new PDO('mysql:host=호스트;dbname=디비명', '아이디', '패스워드',
	[PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"]);
$dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function str_replace_nth($search, $replace, $subject, $nth) {
	$found = preg_match_all('/'.preg_quote($search).'/', $subject, $matches, PREG_OFFSET_CAPTURE);
	if( $found === false || $found <= $nth ) return $subject;
	return substr_replace($subject, $replace, $matches[0][$nth][1], strlen($search));
}

// 여러 행 추출 ( N X N )
function query_rows() {
	global $dbconn;
	$raw_params = func_get_args();
	$query = array_shift($raw_params);
	$replaces = array();
	$params = array();
	foreach($raw_params as $i => $raw_param) {
		if(is_array($raw_param)) {
			$replaces[] = array( $i, count($raw_param));
			$params = array_merge($params, $raw_param);
			continue;
		}
		$params[] = $raw_param;
	}
	$replaces = array_reverse($replaces);
	foreach( $replaces as $replace ) {
		list( $position, $cnt ) = $replace;
		$qs = implode(',', array_fill(0, $cnt, '?') );
		$query = str_replace_nth('?', $qs, $query, $position);
	}
	
	try {
		$sth = $dbconn->prepare($query);
		$sth->execute($params);
	} catch (PDOException $e) {
		query_error(__FUNCTION__, $query, $params, $e->getMessage());
	}
	return $sth->fetchAll(PDO::FETCH_ASSOC);
}

// 단일 행 추출 ( 1 X N )
function query_row() {
	global $dbconn;
	$params = func_get_args();
	$query = array_shift($params);
	try {
		$sth = $dbconn->prepare($query);
		$sth->execute($params);
	} catch (PDOException $e) {
		query_error(__FUNCTION__, $query, $params, $e->getMessage());
	}
	$rows = $sth->fetchAll(PDO::FETCH_ASSOC);
	return isset($rows[0])? $rows[0]: false;
}

// 단일 컬럼 여러 행 추출 ( N X 1 )
function query_values() {
	global $dbconn;
	$raw_params = func_get_args();
	$query = array_shift($raw_params);
	$replaces = array();
	$params = array();
	foreach($raw_params as $i => $raw_param) {
		if(is_array($raw_param)) {
			$replaces[] = array( $i, count($raw_param));
			$params = array_merge($params, $raw_param);
			continue;
		}
		$params[] = $raw_param;
	}
	$replaces = array_reverse($replaces);
	foreach( $replaces as $replace ) {
		list( $position, $cnt ) = $replace;
		$qs = implode(',', array_fill(0, $cnt, '?') );
		$query = str_replace_nth('?', $qs, $query, $position);
	}
	
	try {
		$sth = $dbconn->prepare($query);
		$sth->execute($params);
	} catch (PDOException $e) {
		query_error(__FUNCTION__, $query, $params, $e->getMessage());
	}
	$arr = array();
	while($row = $sth->fetch(PDO::FETCH_NUM)) $arr[] = $row[0];
	return $arr;
}

// 단일 값 추출 ( 1 x 1 )
function query_one() {
	global $dbconn;
	$params = func_get_args();
	$query = array_shift($params);
	try {
		$sth = $dbconn->prepare($query);
		$sth->execute($params);
	} catch (PDOException $e) {
		query_error(__FUNCTION__, $query, $params, $e->getMessage());
	}
	$row = $sth->fetch(PDO::FETCH_NUM);
	return isset($row[0])? $row[0]: false;
}

// 쿼리 실행 ( INSERT / UPDATE / DELETE 등 )
function query() {
	global $dbconn;
	$params = func_get_args();
	$query = array_shift($params);
	try {
		$sth = $dbconn->prepare($query);
		$sth->execute($params);
	} catch (PDOException $e) {
		query_error(__FUNCTION__, $query, $params, $e->getMessage());
	}
}

// 마지막으로 생성된 AUTO_INCREMENT ID
function last_insert_id() {
	global $dbconn;
	return $dbconn->lastInsertId();
}

// 특정 테이블의 AUTO_INCREMENT 값
function get_auto_increment($table) {
	return query_row("SHOW TABLE STATUS WHERE Name LIKE ?", $table)['Auto_increment'];
}

function query_error($function_name, $query, $params, $message) {
	$error_message = "[$function_name] failed: [$message]";
	print_r($error_message);
	print_r($query);
	print_r($params);
	exit;
}

function db_use($db_name) {
	query("use $db_name");
}

3 사용예시[ | ]

$rows = query_rows("SELECT * FROM employees");
$ids = [42, 123];
$rows = query_rows("SELECT * FROM employees WHERE id IN (?)", $ids);

$id = 123;
$row = query_row("SELECT * FROM employees WHERE id=?", $id);

$names = query_values("SELECT name FROM employees");

$cnt = query_one("SELECT COUNT(*) FROM employees");
$dept_id = 50;
$dept_cnt = query_one("SELECT COUNT(*) FROM employees WHERE dept_id=?", $dept_id);

$row = ['dept_id'=>50, 'name'=>'홍길동'];
query("INSERT INTO employees (dept_id, name) VALUES (?, ?)", $row['dept_id'], $row['name']);

query("UPDATE menu
	SET rice=?, soup=?, side1=?, side2=?, side3=?, side4=?,
	msnack1=?, msnack2=?, afsnack1=?, afsnack2=?, adsnack1=?, adsnack2=?, memo=? 
	WHERE menudate=?",
	$rice[$i], $soup[$i], $side1[$i], $side2[$i], $side3[$i], $side4[$i],
	$msnack1[$i], $msnack2[$i], $afsnack1[$i], $afsnack2[$i], $adsnack1[$i], $adsnack2[$i], $memo[$i],
	$mdate[$i]);

4 같이 보기[ | ]

5 참고[ | ]

https://github.com/jmnote/php_lib/blob/master/lib_my.php