codeigniter sort id after delete

Question

I want to rebuild id's after delete raw....

e.x

1 Text1 Number
2 Text2 Number
3 Text3 Number
4 Text4 Number
5 Text5 Number

Whan after delete raw 4. i want table look like:

1 Text1 Number
2 Text2 Number
3 Text3 Number
4 Text5 Number

And whan i add new item add to id 5

I work on codeigniter and my controller is:

delete function:

$this->db->query("DELETE FROM bookmark WHERE id = '$id'");

add function:

 $this->db->query("INSERT INTO bookmark VALUES ('$id', '$text', '$number')");

score 1 · Answer 1 · edited May 23 '17 at 12:07

1

After delete run this mysql command

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

In codeigniter active-records use as :-

$this->db->query("SET @num := 0;");
$this->db->query("UPDATE your_table SET id = @num := (@num+1);");
$this->db->query("ALTER TABLE tableName AUTO_INCREMENT = 1;");

Find more info here link

edited May 23 '17 at 12:07

Community

1
1

answered Feb 13 '16 at 10:52

Praveen Kumar

2,408
1
12
20

it's possible to make this automatic after delete row? In codeigniter php code – MartinKalco Feb 13 '16 at 11:11
I got this error: http://prntscr.com/a2teti – MartinKalco Feb 13 '16 at 18:31
Delete a row in phpmyadmin manually and run this query statement in phpmyadmin and tell me is it running fine there ? – Praveen Kumar Feb 15 '16 at 05:19

codeigniter sort id after delete

1 Answers1