[SOLVED] How to change data in the next row from an auto-increment table?

[qwerpoiu338]

Hi, Newbie in PHP here. I'm looking for a way to update data in the next row once the condition of the row above is met. The table will be auto-incremented. Almost like a positional pointer.

[fenway]

MySQL is based upon sets, so "next" doesn't really mean anything -- what are you trying to achieve?

[qwerpoiu338]

Trying to set a marker for a row in an ordered list (Auto-increment table). The marker will be represented with a "1" in the column, the rest will be "0". Once a condition is met, the marker ("1") will move down one position down the ordered list. Maybe there is an easier way. Any suggestion? Thanks in advance!

[fenway]

Sounds like you need to use a cursor... but you'll need a server-side script to use it.

[bubblegum.anarchy]

If there are no gaps in the auto_increment ids, something like this may work:

SELECT @id:=id FROM table WHERE id = ( SELECT id + 1 FROM table WHERE marker = 1 );
UPDATE table SET marker = 0 WHERE marker = 1;
UPDATE table SET marker = 1 WHERE id = @id;

[qwerpoiu338]

Unfortunately there might be gaps in the auto-increment field. Will this work also? Set the table as an array and use the "next($array)" array pointer to change the elements, then writes it back to the table.

[fenway]

I'm still confused as to what you're actually trying to do.

[bubblegum.anarchy]

fenway:

 

As far as I understand, qwerpoiu338 is trying to move a marker across records in a table, the following table data:

id     | marker | other_fields_in_table_that_are_irrelevant
1      | 0      |
2      | 0      |
3      | 0      |
7      | 1      |
10     | 0      |
14     | 0      |

 

after a marker move would result in the following (The marker moved to the next record):

id     | marker | other_fields_in_table_that_are_irrelevant
1      | 0      |
2      | 0      |
3      | 0      |
7      | 0      |
10     | 1      |
14     | 0      |

 

And no qwerpoiu338, the query I posted would only work on records with NO gaps in the auto increment id.

[bubblegum.anarchy]

This may work accordingly:

 

SELECT @id:=id FROM table WHERE id > ifnull(( SELECT id FROM table WHERE marker = 1 ), 0) ORDER BY id LIMIT 1;
UPDATE table SET marker = 0 WHERE marker = 1;
UPDATE table SET marker = 1 WHERE id = @id;

 

The marker is set to the first record if no marker is present and just stops at the last record ordered by id.

[fenway]

I just don't see why... besides, it's not thread-safe anyway.

[qwerpoiu338]

Thanks for the help. esp bubblegum.anarchy. Your example worked quite well.