Bind SQL Result into PHP Array

Question

I've tried to creade a function to rebuild user id for all users after deleting one from them. In example:

test table:

id | text
1 | aaa
2 | bbb
3 | ccc
4 | ddd
6 | fff
7 | ggg
8 | hhh
9 | iii
10 | jjj

removed record : 5 | eee

I want to fix id for all users and also fix auto-increment for mysql

FIXED TABLE:

id | text
1 | aaa
2 | bbb
3 | ccc
4 | ddd
5 | fff
6 | ggg
7 | hhh
8 | iii
9 | jjj

function rebuild_user_id($mysqli) {

$row_count = get_dbt_rows_count('test', $mysqli);
$result = $mysqli->query("SELECT id FROM test ORDER BY id ASC");

    for ($i = 1; $i <= $row_count; $i++) {

    //Fetch all rows in result and store them in an array $rows
    $rows = array();
    while($row = $result->fetch_row()) {
        $rows[] = $row;
    }

    $currentid = $rows[$i];
    $mysqli->query("UPDATE test SET id = $i WHERE id = $currentid");
    }


// set fixed auto_increment
$ai_value = $row_count + 1;
$mysqli->query("ALTER TABLE `test` AUTO_INCREMENT=$ai_value");

}

I've tried any times with the same result - fixing auto_increment works but fixing id for all users ... ehh, any suggestions?

this is related stackoverflow.com/questions/2089492/how-to-reindex-mysql-table hope this helps — hek2mgl
Why are you trying to do this? Generally speaking, it's a very bad idea.™ — eggyal
Indeed, this is a VERY bad idea. What if you have data stored in your db, linked to this user table? You'll have to edit all those records for all the users coming after the user you just deleted... — redelschaap
don't do this, it's very dangerous. plus AUTO_INCREMENTED ids are free and afaik there's no cost even if you were to go up to 18446744073709551615 — sathia

Brian Anderson Brian Anderson · Accepted Answer · 2014-02-18T06:44:26

eggyal is correct that this appears to be bad practice, but hek2mgl got the correct answer and this question should be marked as answered:

this is related how-to-reindex-mysql-table hope this helps – hek2mgl

Bind SQL Result into PHP Array

1 Answers