MySQL Insert Data

PHP: Inserting Data into MySQL

Inserting Data Securely using MySQLi and PDO

Once you have a database and table structure, you can insert data using the SQL INSERT INTO statement within your PHP scripts. It’s critically important to do this securely to prevent SQL injection vulnerabilities.

The recommended approach is to use Prepared Statements with either the MySQLi or PDO extensions.

SQL INSERT INTO Syntax:

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

Key Rules when constructing SQL queries (especially without prepared statements, which is NOT recommended for user input):

String values must be enclosed in single quotes (e.g., 'John Doe').
Numeric values should not be quoted (e.g., 42).
The SQL keyword NULL should not be quoted.
Date/Time values are typically quoted as strings (e.g., '2023-10-27 10:00:00').

However, relying on manual quoting is error-prone and insecure. Prepared statements handle data types and escaping automatically.

Example Scenario: Inserting into MyGuests Table

Assuming the MyGuests table exists (created in a previous step) with columns: id (AUTO_INCREMENT), firstname (VARCHAR), lastname (VARCHAR), email (VARCHAR), reg_date (TIMESTAMP DEFAULT CURRENT_TIMESTAMP).

The following examples demonstrate inserting a new record using MySQLi (Object-Oriented and Procedural) and PDO, focusing on prepared statements.

Example 1: MySQLi (Object-Oriented Style) with Prepared Statement

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "myDB";           // Replace with your DB name

// Data to insert (Ideally from a validated source, e.g., form input)
$firstName = "John";
$lastName = "Doe";
$email = "john.doe@example.com";

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

// Check connection
if ($conn->connect_error) {
    // Log error securely, provide generic message to user
    error_log("Connection failed: " . $conn->connect_error);
    die("Database connection error. Please try again later.");
}

// Set character set (recommended)
$conn->set_charset('utf8mb4');

// Prepare the SQL statement (Prevents SQL Injection)
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    // Handle prepare error
    error_log("Prepare failed: (" . $conn->errno . ") " . $conn->error);
    die("Error preparing database statement.");
}

// Bind parameters ('sss' means three string parameters)
// Binds variables to the placeholders
$stmt->bind_param("sss", $firstName, $lastName, $email);

// Execute the statement
if ($stmt->execute()) {
    $last_id = $conn->insert_id; // Get the ID of the inserted row
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    // Handle execution error
    error_log("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    echo "Error: Could not execute statement.";
}

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

?>

Example 2: MySQLi (Procedural Style) with Prepared Statement

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDB";

// Data to insert
$firstName = "Jane";
$lastName = "Roe";
$email = "jane.roe@example.com";

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

// Check connection
if (!$conn) {
    error_log("Connection failed: " . mysqli_connect_error());
    die("Database connection error.");
}

// Set character set
mysqli_set_charset($conn, 'utf8mb4');

// Prepare the SQL statement
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)";
$stmt = mysqli_prepare($conn, $sql);

if (!$stmt) {
    error_log("Prepare failed: (" . mysqli_errno($conn) . ") " . mysqli_error($conn));
    die("Error preparing database statement.");
}

// Bind parameters
mysqli_stmt_bind_param($stmt, "sss", $firstName, $lastName, $email);

// Execute the statement
if (mysqli_stmt_execute($stmt)) {
    $last_id = mysqli_insert_id($conn);
    echo "New record created successfully (Procedural). Last ID: " . $last_id;
} else {
    error_log("Execute failed: (" . mysqli_stmt_errno($stmt) . ") " . mysqli_stmt_error($stmt));
    echo "Error: Could not execute statement.";
}

// Close statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);

?>

Example 3: PDO with Prepared Statement

<?php
declare(strict_types=1);

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "myDBPDO"; // Assuming a different DB for PDO example

// Data to insert
$firstName = "Peter";
$lastName = "Jones";
$email = "peter.jones@example.com";

try {
    // Create connection using PDO
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);

    // Set the PDO error mode to exception (recommended)
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare the SQL statement
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (:fname, :lname, :email)";
    $stmt = $conn->prepare($sql);

    // Bind parameters using named placeholders
    $stmt->bindParam(':fname', $firstName);
    $stmt->bindParam(':lname', $lastName);
    $stmt->bindParam(':email', $email);

    /* Alternatively, pass an array to execute():
    $stmt->execute([
        ':fname' => $firstName,
        ':lname' => $lastName,
        ':email' => $email
    ]);
    */

    // Execute the statement (if not using execute with array)
    $stmt->execute();

    $last_id = $conn->lastInsertId(); // Get the last inserted ID
    echo "New record created successfully (PDO). Last inserted ID is: " . $last_id;

} catch(PDOException $e) {
    // Handle PDO errors
    error_log("PDO Error: " . $e->getMessage());
    echo "Database error: " . $e->getMessage(); // Show generic message in production
    // die("Database operation failed."); // Or die gracefully
} catch(Exception $e) {
    // Handle other potential errors
    error_log("General Error: " . $e->getMessage());
    echo "An unexpected error occurred.";
}

// Close connection (PDO closes automatically when script ends or $conn is unset)
$conn = null;

?>

Security Best Practice:

Always use prepared statements when inserting data that originates from users or external sources. This is the most reliable way to prevent SQL injection attacks.