QuizCure
Find Available Solution Here!

Mysql Insert Query

Deepak Feb 19, 2024

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>
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>

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 email
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")
It violates the unique key rule of the name as a raised error. Duplicate entry Demo for the key name
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. 

Was this post helpful?

Send Feedback

Connect With QuizCure


Follow Us and Stay tuned with upcoming blog posts and updates.

Contributed By

Deepak

Deepak

QC STAFF
51 Posts
  • PHP
  • JAVA
  • PYTHON
  • MYSQL
  • SEO

You May Like to Read

Scroll up