Auto Re-Arrange after Delete Query

[Vanq69]

Hi guys,

I am having an issue with my query. Here is what I want it to do:

1. I have a list - chunk list - which in turn has pages of 9 list items each with an order number.

2. When i delete an item out of a page (eg item 5 out of 9 on page 1) i want the rest of the list to move up to accomodate this shift, leaving the last spot free.

3. I then want to move the first item of the next page into this last slot then repeat the process until the end of the pages

4. removing the last page if it only contained 1 item that is then moved to a previous page)

Currently I can do all of the above except for step 3, I have posted my code below in the hope someone can spot where I am going wrong.

n.b. I know it is possible to do this whole thing another way by using a large stream of order numbers then wrapping it, but that isnt suitable for my project.

case 'delChunk':
 
$Susername = $_SESSION['username'];
$Spagelimit = 8;
 
$result2 = $this->mysqlConnection->query('SELECT pageid ' . 'AS currentPage FROM chunks WHERE id="' . $content . '" AND username="' . $Susername . '"');
$row2 = $result2->fetch_assoc();
$currentPage = $row2['currentPage'];
 
$result = $this->mysqlConnection->query('SELECT (MAX(pageid)) ' . 'AS pageid FROM chunks WHERE username="' . $Susername . '"');
$row = $result->fetch_assoc();
$endPage = $row['pageid'];
 
 
if($currentPage == $endPage)
{
$result = $this->mysqlConnection->query('SELECT (MAX(order_no)) ' . 'AS maxOrder FROM chunks WHERE pageid="' . $currentPage . '" AND username="' . $Susername . '"');
$row = $result->fetch_assoc();
$maxOrder = $row['maxOrder'];
 
$result = $this->mysqlConnection->query('SELECT order_no ' . 'AS currentOrder FROM chunks WHERE id="' . $content . '" AND username="' . $Susername . '"');
$row = $result->fetch_assoc();
$currentOrder = $row['currentOrder'];
 
if($currentOrder == $maxOrder)
{
$currentPage = $currentPage - 1;
$result = $this->mysqlConnection->query('UPDATE users SET users.cpage="' . $currentPage . '" WHERE users.username="' . $Susername . '"');
}
 
$result = $this->mysqlConnection->query('DELETE FROM chunks WHERE id="' . $content . '"');
}
 
else
{					
$result = $this->mysqlConnection->query('DELETE FROM chunks WHERE id="' . $content . '"');
 
$previousPage = $startPage;
$startPage = $currentPage;
$startOrder = 0;
 
for($i=$currentPage; $i <= $endPage; $i++)
{
if(!$startPage)
{
$result = $this->mysqlConnection->query('UPDATE chunks SET order_no="' . $Spagelimit . '" AND pageid="' . $previousPage . '" WHERE pageid="' . $currentPage . '" AND order_no="' . $startOrder . '" AND username="' . $Susername . '"');
}
 
for($j=1; $j==($Spagelimit); $j++)
{
$result = $this->mysqlConnection->query('UPDATE chunks SET order_no="' . ($j+1) . '" WHERE pageid="' . $i . '" AND order_no="' . $j . '" AND username="' . $Susername . '"');
++$j;
}
 
$previousPage = $i;
++$i;						
}
 
}
 
$recreatedList = $this->BuildChunksList();
return $recreatedList;
break;

Thanks in advance

[Vanq69]

I use ajax to manage all of the actions so it does refresh, the part that doesnt work is taking the first item of the next list to place it on the last place of the current list.

E.G. List size 4
L1 = Current List
L2 = A Next Page

L1 L2
T1 T5
T4 T6
T3 T7
T2

Want to delete T3

What current happens:

L1 L2
T1 T5
T4 T6
T2 T7

What I want to happen is:

L1 L2
T1 T6
T4 T7
T2
T5

Perhaps this makes what I am trying to do a bit clearer?