ADOdb 테이블 생성, 변경, 삭제 + 데이터 조회, 처리

ADOdb 테이블 생성, 변경, 삭제, 조회

 

1. 테이블 생성 -> createTableSQL($tabname, $fields)

$db = get_connection();
$dict = NewDataDictionary($db);
$tabname = "table1";
$fields = " 
       id INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY
       ,name   VARCHAR(255)  NOT NULL
       ,content   TEXT  NOT NULL
       ,insertdate   DATETIME NOT NULL
       ,deleteyn VARCHAR(10) NOT NULL DEFAULT 'N'
";

$sqlarray = $dict->createTableSQL($tabname, $fields);
$dict->executeSqlArray($sqlarray);

//쿼리
//CREATE TABLE table1 (
//       id INTEGER      NOT NULL AUTO_INCREMENT PRIMARY KEY
//       ,name   VARCHAR(255)  NOT NULL
//       ,content   TEXT  NOT NULL
//       ,insertdate   DATETIME NOT NULL
//       ,deleteyn VARCHAR(10) NOT NULL DEFAULT 'N'
//)

$tablename와 $fields는 string 이어야한다.

 

$opts    = array('MYSQL'=>"ENGINE INNODB,COMPRESSION 'ZLIB'",
                 'DB2'=>'TABLESPACE LARGE_SPACE');
 
$sql = $db->createTableSql($tabname,$flds,$opts);

위와 같이 옵션을 설정할 수 있는데, 옵션은 array를 사용한다.

 

---

 

2. 테이블 변경 -> changeTableSQL($tabname, $fields)

$db = get_connection();
$dict = NewDataDictionary($db);
$tabname = "table1";
$fields = " 
   COL1 VARCHAR(5) NOT NULL DEFAULT 'N'
  ";
$sqlarray = $dict->changeTableSQL($tabname, $fields);

$dict->executeSqlArray($sqlarray);

// 쿼리
// ALTER TABLE table1 ALTER COLUMN COL1 SET DATA TYPE VARCHAR(5) DEFAULT 'N'

 

---

 

3. 테이블 삭제 -> dropTableSql($tabname)

$db = get_connection();
$dict = NewDataDictionary($db);
$tableName = "table1";
$sqlarray = $dict->dropTableSql($tableName);
$dict->executeSqlArray($sqlarray);

//DELETE FROM table1

 

---

 

4. 데이터 조회

1) Execute

$db = get_connection();

$sql = "SELECT * FROM table1 WHERE 1=1";
$stmt = $db->Prepare($sql);
$rs = $db->Execute($sql);

if (empty($rs)) { //$rs->EOF
	echo "값 없음.";
}else {
	while($data = $rs->FetchRow()){
		print_R($data);
		echo "<BR><BR>";
	}
}

$db->close();

 

2) PageExecute ( 페이징 )

$db = get_connection();

$rows = 10;
$page = 1;

$sql = "SELECT * FROM table1 WHERE id = ? AND name = ?";
$stmt = $db->Prepare($sql);
array_push($param, "1"); //id 검색 값
array_push($param, "홍길동"); //name 검색 값

$rs = $db->PageExecute($stmt, $rows, $page, $param);

if (empty($rs)) { //$rs->EOF
	echo "값 없음.";
}else {
	while($data = $rs->FetchRow()){
		print_R($data);
		echo "<BR><BR>";
	}
}

$db->close();

//=> $pager->RenderNav()

 

3) GetRow

$db = get_connection();

$id = "1";

$sql = "SELECT * FROM table1 WHERE id = ?";
$stmt = $db->Prepare($sql);
$rs = $db->GetRow($stmt, array($id));

$db->close();

 

4) GetOne (한 건만 조회)

$db = get_connection();

$sql = "SELECT max(id) as max_id FROM table1";
$stmt = $db->Prepare($sql);
$row = $db->GetOne($stmt);
if (empty($row)) {
	$max_id = 1;
}else {
	$max_id = $row;
}

$db->close();
;

 

---

 

5. 데이터 처리

 

1) Excute로 처리 + 트랜잭션 적용

$del_id = $_REQUEST["del_id"];

$db = get_connection();
$sql = "UPDATE table1 SET deleteyn = 'Y' WHERE id = ?";
$stmt = $db->Prepare($sql);

$db->StartTrans();
foreach ($del_id as $del_id) {
    $result = $db->Execute($stmt, array($del_id));
}
$db->CompleteTrans();

if ($db->HasFailedTrans()) {
    $msg = "처리도중 오류가 발생하였습니다.";
    if ($CONFIG["debug"]) $msg .= "\n".$db->ErrorMsg();
} else {
    $msg = "정상적으로 처리 되었습니다.";
}

$db->close();

echo $msg;

 

2) autoExecute로 처리

$tabname = "table1";
$data = array();
$data['name'] = "홍길동";
$data['content'] = "test //";
$data['insertdate'] = "1970-01-01";
$data['deleteyn'] = "N";

//Insert
$db->autoExecute($tabname,$data,'INSERT');

//Update
$db->autoExecute($table, $record, 'UPDATE', 'id = 1');

 

참고) Insert 된 id 값 조회 : insert_Id

//Insert 된 id 값 조회
$db->execute('INSERT INTO table1 ...');
$id = $db->insert_Id();
echo $id;