Page 1 of 1

Cleaning empty ID spaces [SOLVED]

Posted: Wed Oct 29, 2008 8:13 pm
by spamyboy
So I have table with structure like

Code: Select all

ID | TITLE  | POSITION | PARENT
And sample data, let's say:

Code: Select all

1 | TEST | 1 | 2
2 | TEST | 2 | 2
3 | TEST | 3 | 2
And I delete ID 2, so we have

Code: Select all

1 | TEST | 1 | 2
3 | TEST | 3 | 2
But I need that POSITION always would be from 1 to count(records) where PARENT, for example 2.
I tried playing, that is:

Code: Select all

$result = $mysql->query("SET @counter = 0;");
    $result = $mysql->query("SELECT `static_id`,
                        IF (`static_position`=@counter, @counter:=@counter+1, @counter:=@counter+1) as `static_new_position`
                        FROM `gcms_static` WHERE `static_parent_id`='$parent_id' ORDER BY `static_new_position`, `static_id`") or die(mysql_error());
But that is just a sketch of my minds. Any help appreciated.

Re: Cleaning empty ID spaces

Posted: Wed Oct 29, 2008 10:15 pm
by s.dot
Well, what is it exactly that you are trying to achieve?

What I try to keep in mind is that ID fields are just that - identification of a record. You should never really have to depend on them to be in order without missing any (such as counting them).. so I'm thinking there's a more elegant solution to your problem.

Re: Cleaning empty ID spaces

Posted: Thu Oct 30, 2008 5:17 am
by spamyboy
Well what I'm trying to do is to make query that would fix `static_position` records in my table, making them from 1 to count() as many there are.
I need because if I delete one of records from table positioning script messes up.
In my script I order records by moving records UP/DOWN, that is UP for example would look like this:

Code: Select all

$mysql->query("UPDATE `gcms_static`
                          SET `static_position`=`static_position`-1
                          WHERE `static_position`='".++$row['static_position']."' AND
                          `static_parent_id`='".$row['static_parent_id']."';");
            
            $mysql->query("UPDATE `gcms_static`
                          SET `static_position`=`static_position`+1
                          WHERE `static_id`='".$row['static_id']."' AND
                          `static_parent_id`='".$row['static_parent_id']."';");
Maybe this is stupid, but I didn't managed to find other way for that. So if there will be missing any of position number in `static_position` this script won't work anymore.

Re: Cleaning empty ID spaces

Posted: Thu Oct 30, 2008 5:44 am
by spamyboy
Here I tried to be more detailed.

Code: Select all

So I have table with structure like
 
   ID | TITLE  | POSITION | PARENT
 
 
And sample data, let's say:
 
   1 | TEST | 1 | 2
   2 | TEST | 2 | 2
   3 | TEST | 3 | 2
   6 | TEST | 4 | 2
 
 
And I delete ID 2, so we have
 
   1 | TEST | 1 | 2
   3 | TEST | 3 | 2
   6 | TEST | 4 | 2
 
But I need that POSITION always would be from 1 to count(records) where PARENT, for example 2.
 
So I need to get result that would give me something like this
 
   ID | TITLE  | POSITION | NEW_POSITION | PARENT
 
   1 | TEST | 1 | 1 | 2
   3 | TEST | 3 | 2 | 2
   6 | TEST | 4 | 3 | 2

Re: Cleaning empty ID spaces

Posted: Thu Oct 30, 2008 5:50 am
by spamyboy
Solved.

Code: Select all

$result = $mysql->query("SET    @new_position=0;");
$result = $mysql->query("SELECT (@new_position:=@new_position+1) as `static_new_position`,`static_id`,`static_position` from `gcms_static` order by `static_position`;");