我有一个MySQL表
|id|page |page_number
|1 |name1 |3
|2 |name2 |2
|3 |name3 |1
|4 |name2 |4
用户可以添加新页面,但必须在当前页面之后。例如,当前页面为1(页码),那么新页面(页码)必须为2,然后在1之后的所有其他页面必须更新页码+1,并插入页码=2的新页面。或者可能首先我必须插入新页面,然后更新所有页码?
这里的标准解是什么,有标准解吗?
我试过了:
$count = 1;
// loop over all pages
foreach($pages->fetchAll() as $page){
// if count is less than new-page order then continue add 1 to
// counter
if($new_page_order > $count){
$count++;
continue;
}
// get unique id for current page
$current_id = $page['id'];
$new_order= $count+1;
// update current page with page_order+1
$query = $conn->prepare("UPDATE pages SET
page_order='".$new_order."' WHERE id=".$current_id);
$query->execute();
//add +1 to count, because it is used for page_order value
$count++;
}
// add new page
$query = $conn->prepare("INSERT INTO pages (columns... page_order)
VALUES( values , ..., $new_page_order)");
由于您只需要更新插入的页码之后的页码,因此您可以运行如下查询:
"UPDATE pages SET page_number = page_number + 1 WHERE page_number >= ".$current_id