1

enter image description here

I have such Table structure in MySql Database and based on that I have to design a GUI in php and Html that would let me move a child's sequence under a particular parent up or down.

I have refered How can I reorder rows in sql database It helped me upto certatin extent but I am still facing some logical issues as my question is somewhat complex according to me.

The condition to be considered are:

  • Child nodes of a parent could change the sequence under that respective parent only.

  • Child node would only shift up or shift down. If a node is first it wont go up any further, and if its last node it wont go down any further.

What I tried is I created an array in Php that would get the Id's of childrens under a parent.

So for "Parent 1" I will have child Id's as [2,3,4].

As we have the Id's of child node what should I operate so that I could Interchange the sequence number.

I have tried this code

<?php 
function getid(){
    include 'dbconn.php';
    $id=22;//$_GET['id'];
    return $id;

}
function selectseqno(){
    include 'dbconn.php';
    $id=getid();
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    $sql = "select seq from pages where id =".$id;
    mysql_select_db('cms');
    $retval = mysql_query( $sql, $conn );
    $rows = mysql_num_rows($retval);
    if(! $retval)
    {
      die('Could not get data: ' . mysql_error());
    }else 
        {
            while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
            {
                $s=$row['seq'];
            } 
            return array($s, $id);
        }
}

function updatesequence(){
    include 'dbconn.php';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    $seq=selectseqno();
    $seqno=$seq[0];
    $pageid=$seq[1];
    $seqnoPlus1=$seqno+1;
    $seqnoMinus1=$seqno-1;
    $sql = "update pages set seq=".$seqnoPlus1." where id=".$pageid;
    mysql_select_db('cms');
    $retval = mysql_query( $sql, $conn );
    $rows = mysql_num_rows($retval);
    if(! $retval)
    {
      die('Could not get data: ' . mysql_error());
    }else 
        {
        }   
    $sql2 = "update pages set seq=".$seqnoMinus1." where id=".$pageid;
    mysql_select_db('cms');
    $retval = mysql_query( $sql, $conn );
    $rows = mysql_num_rows($retval);
    if(! $retval)
    {
      die('Could not get data: ' . mysql_error());
    }else 
        {
        }   
}
updatesequence();
?>

Please excuse me I havnot posted the array code as its going too lengthy.

Based on this logic

Update MyTable
    Set ListOrder=ListOrder+1
        Where ListOrder=(Select ListOrder-1 From MyTable where BookMark='f')

Update MyTable
    Set ListOrder=ListOrder-1
        Where BookMark='f'

provided by Mr. Mike Lewis on this link How can I reorder rows in sql database

The problem that I am facing:

If I change the sequence of "Child-1.1" form "1" to "2" say I want to move it down then it will change the sequence of "Child-1.1" to "2" but it is failing to change the sequence no of "Child=1.1" to "1".

What logic could I apply to solve this?

Community
  • 1
  • 1
Sumeet Gavhale
  • 800
  • 4
  • 13
  • 39
  • 1
    You are using the same $pageid on both SQL updates.. Which can't be right, right? – Naruto Apr 16 '15 at 07:42
  • 1
    BookMark column is not in your sample schema. Please update your question. Generally, it seems at least you need to change = (select) in the first update to IN (SELECT), since, the subquery may return many rows. Plus, you need to limit the rows returned by the subquery so that not all rows of a bookmark are updated, but only those that precede (or follow) the row which needs to have its place changed. – Kostja Apr 16 '15 at 07:46
  • I know this isnt going right. Even the functions I have written are useless. I have tried ways but not able to achieve the above goal... Is there any other way to achieve it – Sumeet Gavhale Apr 16 '15 at 08:35

0 Answers0