How to INSERT data in MySQL from PHP

If you are developing an Application in PHP and using MySQL as Database provider, you will need to store data using an INSERT operation. Follow these steps:

• Connect to the MySQL server.
• Build the SQL query with the appropriate `INSERT` statement and data.
• Execute the query.4. Handle any potential errors.

Examples:

Basic INSERT with static values

// Step 1: Connect to MySQL server
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// Step 2: Build the SQL query
$sql = "INSERT INTO users (name, email, age) VALUES ('John Doe', This email address is being protected from spambots. You need JavaScript enabled to view it.', 30)";

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

// Step 4: Close the connection
$conn->close();

INSERT with dynamic values using prepared statements (recommended to prevent SQL injection)

// Step 1: Connect to MySQL server
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// Step 2: Build the SQL query with prepared statement
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

// Step 3: Prepare the statement
$stmt = $conn->prepare($sql);

// Step 4: Bind parameters and execute the statement
$name = "Jane Doe";
$email = "This email address is being protected from spambots. You need JavaScript enabled to view it.";
$age = 25;

$stmt->bind_param("ssi", $name, $email, $age); // 'ssi' specifies the data types of the variables

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

// Step 5: Close the statement and connection
$stmt->close();
$conn->close();

We use prepared statements to prevent SQL injection by binding the parameters securely to the query. This is considered a safer approach, especially when handling user input data. It also helps with query performance when inserting multiple rows with the same statement by reusing the prepared statement.

INSERT data when having JOINED Tables

When dealing with complex operations involving multiple joined tables, it's essential to use transactions to ensure data integrity and consistency. Transactions allow you to execute a sequence of database operations as a single unit of work. If any part of the transaction fails, the entire transaction is rolled back, and no changes are committed to the database.

Let's assume we have the following database schema:

customers table (id, name, email)
orders table (id, customer_id, order_date)
order_items table (id, order_id, product_name, quantity)

// Step 1: Connect to MySQL server
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// Step 2: Start the transaction
$conn->begin_transaction();

try {
    // Step 3: Insert into customers table
    $customerName = "John Doe";
    $customerEmail = "This email address is being protected from spambots. You need JavaScript enabled to view it.";
    $customerSql = "INSERT INTO customers (name, email) VALUES ('$customerName', '$customerEmail')";
    $conn->query($customerSql);
    $customerId = $conn->insert_id; // Get the auto-incremented ID of the last inserted customer

    // Step 4: Insert into orders table
    $orderDate = date('Y-m-d');
    $orderSql = "INSERT INTO orders (customer_id, order_date) VALUES ('$customerId', '$orderDate')";
    $conn->query($orderSql);
    $orderId = $conn->insert_id; // Get the auto-incremented ID of the last inserted order

    // Step 5: Insert into order_items table (assuming we have an array of items to insert)
    $items = [
        ['Laptop', 2],
        ['Mouse', 1],
        ['Keyboard', 1]
    ];

    foreach ($items as $item) {
        $productName = $item[0];
        $quantity = $item[1];
        $itemSql = "INSERT INTO order_items (order_id, product_name, quantity) VALUES ('$orderId', '$productName', '$quantity')";
        $conn->query($itemSql);
    }

    // Step 6: Commit the transaction
    $conn->commit();

    echo "Transaction completed successfully!";
} catch (Exception $e) {
    // Step 7: If any error occurs, roll back the transaction
    $conn->rollback();

    echo "Transaction failed: " . $e->getMessage();
}

// Step 8: Close the connection
$conn->close();

In this example, we are inserting data into three joined tables: customers, orders, and order_items. The code is enclosed within a transaction block ($conn->begin_transaction();) which ensures that all the database operations are treated as a single unit of work. If any part of the transaction fails (due to an exception or error), the rollback() function is called, which undoes all the changes made during the transaction. If everything executes successfully, the commit() function is called, and the changes are permanently saved in the database.

Remember that using prepared statements is recommended for real-world applications to prevent SQL injection, especially when dealing with user input or dynamic data. The above example uses simple queries for brevity, but in practice, you should utilize prepared statements for better security.