Hello you all,

I've got a question

Let's say I have a database width the table "news". It contains about 10 items which is ordered by the field "id".
Now from my admin page i do this:

And a few days later i do:


Now there are two gaps in the table => 1, 2, 3, 5, 6, 8, 9, 10 (no 4 and 7).
It want to reallocate the whole table to fill the gaps like this => 1, 2, 3, 4, 5, 6, 7, 8.
Of course true a php script which lies in my admin environment.

But I can't figure out how to, so i thought let's ask it here

Bakr_2k5

EDIT:
Perhaps it can be done with a temporary table or something like that, but still don't know really how. And it would be nicer if it could be done without the temporary table
END EDIT

This post has been edited by bakr_2k5: Dec 3 2006, 07:29 PM

Are you sure you want to do this? In case these IDs act as references (FOREIGN KEYS) to other tables - that might collapse the whole database structure and create a lot of orphaned/mis-linked records - and that's not desirable at all.

Anyways, I believe I'd come across a statement which does what you want - but can't recall it at the moment. Let me jog my memory a bit and I'll get back to you.

I don't have time to write the PHP script, so I'll just try to explain what I have in mind.

First, you select all the rows in that table. Next, you start a while loop that will go through all those rows. Also, you create a new variable, let's say $i, which is 1. Now, in the loop, if the current row isn't empty, you will save it in the same table, but the row will be $i, and then you will increase $i. If, however, the row is empty, just increase $i without saving anything.

What will you accomplish this way? You will go through all the rows, and save them one after another if they are not empty. Be aware, that this would leave you with a certain number of unwanted rows. You can delete this by starting from row $i (as $i will be the row after the last one).

I hope I was clear enough

@pyost:

This isn't exactly what I had in mind. You see, the "empty" rows don't exist. They are deleted true the mysql DELETE thing. Which ends up with 1,2,3,5,6,8,9,10 (4 and 7 are deleted) so those aren't empty they just don't exist. And I have set the "id" field from the "news" table to "auto increment". BUT when I delete a row 4&7, the id's don't get updated to "recount auto_increment" so it leaves a gap between id 3&5 and 6&8. Well there isn't more to say I think. Maybe you don't exactly understand what I mean, but I'm no professional in English ! Although your explanation was very clear but just not what I'm looking for. (Hmm... It could also be that I don't understand what you mean, but then I'll hear it from you )
Well thanks anyways!

@miCRoSCoPiC^eaRthLinG:

I hope you can dig it somewhere from your memory

Bakr_2k5

The gaps in the index for this table are not critical to anything. The Index does not need to be continuous at all, what-so-ever. As mentioned by m^e, you could create more grief by messing with this than it is worth to have nice, neat indexes. Let the MySql worry about that.

Yes that's completely true, but it was just a thought that popped into my head!
And it was more like a question if it was possible. But I'd guess it IS possible but like m^e and you said it's not worth it, and could break things. So I'll quickly "remove" the thought and go on with life

Thank you all anyways!!

Bakr_2k5

Oh, this makes it even easier. Again, you select all the rows, and create a variable $i = 1. In a "while" loop you go through all the rows and check if id is equal to $i. If it is, you just increase $i and go to the next row. If it is not, you update the current row so that id becomes $i, increase $i and move on to the next row.

I hope this is what you were asking for

That's EXACTLY what I meant! Thank you! Damn, I'm not clearly minded these days. It's so simple and even can't think of this! Hmm maybe I was thinking a bit too much the difficult way!

Anyway thanks, if I need it I know how to ... Although those posts earlier from m^e and jlhaslip kinda discouraged me to do this. But I might be handy sometime!

Bakr_2k5

Yes it is so simple and efective and works perfect, but, it only works if your column id is not a foreign key in another table, but dont worry, if you have such case simply add another SQL statement that deletes the rows referenced:
Also don't forget to complete the code to update all the rows with the foreign key column in the second table when id is not equal to $i.

You can also use ALTER TABLE to reset the AUTO_INCREMENT value:
Best regards,

I love this ! Because it's in a loop on the whole database ! So, in case of mistake, you detroyed the whole database !