• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Insert Data

← Previous Next →

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

  1. Always use prepared statements for user input.

  2. Validate and sanitize input before insertion.

  3. Insert multiple rows in a single query if possible for efficiency.

  4. Handle errors gracefully to avoid exposing sensitive information.

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

Practice Questions

  1. Write a SQL command to insert a record into the students table with values: name = 'Neha', age = 16, email = 'neha@example.com'.

  2. Write a SQL command to insert three students in a single query: Anika, 17, Riya, 16, and Sanya, 18.

  3. Write a PHP script using MySQLi procedural style to insert a student named Anika with age 17 and email anika@example.com.

  4. Write the same insertion using MySQLi object-oriented style.

  5. Write a PHP script using PDO to insert a record into the students table with proper error handling.

  6. Write a PHP prepared statement using MySQLi to insert a student with variables $name, $age, $email.

  7. Write a PHP prepared statement using PDO to insert a record using named parameters.

  8. Explain why prepared statements are preferred over direct SQL insertion in PHP.

  9. Write a PHP script to insert a student record but handle the case where the email already exists as a unique value.

  10. Modify a PHP script to insert multiple rows into a table efficiently in a single query.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top