Re-order MySQL Table

[bakr_2k5 0]

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:

<?PHPmysql_query("DELETE FROM news WHERE id=4");?>

And a few days later i do:

<?PHPmysql_query("DELETE FROM news WHERE id=7");?>

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
Edited December 3, 2006 by bakr_2k5 (see edit history)

[miCRoSCoPiC^eaRthLinG 0]

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.

[pyost 0]

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

[bakr_2k5 0]

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

[iGuest 3]

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.

[bakr_2k5 0]

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

[pyost 0]

This isn't exactly what I had in mind. You see, the "empty" rows don't exist.

 

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

[bakr_2k5 0]

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

[TavoxPeru 0]

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:

<?phpmysql_query("DELETE FROM news WHERE id=$n");mysql_query("DELETE FROM table_with_fk WHERE table_with_fk.id=$n");// $n = id to delete?>

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:

<?phpmysql_query("ALTER TABLE news AUTO_INCREMENT=$n");// $n = row count plus 1?>

Best regards,

[yordan 10]

mysql_query("DELETE FROM news WHERE id=$n");

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

[NoMore1405241533 0]

i havnt read all the rplays so i am sorry if ill write what outer ppl wroteyou can simply when taking out datas with PHP or any outer programing lang to increase number by one every time you take somting out,so you will get the real number and not the aout incresed number form the MySQL databaseor in for while (PHP mainly) to use the incereserNoMore

[bakr_2k5 0]

i havnt read all the rplays so i am sorry if ill write what outer ppl wroteyou can simply when taking out datas with PHP or any outer programing lang to increase number by one every time you take somting out,
so you will get the real number and not the aout incresed number form the MySQL database
or in for while (PHP mainly) to use the incereser

NoMore

Perhaps you should have read the replays since it's already solved
Of course I can't blame you .

Bakr_2k5