Yoona Yoona - 3 months ago 15
MySQL Question

Auto primary key insert custom table $wpdb

I need to insert to custom table that has primary key 'id' by number, but I got this problem for example

I have 5605 rows (start with id = 1) in my table,so I have to set current id = 5606 to insert.

1/I set $data['id'] = 5606 by hand to insert it, it works fine. current row with id 5606 is inserted.

but I want it automatically get the right id to insert so I do

2/select * to returns the current number of rows in table, it returns 5604 (always lesser by 1 when I check database has 5605). so I + 2 then do insert.
It ends up insert 3 times like 5606 5607 5608 in my table.
Please help me here is my code

$data = array(
'name' => 'naomi',
'ability' => 'walk',
);

$wpdb->get_results("SELECT * FROM contest");
$numid = $wpdb->num_rows;
$numid +=2;
$data['id'] = $numid;
$wpdb->insert('contest', $data);


The given number is for example, my problem is in that format.

Answer

Just declare column id (or whatever you use as primary key) as AUTO_INCREMENT (in MySQL) or SERIAL (in PostgreSQL) and insert all other columns but your primary key. Example:

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

INSERT INTO persons (LastName,FirstName,Address,City) VALUES (
'Sample','Person','Sample-street','Sample-city'
);

More than! You should not use any manual inserts for primary keys, because it can make you a lot of problems with handling unsuccessfull queries etc.

SECOND PART. To return number of rows in your table just use

SELECT COUNT(id) FROM persons;