삭제된 wr_id 와 마지막 wr_id
본문
예를 들어 free 라는 게시판이 있다면
코멘트 포함에서 1부터 순차적으로 wr_id 가 생기잖아묘?
여기에서 게시글이나 댓글을 삭제할 경우 wr_id 가 없어지는데요.
그 없어진 wr_id 들을 모두 구하는 코드를 알고 싶어요.
그리고 마지막 wr_id 를 구하는 코드도 부탁드립니다.
감사합니다.
답변 2
아래의 코드를 한번 참고를 해보시겠어요..
<?php
include_once('./_common.php');
// 1. 삭제된 wr_id 목록 구하기
$sql_missing = "
SELECT wr_id + 1 AS missing_id
FROM g5_write_free
WHERE (wr_id + 1) NOT IN (SELECT wr_id FROM g5_write_free)
AND wr_id + 1 <= (SELECT MAX(wr_id) FROM g5_write_free)
ORDER BY wr_id
";
$result_missing = sql_query($sql_missing);
$missing_ids = array();
while ($row = sql_fetch_array($result_missing)) {
$missing_ids[] = $row['missing_id'];
}
echo "삭제된 wr_id 목록: " . implode(', ', $missing_ids) . "<br>";
// 2. 마지막 wr_id 구하기
$sql_last = "SELECT MAX(wr_id) AS last_wr_id FROM g5_write_free";
$row_last = sql_fetch($sql_last);
echo "마지막 wr_id: " . $row_last['last_wr_id'];
?>
<?php
include $_SERVER['DOCUMENT_ROOT'] . DIRECTORY_SEPARATOR . 'common.php';
$deleted_ids = [];
$tbl = 'g5_write_free';
$sql = "
SELECT wr_id - gap + 1 id_start, wr_id - gap + gap - 1 id_end FROM
(
SELECT @v, a.wr_id, a.wr_id - @v gap, @v := a.wr_id
FROM {$tbl} a INNER JOIN (SELECT @v := 0 FROM DUAL) tmp
ORDER BY a.wr_id
) tmp
WHERE gap > 1
";
$result = sql_query($sql);
while ($row = sql_fetch_array($result)) {
$deleted_ids = array_merge($deleted_ids, range($row['id_start'], $row['id_end']));
}
// deleted collection
print_r($deleted_ids);
// last ID of deleted collection
if (count($deleted_ids) > 0) {
echo $deleted_ids[count($deleted_ids) - 1];
}
// last ID of rows
$row = sql_fetch("SELECT wr_id FROM {$tbl} ORDER BY wr_id DESC LIMIT 1");
if (isset($row['wr_id']) == true) {
echo $row['wr_id'];
}
?>
