[RESOLVED] "squeeze in" a row into a table instead of at end

php_noob2

Hi all,

Let's say I've got a table containing pages for a book or manual using the auto increment id as the page number. This is fine until I want to stick in another page in between, rather than appending to the very end.

any ideas?

thanks!

coldwerturkey

make a new row in the table, pageOrder or something.

bradgrafelman

The order of the rows that MySQL outputs without any type of ORDER BY clause shouldn't be considered "ordered" at all.

php_noob2

Ok, sorry I can't connect the dots yet... but either way, if I am ordering by the AI id or creating another field with page number to order, if I add another row I still need to stick it in the middle and renumber those after.

id | info

1 stuff
2 stuff
3 stuff

Now if I want to stick "new stuff" in for id 2 and push the rest down for this:

id | info

1 stuff
2 new stuff
3 stuff
4 stuff

how could I do that? I know this is a lowbrow question but if I could get a tiny example or query I'd be even more thankful than for your time spend reading this 🙂

thanks!
phpnoob

paulnaj

Hey noob, it's not a lowbrow question.

You should, as already suggested, include a integer field (pageOrder, say).

Before you insert a new record, you would have to update other records. For example, if you want to insert a new record in position 7, say, then you would need to increment the pageOrder in every record that has pageOrder >= 7.

This can be done in one line of SQL ... something like:

UPDATE table 
SET `pageOrder` = `pageOrder`+ 1
WHERE `pageOrder`>= 7;

Then insert the new record setting it's pageOrder = 7.

Hope that helps.

Paul.

php_noob2

THANK YOU!!! This worked perfectly and makes sense. So easy too 🙂

bradgrafelman

Also note that your original model in post #4 above suggests a conceptual error: If you have an AUTO_INCREMENT column in your table, you should never change these id's; doing so destroys relations. This is why others have suggested adding a new "orderColumn" column to your schema that you'd freely update whenever the order changes while maintaining the same id relationship with each row of data.