CodeIgniter Forums
What's wrong with mysql reset auto increment in codeigniter? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: What's wrong with mysql reset auto increment in codeigniter? (/thread-68053.html)



What's wrong with mysql reset auto increment in codeigniter? - Ukasyah - 05-17-2017

Hi, I'm a newbie in codeigniter. So far, I've tried to reset auto increment field in mysql using codeigniter. 

Code:
// Location: rb_model.php
function reset_ai() {
   $sql = "SET [email protected] := 0; UPDATE rancangan_bulanan SET id = @num := (@num+1);";
   $this->db->query($sql);
}

Unfortunately, it give me an error:
Code:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE rancangan_bulanan SET id = @num := (@num+1)' at line 1

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

Filename: C:/xampp/htdocs/belajar/rkape/system/database/DB_driver.php

Line Number: 691

It's really strange, because the code was working in sql command line.

Any suggestion?


RE: What's wrong with mysql reset auto increment in codeigniter? - skunkbad - 05-17-2017

ALTER TABLE rancangan_bulanan AUTO_INCREMENT = 1


RE: What's wrong with mysql reset auto increment in codeigniter? - Ukasyah - 05-17-2017

(05-17-2017, 08:26 PM)skunkbad Wrote: ALTER TABLE rancangan_bulanan AUTO_INCREMENT = 1

Thanks for your effort, but I also want the filled record's id are rearranged too. Can you help me?


RE: What's wrong with mysql reset auto increment in codeigniter? - Rufnex - 05-18-2017

Why an reset? Normaly you do a "truncate table" for an reset. but you have to think about dependences.


RE: What's wrong with mysql reset auto increment in codeigniter? - InsiteFX - 05-18-2017

ALTER TABLE tablename AUTO_INCREMENT = 1

There is a very easy way with phpmyadmin under the ( operations tab ),
you can set, in the table options, autoincrement to the number you want.


RE: What's wrong with mysql reset auto increment in codeigniter? - skunkbad - 05-18-2017

(05-18-2017, 02:31 AM)InsiteFX Wrote: ALTER TABLE tablename AUTO_INCREMENT = 1

There is a very easy way with phpmyadmin under the ( operations tab ),
you can set, in the table options, autoincrement to the number you want.

I think OP does want to do this, but also wants to renumber all of the old records, probably to remove missing ids that were deleted? If it were me, I'd probably just rename the table, recreate the original table, and then batch insert everything. Maybe there is an easier way.


RE: What's wrong with mysql reset auto increment in codeigniter? - Martin7483 - 05-19-2017

1. Select all records and store in array or object. Maybe also save them to a JSON file
2. Truncate the table -> This will reset auto increment
3. (Re)Insert the records from step 1 -> remove existing ID column
4. Your ID's have been rearranged Smile