PHP mySQL - Insert new record into table with auto-increment on primary key

To insert a new record into a MySQL table that has an auto-increment primary key, you can use the INSERT statement with a list of comma-separated values. For example:

Example of inserting a new record into a MySQL table that has an auto-increment primary key

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

The primary key column does not need to be included in the list of columns, as it will be generated automatically by the database.

Here is an example of how to insert a new record into a table named users that has an auto-increment primary key id and other columns username, password, and email:

Example of inserting a new record into a MySQL table

INSERT INTO users (username, password, email) VALUES ('john', 'mypassword', '[email protected]');

If you want to insert multiple records at the same time, you can use the following syntax:

Syntax of inserting multiple records into a MySQL table

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3), (value1, value2, value3), ...;

For example:

Example of inserting multiple records into a MySQL table

INSERT INTO users (username, password, email) VALUES 
('jane', 'password1', '[email protected]'), 
('jack', 'password2', '[email protected]'), 
('jill', 'password3', '[email protected]');

You can then use a SELECT statement to verify that the records have been inserted into the table.

Example of using SELECT statement to verify the records inserted into a MySQL table

SELECT * FROM users;

Note on PHP usage: The examples above show raw SQL. In PHP, you would execute these statements using mysqli_query() or PDO. For production code, always use prepared statements to prevent SQL injection:

$stmt = $pdo->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
$stmt->execute(['john', 'mypassword', '[email protected]']);