POPULAR
Instant Online HTML Formatter
NEW TOOL
Try our Interactive Python Online Playground!
FEATURED
HTML, CSS & JS Live Code Playground
POPULAR
Try Our Online Code Editor and Compiler
POPULAR
Instant Online HTML Formatter
NEW TOOL
Try our Interactive Python Online Playground!
FEATURED
HTML, CSS & JS Live Code Playground
POPULAR
Try Our Online Code Editor and Compiler
MySQL Insert Data
After creating a database and tables in MySQL, the next essential step is to insert data. Inserting data allows your PHP application to store actual information that can be retrieved, updated, or deleted later.
This tutorial covers different methods of inserting data into MySQL tables using SQL commands, PHP MySQLi, and PDO, along with best practices and tips for handling errors.
What is Data Insertion?
Data insertion is the process of adding records (rows) into a table. Each row must conform to the structure of the table, meaning it must match the data types and constraints defined for each column.
For example, consider a students table with columns: id, name, age, and email.
Inserting a new student record would involve providing a value for each of these fields (except for id if it’s AUTO_INCREMENT).
Inserting Data Using SQL
The basic SQL command for inserting data is:
INSERT INTO students (name, age, email) VALUES ('Neha', 16, 'neha@example.com');
Explanation:
-
INSERT INTO studentsspecifies the table. -
(name, age, email)defines the columns you want to insert data into. -
VALUES ('Neha', 16, 'neha@example.com')provides the corresponding data for each column.
Inserting Multiple Rows
You can insert multiple records in a single query:
INSERT INTO students (name, age, email) VALUES
('Anika', 17, 'anika@example.com'),
('Riya', 16, 'riya@example.com'),
('Sanya', 18, 'sanya@example.com');
This approach is more efficient than inserting each row separately.
Inserting Data Using PHP (MySQLi)
Procedural Style
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";
$conn = mysqli_connect($servername, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO students (name, age, email) VALUES ('Neha', 16, 'neha@example.com')";
if (mysqli_query($conn, $sql)) {
echo "Record inserted successfully!";
} else {
echo "Error inserting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Object-Oriented Style
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO students (name, age, email) VALUES ('Anika', 17, 'anika@example.com')";
if ($conn->query($sql) === TRUE) {
echo "Record inserted successfully!";
} else {
echo "Error inserting record: " . $conn->error;
}
$conn->close();
?>
Inserting Data Using PDO
PDO provides a more secure and flexible way to insert data:
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO students (name, age, email) VALUES ('Riya', 16, 'riya@example.com')";
$conn->exec($sql);
echo "Record inserted successfully!";
} catch(PDOException $e) {
echo "Error inserting record: " . $e->getMessage();
}
$conn = null;
?>
Using Prepared Statements
Prepared statements protect against SQL injection by separating SQL code from user data. This is especially important for applications that accept input from users.
Example Using MySQLi (OOP)
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
$stmt = $conn->prepare("INSERT INTO students (name, age, email) VALUES (?, ?, ?)");
$stmt->bind_param("sis", $name, $age, $email);
$name = "Sanya";
$age = 18;
$email = "sanya@example.com";
$stmt->execute();
echo "Record inserted successfully!";
$stmt->close();
$conn->close();
?>
Explanation
-
?are placeholders for values. -
bind_param("sis", ...)binds PHP variables to the placeholders (s= string,i= integer). -
This method prevents malicious input from breaking the query.
Example Using PDO Prepared Statement
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO students (name, age, email) VALUES (:name, :age, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':email', $email);
$name = "Neha Sharma";
$age = 17;
$email = "neha.sharma@example.com";
$stmt->execute();
echo "Record inserted successfully!";
} catch(PDOException $e) {
echo "Error inserting record: " . $e->getMessage();
}
$conn = null;
?>
Prepared statements are highly recommended for any real-world application.
Best Practices for Inserting Data
-
Always use prepared statements for user input.
-
Validate and sanitize input before insertion.
-
Insert multiple rows in a single query if possible for efficiency.
-
Handle errors gracefully to avoid exposing sensitive information.
-
Close connections after insertion to free resources.
Common Errors
| Error | Cause | Solution |
|---|---|---|
| Unknown column | Column name mismatch | Check table structure |
| Cannot add null value | Column is NOT NULL but value missing | Provide a value for mandatory fields |
| Duplicate entry | Primary key conflict | Use AUTO_INCREMENT or ensure unique values |
| Syntax error | Typo in SQL | Check commas, brackets, quotes |
Summary of the Tutorial
-
Inserting data is a crucial step in making your database functional.
-
Use SQL commands for manual inserts or PHP scripts for dynamic insertion.
-
Prepared statements improve security and prevent SQL injection.
-
Always validate data and handle errors to maintain application integrity.
Inserting data correctly lays the foundation for retrieving, updating, and deleting records, allowing your PHP applications to fully interact with MySQL.
⚡ Want to test or run code snippets from this lesson?
Use CodePractice Universal Try-It Editor to run HTML, CSS, JS, Python, C, C++, Java & MySQL live in your browser!
Launch Free Live Code EditorPractice Questions
-
Write a SQL command to insert a record into the
studentstable with values:name = 'Neha',age = 16,email = 'neha@example.com'. -
Write a SQL command to insert three students in a single query:
Anika, 17,Riya, 16, andSanya, 18. -
Write a PHP script using MySQLi procedural style to insert a student named
Anikawith age 17 and emailanika@example.com. -
Write the same insertion using MySQLi object-oriented style.
-
Write a PHP script using PDO to insert a record into the
studentstable with proper error handling. -
Write a PHP prepared statement using MySQLi to insert a student with variables
$name,$age,$email. -
Write a PHP prepared statement using PDO to insert a record using named parameters.
-
Explain why prepared statements are preferred over direct SQL insertion in PHP.
-
Write a PHP script to insert a student record but handle the case where the email already exists as a unique value.
-
Modify a PHP script to insert multiple rows into a table efficiently in a single query.
