PHP Basics
Introduction to PHP
PHP Installation
PHP Syntax
PHP Comments
PHP Variable
PHP Data Types
PHP Operators
PHP Constants
PHP String
PHP Math
PHP Controls
PHP Condition if
PHP Switch
PHP While Loop
PHP For Loop
PHP Break
PHP Continue
PHP Function
PHP Arrays
PHP Forms
PHP Form Handling
PHP Form Data
PHP Form Validation
PHP Complete Form
PHP Advanced
PHP Date and Time
PHP Include
PHP File Handling
PHP File Open and Read
PHP File Create/Write
PHP File Uploading
PHP Cookies
PHP Sessions
PHP Filters
PHP Callback Functions
PHP Json
PHP Exceptions
PHP Classes
PHP OOPs
PHP Classes & Objects
PHP Constructor
PHP Destructor
PHP Access Modifiers
PHP Inheritance
PHP Class Constants
PHP Abstract Classes
PHP Interfaces
PHP Traits
PHP Static Methods
PHP Static Properties
PHP Namespaces
PHP lterables
PHP XML
PHP XML Parsers
PHP SimpleXML Parsers
PHP SimpleXML Get
PHP XML Expat
PHP XML DOM
MySQL Database
MySQL Database
MySQL Connect
MySQL Create DB
MySQL Create Table
MySQL Insert Data
MySQL Get Last ID
MySQL Insert Multiple
MySQL Prepared
MySQL Select Data
MySQL Where
MySQL Order By
MySQL Delete Data
MySQL Update Data
MySQL Limit Data
Financial Calculator

Financial Calculator

Take control of your finances with our powerful Financial Calculator app—calculate loans, SIP, PPF, NPS and more all in one place!

Play Store
Financial Calculator
Financial Calculator

Calculate loans, SIP, PPF, NPS & more!

Financial Calculator
Financial Calculator

Calculate loans, SIP, PPF, NPS & more!

MySQL Insert Multiple

PHP

MySQL Insert Multiple Records

INSERT Multiple query की मदद से आप एक ही SQL statement में कई records insert कर सकते हैं। यह तरीका fast, optimized और professional applications में बहुत उपयोगी है। बार-बार single insert query चलाने की बजाय, एक ही बार में multiple rows insert करना performance को बेहतर बनाता है।

  • एक ही SQL query से एक से ज़्यादा rows insert होती हैं
  • Performance single-row inserts की तुलना में कई गुना बेहतर
  • Bulk data import, CSV uploads, logs saving में बहुत उपयोगी
  • MySQLi और PDO दोनों में समान तरीके से उपयोग किया जा सकता है

Basic Syntax (Multiple Insert)

SQL: Multiple INSERT Syntax
INSERT INTO students (name, class, city) VALUES
('Aman', 10, 'Delhi'),
('Riya', 9, 'Mumbai'),
('Kunal', 11, 'Kolkata')

Important Notes

  • Data ज्यादा हो तो batch size 100–500 rows रखें (बहुत बड़ी query slow हो सकती है)।
  • Bulk insert करते समय हमेशा transactions इस्तेमाल करना अच्छा है।
  • यदि values user-input हों तो prepared statements का उपयोग करें (SQL Injection से बचाव)।

MySQLi Procedural – Multiple Insert

यह example एक ही query में तीन records insert करता है:

उदाहरण: MySQLi Procedural (Multiple INSERT)
 <?php
  $conn = mysqli_connect("localhost", "root", "", "school");
  $sql = "INSERT INTO students (name, class, city) VALUES
         ('Aman', 10, 'Delhi'),
         ('Riya', 9, 'Mumbai'),
         ('Kunal', 11, 'Kolkata')";

  if (mysqli_query($conn, $sql)) {
    echo "3 Records Inserted Successfully";
  } else {
    echo "Insert Error: " . mysqli_error($conn);
  }
  mysqli_close($conn);
 ?>

Using Loop to Build Multiple Insert Query

जब dynamic data list (form array, CSV, API response) insert करना हो:

Dynamic Query Build (MySQLi)
  $data = [["Sumit", 12, "Pune"], ["Arjun", 9, "Delhi"], ["Meena", 10, "Jaipur"]];
  $values = [];
  foreach ($data as $row) {
    $values[] = "('" . $row[0] . "', " . $row[1] . ", '" . $row[2] . "')";
  }
  $sql = "INSERT INTO students (name, class, city) VALUES " . implode(",", $values);
  mysqli_query($conn, $sql);

Warning: जब data बहुत बड़ा हो (1000+ rows)

  • One large query slow हो सकती है
  • इसे छोटे batches में divide करें (e.g., 100 rows per insert)
  • Bulk insert के लिए LOAD DATA INFILE fastest तरीका है

PDO Multiple Insert Example

PDO में भी एक बार में multiple rows insert की जा सकती हैं:

उदाहरण: PDO (Multiple INSERT)
<?php
  $pdo = new PDO("mysql:host=localhost;dbname=school", "root", "");
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = " INSERT INTO students (name, class, city) VALUES
  ('Aman', 10, 'Delhi'), ('Riya', 9, 'Mumbai'), ('Kunal', 11, 'Kolkata') ";
 
  $pdo->exec($sql);
  echo "Multiple Records Inserted";
 ?>

PDO Prepared Statement – Recommended for Safety

यदि values user-input हों, तो prepared statement सबसे सुरक्षित तरीका है:

PDO (Prepared Multiple Insert – Loop)
 <?php
  $pdo = new PDO("mysql:host=localhost;dbname=school", "root", "");
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $data = [["Rohit", 12, "Pune"], ["Tina", 8, "Delhi"], ["Mahesh", 9, "Surat"]];
  $stmt = $pdo->prepare("INSERT INTO students (name, class, city) VALUES (?, ?, ?)");
  foreach ($data as $row) {
    $stmt->execute([$row[0], $row[1], $row[2]]);
  }
  echo "Multiple Records Inserted Safely";
 ?>

Transactions for Bulk Insert (Highly Important)

100+ records insert करने हों → transaction use करें:

PDO Transaction For Bulk Insert
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO students (name, class, city) VALUES (?, ?, ?)");
foreach ($data as $row) {
  $stmt->execute($row);
}
$pdo->commit();

Best Practices for Multiple Inserts

  • 1000 से ज्यादा rows हों तो batching करें
  • Always validate data before insert
  • Prepared statements का उपयोग करें (safe & secure)
  • Bulk inserts के लिए transactions fastest होते हैं
  • यदि performance critical हो → LOAD DATA INFILE method use करें

Common Mistakes & Fixes

  • गलती: बहुत लंबी query
    Fix: batch size विभाजित करें (100–200 rows)
  • गलती: special characters के कारण query break
    Fix: prepared statements उपयोग करें
  • गलती: values में commas या quotes — SQL error
    Fix: PDO execute() array सुरक्षित values handle करता है

Conclusion

Multiple Insert MySQL applications में performance improvement का एक महत्वपूर्ण हिस्सा है। चाहे आप MySQLi use करें या PDO — bulk operations को सुरक्षित तरीके से execute करने के लिए prepared statements और transactions का उपयोग करना हमेशा recommended है।

Article By: Brajlal Prasad
Created on: 05 Nov 2025  5  Views
 Print Article
Report Error

If you want to report an error, or any suggestion please send us an email to [email protected]

Financial Calculator

Financial Calculator

Take control of your finances with our powerful Financial Calculator app—calculate loans, SIP, PPF, NPS and more all in one place!

Play Store