PHP MySQL Insert Data
Adding data to database tables
➕ Inserting Data
The INSERT INTO statement adds new records to database tables. You specify the table name, column names, and values to store. This is how you save user registrations, posts, products, and all dynamic content.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "mywebsite";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert data into users table
$sql = "INSERT INTO users (username, email)
VALUES ('john_doe', '[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Output:
New record created successfully!
Insert Methods
Basic Insert
Add single record
INSERT INTO table VALUESMultiple Insert
Add many records at once
VALUES (...), (...)Prepared Statements
Secure data insertion
$stmt->bind_param()Form Data
Insert from user input
$_POST['field']🔹 Basic Insert Statement
The simplest way to insert data into a table. Specify the columns and their corresponding values in the same order.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_system";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert a new student
$sql = "INSERT INTO students (first_name, last_name, age, grade)
VALUES ('Alice', 'Johnson', 16, '10th')";
if ($conn->query($sql) === TRUE) {
echo "Student added successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Output:
Student added successfully!
🔹 Insert Multiple Records
Save time by inserting multiple rows in a single query. This is much faster than running separate INSERT statements.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "online_store";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert multiple products at once
$sql = "INSERT INTO products (name, price, stock) VALUES
('Laptop', 999.99, 15),
('Mouse', 29.99, 50),
('Keyboard', 79.99, 30),
('Monitor', 299.99, 20)";
if ($conn->query($sql) === TRUE) {
echo "Multiple products added successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Output:
Multiple products added successfully!
🔹 Insert with Variables
Use PHP variables to insert dynamic data. This is essential for storing user input and calculated values.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "blog_website";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Variables with data to insert
$author_name = "Sarah Smith";
$author_email = "[email protected]";
$author_bio = "Tech writer and blogger";
// Insert using variables
$sql = "INSERT INTO authors (username, email, bio)
VALUES ('$author_name', '$author_email', '$author_bio')";
if ($conn->query($sql) === TRUE) {
echo "Author '$author_name' added successfully!";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Output:
Author 'Sarah Smith' added successfully!
🔹 Prepared Statements (Secure Method)
Prepared statements protect against SQL injection attacks by separating SQL code from data. Always use this method for user input to keep your application secure.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "user_management";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// User input (could be from a form)
$user_name = "mike_wilson";
$user_email = "[email protected]";
$user_password = password_hash("secret123", PASSWORD_DEFAULT);
// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
// Bind parameters (s = string)
$stmt->bind_param("sss", $user_name, $user_email, $user_password);
// Execute
if ($stmt->execute()) {
echo "User registered securely!";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
Output:
User registered securely!
🔹 Insert from HTML Form
A complete example showing how to insert data submitted from an HTML form. This is the most common real-world scenario.
🔸 HTML Form (form.html)
<!DOCTYPE html>
<html>
<head>
<title>Add Product</title>
</head>
<body>
<h2>Add New Product</h2>
<form action="insert.php" method="POST">
<label>Product Name:</label>
<input type="text" name="product_name" required><br><br>
<label>Price:</label>
<input type="number" step="0.01" name="price" required><br><br>
<label>Stock:</label>
<input type="number" name="stock" required><br><br>
<button type="submit">Add Product</button>
</form>
</body>
</html>
🔸 PHP Insert Script (insert.php)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "online_store";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Get form data
$product_name = $_POST['product_name'];
$price = $_POST['price'];
$stock = $_POST['stock'];
// Prepare and execute
$stmt = $conn->prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)");
$stmt->bind_param("sdi", $product_name, $price, $stock);
if ($stmt->execute()) {
echo "Product '$product_name' added successfully!";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
Output (after form submission):
Product 'Wireless Headphones' added successfully!
🔹 Insert with Default Values
Some columns have default values set in the table structure. You can skip these columns when inserting data.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "social_media";
$conn = new mysqli($servername, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Insert post (created_at and likes have default values)
$sql = "INSERT INTO posts (user_id, title, content)
VALUES (1, 'My First Post', 'Hello everyone!')";
if ($conn->query($sql) === TRUE) {
echo "Post created! Timestamp and likes set automatically.";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Output:
Post created! Timestamp and likes set automatically.
🔹 Parameter Types for bind_param()
Data Type Codes:
- i - Integer (whole numbers)
- d - Double (decimal numbers)
- s - String (text)
- b - Blob (binary data like images)
Examples:
-
bind_param("s", $name)- One string -
bind_param("si", $name, $age)- String and integer -
bind_param("sdi", $name, $price, $qty)- String, double, integer
🔹 Best Practices
Security & Performance:
- Always use prepared statements for user input
- Validate data before inserting (check format, length)
- Hash passwords using password_hash()
- Use transactions for multiple related inserts
- Check for duplicates before inserting unique values
- Handle errors gracefully with try-catch or if statements