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>
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>
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);
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 | |
|---|---|---|
| 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")
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.
