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
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
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`;");