MySQL INSERT INTO Query: The INSERT INTO statement in MySQL is used to insert new records into a database table. It is one of the most commonly used SQL commands, essential for data entry and storage operations.
Table of Contents
- What is INSERT INTO?
- Basic Syntax of INSERT INTO
- Inserting Data into a MySQL Table (Examples)
- Handling String, Numeric, and Date Data Types
- Inserting Data from Another Table
- PHP Code Example for MySQL INSERT INTO
- Summary
1. What is INSERT INTO in MySQL?
MySQL INSERT INTO Query: The INSERT INTO statement allows you to insert one or more rows into a database table. This command is widely used in database operations for adding new records.
2. MySQL INSERT INTO Syntax
The basic syntax for inserting data into a table is:
sql
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
Breakdown of the Syntax:
- table_name: The name of the table where data will be inserted.
- (column1, column2, column3, …): The specific columns where values will be inserted.
- VALUES (value1, value2, value3, …): The values to be inserted.
Note:
- String values should be enclosed in single quotes (‘ ‘).
- Numeric values should not be enclosed in quotes.
- Date values should be in the format ‘YYYY-MM-DD’.
3. MySQL INSERT INTO Query Examples
MySQL INSERT INTO Query: Let’s consider a members table with the following columns:
- full_names
- date_of_birth
- gender
- physical_address
- contact_number
Example 1: Insert a Single Row
sql
INSERT INTO members (full_names, gender, physical_address, contact_number)
VALUES (‘Leonard Hofstadter’, ‘Male’, ‘Woodcrest’, ‘0845738767’);
Key Notes:
- The contact number is enclosed in quotes to preserve leading zeros.
Example 2: Insert Data in a Different Column Order
You can insert values in a different column order as long as they match the correct data type.
sql
INSERT INTO members (contact_number, gender, full_names, physical_address)
VALUES (‘0938867763’, ‘Male’, ‘Rajesh Koothrappali’, ‘Fairview’);
4. Handling Different Data Types in INSERT INTO
String Values
MySQL INSERT INTO Query: String values should be enclosed in single quotes (‘ ‘).
sql
INSERT INTO members (full_names, gender)
VALUES (‘Sheldon Cooper’, ‘Male’);
Numeric Values
Numeric values should not be enclosed in quotes.
sql
INSERT INTO payments (amount_paid)
VALUES (2500);
Date Values
Dates should be enclosed in single quotes and follow the YYYY-MM-DD format.
sql
INSERT INTO members (full_names, date_of_birth)
VALUES (‘Leslie Winkle’, ‘1984-02-14’);
5. Inserting Data from Another Table
MySQL INSERT INTO Query: You can copy data from one table into another using INSERT INTO … SELECT.
Example: Copying Data from categories to categories_archive
sql
INSERT INTO categories_archive (category_id, category_name, remarks)
SELECT category_id, category_name, remarks FROM categories;
Requirements:
- The destination table (categories_archive) must have the same structure as the source table (categories).
6. PHP Example: Insert Data into MySQL Table
MySQL INSERT INTO Query:: In PHP, you can use mysqli_query() to execute an INSERT INTO query.
Example: PHP Code to Insert Data
php
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create Connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check Connection
if (!$conn) {
die(“Connection failed: ” . mysqli_connect_error());
}
// Insert Query
$sql = “INSERT INTO members (full_names, gender, contact_number)
VALUES (‘Howard Wolowitz’, ‘Male’, ‘0987786553’)”;
if (mysqli_query($conn, $sql)) {
echo “New record created successfully”;
} else {
echo “Error: ” . $sql . “<br>” . mysqli_error($conn);
}
// Close Connection
mysqli_close($conn);
?>
PHP Key Takeaways:
- mysqli_connect() establishes a database connection.
- mysqli_query() executes the SQL query.
- Always check for connection errors.
7. Summary
- The INSERT INTO statement adds new records to a table.
- Enclose string and date values in single quotes.
- Numeric values should not be enclosed in quotes.
- The INSERT INTO … SELECT query allows inserting data from one table into another.
- PHP can execute INSERT INTO queries using mysqli_query().
This guide provides a complete overview of using INSERT INTO in MySQL, helping you efficiently add new data to your database.
Bonus Tip:
For bulk data entry, consider using batch insert for better performance:
sql
INSERT INTO members (full_names, gender, contact_number)
VALUES
(‘John Doe’, ‘Male’, ‘1234567890’),
(‘Jane Doe’, ‘Female’, ‘9876543210’),
(‘Alice Smith’, ‘Female’, ‘5556677889’);
This reduces the number of queries executed, improving efficiency.
