Mysql Insert Query

Mysql Insert query is used to create record in database table.

<code class="language-html" data-lang="php">
insert INTO table_name (column1,column2) VALUES (value1, value2);
</code>
Description:

Here is the example for PDO insert Query
<code class="language-html" data-lang="php">
<?php
$db_name = "demo";
$db_host = "localhost";
$db_user = "root";
$db_pwd = "databasepwd";

$con = NEW PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pwd);

$stmt = $con->prepare("insert INTO   tbl_employee (name,email) VALUES (?,?)");
$arrayInsert = array("Demo", "demo@quizcure.com");
$stmt->execute($arrayInsert);
</code>

1. How to add Multiple Rows using single mysql insert query

There is two way to insert array of data into table.

  • Looping through data in iteration and call database insert query for each itertion to create record for each set of data
  • Step 1 is expensive as it required to multiple database insert query to create N number of recors. Thefore we can combine values ino one string and write following SINGLE Insert query to create N records:

INSERT INTO table_name (column1,column2)
VALUES
	(value11, value12 ),
	(value21, value22),
	...
	(valueN1, valueN2);

2. How to solve Duplicate entry errors for key that occurred during creating a new record?

Duplicate entry error is a key violation error occurred if we try to Insert new row with the same value that already exists for a unique key column.

Also it happens if we update any unique key column value with another value which already exists in another record for the same column.

Unique key can be either a primary key or any other column of the database table.

Example :
Here is the employee table with unique key applied on name column

CREATE TABLE `tbl_employee` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB


ID name email
1 Demo demo@quizcure.com

Therefore if we try to create record by using following following query

INSERT INTO tbl_employee (name, email) VALUES ("Demo", "ddd@test.com")
It violates the unique key rule of name as raised error. Duplicate entry Demo for key name
Lets find solution in such case to avoid Duplicate key issue

Solution 1: Use ON DUPLICATE KEY UPDATE

Do update column if it match value with unique key column values by using ON DUPLICATE KEY UPDATE

Example:

INSERT INTO `tbl_employee` (name, email) VALUES ("Demo", "demo@quizcure.com") ON DUPLICATE KEY UPDATE email="newEmail@test.com"


Note:
  • Total number of affected rows will be 2 if it updated existing rows when duplicate records matches
  • Total number of affected rows will be 1 if it create new records and does not matches with duplication

Solution 2: Use INSERT Ignore INTO tableName

Therefore if any duplicate records match with newly creating values it will not insert to table and total affected rows will be 0


INSERT Ignore INTO `tbl_employee` (name, email) VALUES ("Demo", "demo@quizcure.com")

Output :0 rows inserted.