CODEWITHSUNDEEP
mysql
Nathan Martin
Nathan Martin

Reputation: 305

MySQL, Insert Row in-between two others

I'm coding a website for a photographer and I'm currently working on gallery implimentation. I need to be able to take a row from point n.a and move it to point n.b Here's an example of the raw table:

|gallery_img               |
|--------------------------|
| id | fk_gal | fk_img | o |
|    |        |        |   |
| 0  | 16     | 240    | 1 |
| 1  | 16     | 322    | 2 |
| 2  | 27     | 240    | 1 |
| 3  | 16     | 245    | 3 |
| 4  | 16     | 210    | 4 |
| 5  | 27     | 530    | 2 |

All fields are INT(11). 'id' Auto_increments. 'fk_gal' and 'fk_img' are linked to other, irrelevant, tables via FOREIGN_KEY.

Now, 'o' is the field I'm focusing on. It determines what order the images will be displayed on the website. This value needs to always be unique for each table. To clarify, If I only call one table, 'o' should be different in every row. However, if I call the entire table, 'o[0]' might reoccur a few times.

So here's what I need. Firstly, I'm only going to be running this function on only one gallery at a time so all visuals of the table from here on out are going to be filtered with 'SELECT * FROM gallery_img WHERE fk_gal = 16'. I need to change 'o' from n to n2 which will effectively move it on the database.

|gallery_img               |
|--------------------------|
| id | fk_gal | fk_img | o |
|    |        |        |   |
| 0  | 16     | 240    | 1 |
|    |        |        |   |  <--
| 1  | 16     | 322    | 2 |+   |
| 3  | 16     | 245    | 3 |+   |
| 4  | 16     | 210    | 4 |  --|

The code needs to move the desired row (in this example 'o=4') to 1 and simultaneously move all of the next rows down to prevent any reoccurrences. Here's my code I have right now. I'm coding my MySql scripts via PHP. I am using the $n variable here. It includes the following data:

$n = array(gallery_id,img_id,target_o);

            sql("UPDATE gallery_img SET o = o + 1 ORDER BY o ASC LIMIT ". ($n[2] - 1) .", 18446744073709551615;");
            sql("UPDATE gallery_img SET o = ". ($n[2] + 2) ." WHERE fk_img = $n[1] AND fk_gal = $n[0];");

The problem I'm having with this is that when I execute it I get one of these two outputs:

|gallery_img               |
|--------------------------|
| id | fk_gal | fk_img | o |
|    |        |        |   |
| 0  | 16     | 240    | 1 |
| 4  | 16     | 210    | 1 | <-- Shouldn't be duplicate
| 1  | 16     | 322    | 2 |
| 3  | 16     | 245    | 3 |


|gallery_img               |
|--------------------------|
| id | fk_gal | fk_img | o |
|    |        |        |   |
| 0  | 16     | 240    | 1 |
| 4  | 16     | 210    | 2 |
| 1  | 16     | 322    | 4 |-|
| 3  | 16     | 245    | 4 | |-- Shouldn't be duplicate
| 5  | 16     | 273    | 4 | |
| 6  | 16     | 14     | 4 |-|

A good way to think of it is as so:

UPDATE

If you have any questions please let me know! Thanks ahead of time for your help!

Upvotes: 0

Views: 191

Answers (2)

The Impaler
The Impaler

Reputation: 48770

Can I suggest a hack? For the column o don't use an integer number, but a DOUBLE PRECISION one.

It would be much easier to insert a row in between, just by averaging the values of the previous and next row. If you need to insert between 3 and 4, you can just insert a row with 3.5.

Of course, after some time (after 50 times at least) you would like to re-number those values, since a DOUBLE PRECISION has 53 bits for the mantissa.

Upvotes: 2

AbraCadaver
AbraCadaver

Reputation: 78994

So if you're wanting to change the row WHERE o=4 to o=1 then increment the number to be replaced and all greater numbers.

UPDATE gallery_img SET o = (o + 1) WHERE o >= 1

Then update the row that you want to be o=1:

UPDATE gallery_img SET o = 1 WHERE fk_img = something1 AND fk_gal = something2

Or if you only know the o use o=(4+1) since it changed in the last UPDATE:

UPDATE gallery_img SET o = 1 WHERE o = 5

Upvotes: 3

Related Questions