Update with an incremented value based on corresponding row

Question

Here's a problem for the PHP-juggler in you. I want to use plain-ol' mysql_* functions of PHP.

I have the following MySQL table:

+-----+-----------------+
|  id | thread          |
+-----+-----------------+
|  14 | 01/             |
|  14 | 02/             |
|  14 | 03/             |
|  15 | 01/             |
|  22 | 01/             |
|  24 | XXX             |
|  24 | XXX             |
|  24 | XXX             |
|  24 | XXX             |
|  32 | XXX             |
|  32 | XXX             |
+-----+-----------------+

The "XXX" values are my making. I want to change (UPDATE) that table to this one:

+-----+-----------------+
|  id | thread          |
+-----+-----------------+
|  14 | 01/     <-      |
|  14 | 02/             |
|  14 | 03/             |
|  15 | 01/     <-      |
|  22 | 01/     <-      |
|  24 | 01/     <-      |
|  24 | 02/             |
|  24 | 03/             |
|  24 | 04/             |
|  32 | 01/     <-      |
|  32 | 02/             |
+-----+-----------------+

On every new value of the "id" field (where the "<-" is; my making, also), the "thread" field value has to reset itself to "01/" and continue incrementing until a new value of "id" is found.

I've tried querying with COUNT(id) to increment somehow. I tried storing in arrays. I thought of mysql_data_seek() also. Alas, I don't seem to pull it off.

---

I got the "thread" format right, though:

$thread = $i < 10 ? "0$i" : $i;

So, if it's bigger than 10, it doesn't get a leading zero. But this is just the fun part.

---

Any help would be appreciated.

Thanks

Answer 1

Set PRIMARY KEY the tuple (id,thread) and set thread (but not the id!) as AUTO_INCREMENT, then run the query

INSERT INTO mytable (id) VALUES (24),(24),(32),(24),(32),(24)

and thread attribute should be set autoincrementally. If you insist on "0n/" form, I suggest to create thread_string attribute and create BEFORE UPDATE trigger according to NEW.thread attribute. Does it work?

Answer 2

SET @oldid = 0;
SET @counter = 0;
UPDATE tablename
SET thread = CONCAT(
  LPAD(
     CAST(IF(id = @oldid,
       @counter := @counter + 1,      -- same id, increment
       @counter := (@oldid := id)/id) -- other id, set to 1 
       AS UNSIGNED),
     2,'0'),                          -- pad with '0'
  '/')                                -- append '/'
WHERE thread = 'XXX'                  -- or enumerate the whole thing if need be
ORDER BY id, thread;

Which can just be fed to "plain ol' mysql_query" (3 in a row: feed the SET & UPDATE queries separately, alternatively forget about SETting anything, I just hate uninitialized variables ;)