PHP MySQL Insert Data – Step-by-Step Guide with Best Practices

Inserting data into a MySQL database using PHP is an essential operation for dynamic web applications. This guide will show you how to securely insert data using both mysqli and PDO, with best practices for security and efficiency.

Connect to MySQL Database

Before inserting data, establish a database connection.

Using mysqli

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Using PDO

<?php
$dsn = "mysql:host=localhost;dbname=mydatabase";
$username = "root";
$password = "";

try {
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Insert Data into MySQL Table

CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Using PDO with Prepared Statements (Recommended)

<?php
$sql = "INSERT INTO users (name, email, password) VALUES ('John Doe', 'john@example.com', 'mypassword')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

⚠️ Warning: The above method is insecure and vulnerable to SQL injection. Always use prepared statements.

Insert Data Securely with Prepared Statements

Using mysqli with Prepared Statements (Recommended)

<?php
$name = "John Doe";
$email = "john@example.com";
$password = password_hash("mypassword", PASSWORD_BCRYPT); // Secure password hashing

$stmt = $conn->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $password);

if ($stmt->execute()) {
    echo "New record inserted successfully";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Using PDO with Prepared Statements (Recommended)

<?php
$name = "John Doe";
$email = "john@example.com";
$password = password_hash("mypassword", PASSWORD_BCRYPT); // Secure password hashing

try {
    $sql = "INSERT INTO users (name, email, password) VALUES (:name, :email, :password)";
    $stmt = $conn->prepare($sql);
    
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':password', $password);
    
    $stmt->execute();
    
    echo "New record inserted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Verify Data Insertion

To check if the data was inserted successfully, run this SQL query in phpMyAdmin or MySQL CLI:

SELECT * FROM users;

Best Practices for Inserting Data into MySQL Using PHP

✅ Use Prepared Statements – Prevent SQL injection attacks.
✅ Hash Passwords – Use password_hash() for storing passwords securely.
✅ Validate User Input – Always sanitize and validate input data before inserting.
✅ Use PDO for Flexibility – PDO supports multiple database types and has better error handling.
✅ Close Connections – Always close the database connection after executing queries.