Cleaning empty ID spaces [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Cleaning empty ID spaces [SOLVED]

Post 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.
Last edited by spamyboy on Thu Oct 30, 2008 5:50 am, edited 1 time in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Cleaning empty ID spaces

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Re: Cleaning empty ID spaces

Post 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.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Re: Cleaning empty ID spaces

Post 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
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Re: Cleaning empty ID spaces

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