0

Trying to basically re-count the primary column, meaning updating from the first to the last, incrementing the value with each row. First I am selecting,

select
  `someid`,
  `time`
from
  `table`
ORDER BY
  `time` ASC

fetching the result,

$data = new SplFixedArray($query->num_rows);
$data = $query->fetch_all();

and updating the table.

for($i=1; $i<count($data); $i++){
   print_r("UPDATING | New someid: " . $i . " WHERE old someid: " . $data[$i][0] . " AND time = " . $data[$i][1] . "<br />");
   $query = $mysqli->query("UPDATE `table` SET `someid` = '" . $i . "' WHERE `time` = '" . $data[$i][1] . "' AND `someid` = '".$data[$i][0]."'");
}

What confuses me is, that the first few entries aren't sorted correctly. It's like

someid  | time
--------------
0       |    0
2       |    1
1       |    2
7       |    3
11      |    4
6       |    5

It's because of duplicates. After the 15ish rows in "someid" there is a gap and it jumps to 60. So after that there are no more duplicate someids. But how can I handle the first few rows? If I want to update someid to 2 but 2 is already existent?

Any way to handle that without using too many querys?

Edit:

Maybe it's unclear to some. Basically I am trying to refresh the int-values from a primary column. So basically I am counting them and enter their appropriate number into someid, sorted by sometime. Sometime is datetime. So the earliest "sometime" would get "someid" = 1.

At the moment, someid has gaps. 1..2..3..4..5..6..7..15..16..17..18..30..31..32 Updating, like I am doing now, doesn't work until I reach a gap. Because I am trying to write a duplicate value. Updating the first someid to 1, which might be of value 11, does not work because someid 1 is already existent somewhere.

I would update someid 15 to 8, because it's the next number. This works because 8 to 14 are missing, and I am not duplicating a value.

One solution might be to create another loop and first update every someid to some made up number, maybe start counting from row->count, incrementing for every row. After that, I can update them as I'd do normally. But isn't there a better solution?

// UPDATE:

This:

SET @count = 0;
UPDATE `table` SET `table`.`someid` = @count:= @count+ 1;

"Resets" the IDs, but it doesnt sort them by time.

Trying this:

SET @count = 0;
UPDATE `table` SET `table`.`someid` = @count:= @count+ 1;
ORDER BY `time` DESC;

Gives me the following error:

Duplicate entry '1' for key 'PRIMARY'

Dawg
  • 69
  • 9
  • 1
    they are sorted by "time", as you wrote ORDER BY time ASC. so what is wrong? or didn't i understand it? – Esteban P. Aug 10 '17 at 17:38
  • Currently they aren't, I want to sort them by time. So the first row, sorted by time, could have the someid 11. Now I want to update the entry with someid 11 and set it to 1. But 1 is already existent somewhere, since it's a primary key it doesn't work correctly. However, there are gaps in someid. So if I am at time 11, the ID might be 60 instead of 11. From there on, the ID increments. So 61, 62, 63.. Since there is a gap, there are no more duplicates. Is the only way temporarily changing every ID to a higher number than rowcount before finally changing them from 1 to rowcount? – Dawg Aug 10 '17 at 17:48
  • Is your column auto_incremented? – Ankit Bajpai Aug 10 '17 at 18:26
  • @AnkitBajpai Yes, it is. – Dawg Aug 10 '17 at 18:27
  • 1
    @Dawg Refer these links - https://stackoverflow.com/questions/1841104/how-to-fill-in-the-holes-in-auto-incremenet-fields https://stackoverflow.com/questions/9416471/fixing-gaps-in-mysql-table-row-id-after-we-delete-some-of-them https://stackoverflow.com/questions/740358/reorder-reset-auto-increment-primary-key https://stackoverflow.com/questions/12969161/make-auto-increment-fill-previously-deleted-number https://stackoverflow.com/questions/12967588/using-unused-primary-keys – Ankit Bajpai Aug 10 '17 at 18:29
  • Thank you. Still not what I am looking for, It's not sorted by time. – Dawg Aug 10 '17 at 18:55

0 Answers0