因此,当我删除表中的一个条目时,我想更新每一列oneToFive
(从一到五的数字):
所以我的桌子看起来像这个
|oneToFive|name|
----------------
|1 |a |
|2 |b |
|3 |c |
|4 |d |
|5 |e |
假设我删除了名称为d的行,那么表将看起来像:
|oneToFive|name|
----------------
|1 |a |
|2 |b |
|3 |c |
|5 |e |
我希望它看起来像:
|oneToFive|name|
----------------
|1 |a |
|2 |b |
|3 |c |
|4 |e |
表格也可能没有满,例如,它可能是这样的:
|oneToFive|name|
----------------
|1 |a |
|2 |b |
|3 |c |
如果我删除了一个,我会想要它像:
|oneToFive|name|
----------------
|1 |b |
|2 |c |
因此,我尝试了这个疯狂的代码,其中我编写了所有的更新查询,然后执行删除行后存在的更新查询。但这行不通。
//find query
if($result === 5){
}else if($result === 4){
$query2 = "UPDATE faveMonth
SET oneToFive='4'
WHERE oneToFive='5'";
}else if($result === 3){
$query3 = "UPDATE faveMonth
SET oneToFive='3'
WHERE oneToFive='4'";
$query2 = "UPDATE faveMonth
SET oneToFive='4'
WHERE oneToFive='5'";
}else if($result === 2){
$query4 ="UPDATE faveMonth
SET oneToFive='2'
WHERE oneToFive='3'";
$query3 = "UPDATE faveMonth
SET oneToFive='3'
WHERE oneToFive='4'";
$query2 = "UPDATE faveMonth
SET oneToFive='4'
WHERE oneToFive='5'";
}else {
$query5 ="UPDATE faveMonth
SET oneToFive='1'
WHERE oneToFive='2'";
$query4 ="UPDATE faveMonth
SET oneToFive='2'
WHERE oneToFive='3'";
$query3 = "UPDATE faveMonth
SET oneToFive='3'
WHERE oneToFive='4'";
$query2 = "UPDATE faveMonth
SET oneToFive='4'
WHERE oneToFive='5'";
}
//run querys
if($result === 5){
}else if($result === 4){
if(mysqli_query ($con,$query2))
{
}
else
{
die("error updating other row(s)");
}
}else if($result === 3){
if(mysqli_query ($con,$query2))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query3))
{
}
else
{
die("error updating other row(s)");
}
}else if($result === 2){
if(mysqli_query ($con,$query2))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query3))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query4))
{
}
else
{
die("error updating other row(s)");
}
}else {
if(mysqli_query ($con,$query2))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query3))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query4))
{
}
else
{
die("error updating other row(s)");
}
if(mysqli_query ($con,$query5))
{
}
else
{
die("error updating other row(s)");
}
}
你能想出更好的方法吗?
您可以使用类似的单个语句
UPDATE faveMonth SET oneToFive = oneToFive - 1 WHERE oneToFive > x
使用oneToFive == x
删除条目后
您可以使用循环,而不是使用if else
链。
示例:
$result = ... // result from query
$count = ... // number of results in query
for ($i = 1; $i++; $i < $count)
{
// update $result[$i]'s oneToFive with $i
}
这不是一个完整的实现,只是一个想法。
它不是很有效,你应该使用kingkero
的解决方案。