Mysql Insert Query
Mysql Insert query is used to create a record in a 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 an array of data into the table.
- Looping through data in iteration and call database insert query for each iteration to create a record for each set of data
- Step 1 is expensive as it requires multiple database insert queries to create N number of records. Therefore we can combine values into one string and write the following SINGLE Insert query to create N records:
<code class="language-html" data-lang="php">
INSERT INTO table_name (column1,column2)
VALUES
(value11, value12 ),
(value21, value22),
...
(valueN1, valueN2);
</code>
How to solve Duplicate entry errors for key that occurred during creating a new record?
A duplicate entry error is a key violation error that occurs if we try to Insert a 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 that 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 a unique key applied to the 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 a record by using following following query
INSERT INTO tbl_employee (name, email) VALUES ("Demo", "ddd@test.com")
Let's find a solution in such a case to avoid Duplicate key issue
Solution 1: Use ON DUPLICATE KEY UPDATE
Do update the column if it matches the 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 creates new records and does not match with duplication
Solution 2: Use INSERT Ignore INTO tableName
Therefore if any duplicate records match with newly created values it will not be inserted to table and the total affected rows will be 0
INSERT Ignore INTO `tbl_employee` (name, email) VALUES ("Demo", "demo@quizcure.com")
Output :0 rows inserted.
Brain Exercise
