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;
$data['id'] = $numid;
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.
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;