Autoincrement Primary Key in MySQL when data received from form

Question

I have a form that sends the data it captures in to a database, i have a primary key attached to my table (form_id) which i want to autoincrement everytime a new form is submitted and consequently added into my database table. Currently it is just adding a 0 for the first form submitted then anymore forms i submit after do not show as it gives me a message saying you can not have two rows with the same id as zero, which is correct so i would like to change this?

Below is my php code that submits the data into the database:

public function action_claimincentive() {
                    $this->template->content = View::factory('crm/uk/claim_incentive_form');
                    $this->template->content->thanks = false;
                    $this->template->content->val = '';
                    $this->template->content->post = '';

                        if ($this->request->post('form')) {
                                    $post = $this->request->post('form');

                                    $stmt = DB::query(Database::INSERT, 'INSERT INTO `claim_incentive_form_data` (`Claimant Name`, `Claimant Postcode`, `Purchase Order No.`, `Claimant Email Address`, `Storename`, `Storetown`, `Date of Sale`, `Date of Delivery`, `Tempur Acknowledgement No.`, `Tempur Product`)
                                                                        VALUES (:claimantname, :claimantpostcode, :orderno, :email, :storename, :storetown, :dateofsale, :dateofdelivery, :acknowledgementno, :tempurproduct)');
                                    $stmt->param(':claimantname', $post['claimantname']);
                                    $stmt->param(':claimantpostcode', $post['claimantpostcode']);
                                    $stmt->param(':orderno', $post['orderno']);
                                    $stmt->param(':email', $post['email']);
                                    $stmt->param(':storename', $post['storename']);
                                    $stmt->param(':storetown', $post['storetown']);
                                    $stmt->param(':dateofsale', $post['dateofsale']);
                                    $stmt->param(':dateofdelivery', $post['dateofdelivery']);
                                    $stmt->param(':acknowledgementno', $post['acknowledgementno']);
                                    $stmt->param(':tempurproduct', $post['tempurproduct']);
                                        try {
                                                $stmt->execute();
                                                $this->template->content->post = $post;
                                                $this->template->content->thanks = true;
                                                } catch (Exception $e) {
                                                    FB::error($e);
                                                }

                    }
                }

score 2 · Accepted Answer · edited May 23 '17 at 12:22

This sounds more like a MySQL issue rather than anything else. Make sure you have your Primary Key setup to auto increment. Try altering the table to add the auto increment feature:

ALTER TABLE [table name] MODIFY COLUMN [column name] [column type] PRIMARY KEY AUTO_INCREMENT;

In the above example. Replace the keys in brackets with their appropriate names. Replace [table name] with the name of your table, [column name] with the name of the column and [column type] with the type of the column (SMALLINT, INT, etc).

For more information, see this answer posted by roman.

For more information on the AUTO_INCREMENT feature, check out the MySQL Development Documentation here.

score 0 · Answer 2 · answered Dec 17 '13 at 14:32

You need to add AUTO_INCREMENT to the primary key column on the table check this http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

you should be able to alter the table with something like this

ALTER TABLE claim_incentive_form_data CHANGE id id INT(10)AUTO_INCREMENT PRIMARY KEY;

Just make sure you change the id column and the datatype if yours are diffrent from that.

score 0 · Answer 3 · answered Dec 17 '13 at 14:32

You have to set the auto_increment setting in the table within mysql.

ALTER TABLE `claim_incentive_form_data` MODIFY COLUMN `your_primary_column` int(4) PRIMARY KEY AUTO_INCREMENT

Refs: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

score 0 · Answer 4 · answered Dec 17 '13 at 14:37

0

you have to set form_id as primary key, auto increment and not null.

answered Dec 17 '13 at 14:37

Anil Meena

903
1
12
28

Autoincrement Primary Key in MySQL when data received from form

4 Answers4