PHP MySQL Insert Data

To insert data into a MySQL database using PHP, you typically follow these steps:

Connect to the MySQL database.
Construct an SQL INSERT statement.
Execute the SQL statement.

Insert Data Into MySQL Using MySQLi and PDO

Here are some syntax rules to follow:

The SQL query must be quoted in PHP
String values inside the SQL query must be quoted
Numeric values must not be quoted
The word NULL must not be quoted

The INSERT INTO statement is used to add new records to a MySQL table:

Syntax

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Using MySQLi:

<?php

// MySQLi Configuration
$servername = "localhost"; // Change this to your MySQL server address
$username = "username"; // Change this to your MySQL username
$password = "password"; // Change this to your MySQL password
$database = "dbname"; // Change this to your MySQL database name

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

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

// Data to be inserted
$name = "John Doe";
$email = "john@example.com";

// SQL statement
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";

// Prepare and bind the statement
$stmt = $conn->prepare($sql);
$stmt->bind_param("ss", $name, $email);

// Execute the statement
if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

// Close connection
$stmt->close();
$conn->close();
?>

You can click on above box to edit the code and run again.

Output

Using PDO:

<?php

// PDO Configuration
$servername = "localhost"; // Change this to your MySQL server address
$username = "username"; // Change this to your MySQL username
$password = "password"; // Change this to your MySQL password
$database = "dbname"; // Change this to your MySQL database name

// Data to be inserted
$name = "John Doe";
$email = "john@example.com";

try {
    // Connect to MySQL using PDO
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    
    // Set PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // SQL statement
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    
    // Prepare the statement
    $stmt = $conn->prepare($sql);
    
    // Bind parameters
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);
    
    // Execute the statement
    $stmt->execute();
    
    echo "New record created successfully";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

You can click on above box to edit the code and run again.