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.



Code PHP:
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