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
– hek2mgl, Commented Feb 16, 2014 at 14:38
Why are you trying to do this? Generally speaking, it's a very bad idea.™ — eggyal
– eggyal, Commented Feb 16, 2014 at 14:42
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
– redelschaap, Commented Feb 16, 2014 at 22:05
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 Musso
– Sathia Musso, Commented Feb 17, 2014 at 15:40

Community · Accepted Answer · 2017-05-23 11:57:47Z

0

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

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Feb 18, 2014 at 6:44

Brian Anderson

6217 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bind SQL Result into PHP Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related