Re-aligning autoassigned id numbers on insert

VooDooRT

Ok, as some of you may have read in a former post, I'm trying to set up next/back buttons based on database queries. If there IS another entry, I get a next, if there IS a former entry, I get a back. Well, I fixed that problem, unefficently, but fixed. What I'm having problems with now is moving around a null entry.

Say there are autoassigned IDs of 1, 2, 4, 5, 6, 8, 10. How do I move around 3, 7, and 9? I have an idea, but I'm not sure how to do it. I think would solve a lot of problems in the long run too.

What I want to do is re-align the database IDs on each INSERT, DELETE, or UPDATE so that the first entry in the database will ALWAYS be id #1 and all the following records would be in line, with no missed ID numbers. So, instead of:
1-2-4-5-6-8-10 you would have
1-2-3-4-5-6-7

Ideas?

rrijnders

You want to re-assign auto incremented table keys?! Are you maaaAAAaaddd!

Seriously; you do not want to re-assign table keys. Very bad idea.

I don't understand what you mean by "move around 3, 7, and 9". Why don't you give some more detail on your problem and we'll see if we can't find a simpler solution.

ednark

ok check this out. its a little non-intuative... use limit.

LIMIT <start here>,<return this many>

I don't know your circumstances so this may not work for you.

if you are just stepping through a sequence you can ,instead of asking for the object directly, just ask for its place in the table.

table T

1 a
2 b
8 c

SELECT * FROM T LIMIT 0,1

this will pull out starting from 0 (first row), 1 item, which will be (1, a)

the page that does this simply needs to be told (pull out item 0) then the next button will ask for item 1

SELECT * FROM T LIMIT 1,1

will pull out (2, b)

maybe this helps

ednark

ok check this out. its a little non-intuative... use limit.

LIMIT <start here>,<return this many>

I don't know your circumstances so this may not work for you.

if you are just stepping through a sequence you can ,instead of asking for the object directly, just ask for its place in the table.

table T

1 a
2 b
8 c

SELECT * FROM T LIMIT 0,1

this will pull out starting from 0 (first row), 1 item, which will be (1, a)

the page that does this simply needs to be told (pull out item 0) then the next button will ask for item 1

SELECT * FROM T LIMIT 1,1

will pull out (2, b)

maybe this helps

rrijnders

You want to re-assign auto incremented table keys?! Are you maaaAAAaaddd!

Seriously; you do not want to re-assign table keys. Very bad idea.

I don't understand what you mean by "move around 3, 7, and 9". Why don't you give some more detail on your problem and we'll see if we can't find a simpler solution.

ednark

ok check this out. its a little non-intuative... use limit.

LIMIT <start here>,<return this many>

I don't know your circumstances so this may not work for you.

if you are just stepping through a sequence you can ,instead of asking for the object directly, just ask for its place in the table.

table T

1 a
2 b
8 c

SELECT * FROM T LIMIT 0,1

this will pull out starting from 0 (first row), 1 item, which will be (1, a)

the page that does this simply needs to be told (pull out item 0) then the next button will ask for item 1

SELECT * FROM T LIMIT 1,1

will pull out (2, b)

maybe this helps

VooDooRT

I'm trying to set up a success story box, that pops up a new window for a full success story.

Everything is tracked by the ID number, which is auto-increment. I set up next/back buttons to read ID +1 or ID -1 which goes to the next/previous record. However, if there ISN'T a record, it won't show the Next/Back buttons, because I have it looking to see if there is a NULL entry in the name field. If there IS a NULL entry in the name field, it's because that entry in the database was deleted. So, how do I got from ID3 to ID5 when it's looking at ID4 saying the name is NULL because there is no ID4?

Following me? Sorry if I'm not making sense here...lol

ednark

i have a feeling you can get away with this. here is some code minus the database specifics. I use something similar to make the "page X of Y" results like google.

this code does not care what the next id is specifically, but will succesfully pull out each id in order from the database for you to display.

however, this code does not take into the count calculating an specific stories relative position in the table. so you can't specify an stories id, and then start navigating from there, you would have to specify only the relative position in the table

/// look for teh current story to view, if we have sent it previously, the relative position in the table
$current_start = $_POST['start'];

/// if not sent specifically, set default
if  ( !$current_start ) {
    $current_start = 0;
}

/// get total things in databse, 
$total = "SELECT COUNT(*) FROM table";

/// get the one item you are looking for
$current = "SELECT * FROM table ORDER BY id LIMIT $current_start,1";

/// do your popup
echo $current['id'];
echo $current['story_text'];

/// if this is not the first item, print prev button
if ( $current_start > 0 ) {
    $action ="./thispage?start=". ($current-1);

echo "<form action='$action'>";
echo "<input type='submit' value='Prev'>";
echo "</form>";


}


/// if this is not the last entry, print the next button 
if ( $current_start < $total ) {
    $action ="./thispage?start=". ($current+1);

echo "<form action='$action'>";
echo "<input type='submit' value='Next'>";
echo "</form>";

}

after reading your description i'm still of the opinion that this is really what you need, if it is not however please describe further what you problem is.

VooDooRT

Top...

michael

One thing I think the other posters are trying to tell you, the limit directive returns the X number of records.

Thus, if your primary key was called id, and your table has posts with ids: 1, 10, 100

And you do select * from mytable limit 2,1

It would return only entry id 100.

You do not need to renumber those. You can do :
UPDATE mytable SET id = # limit 1

To change the id, but I don't reccommend it. The next id would be the natural next. I cannot recall how to reset that. I think if you remove the pri-index and readd it ... ?