PHP MySQL Insert Data

After creating a database and a table, the next step is to add data into it. In PHP, you use the INSERT INTO SQL statement to add new records (rows) to a MySQL table.

The SQL Statement

To insert data, you must specify the table name and the columns you want to fill, followed by the values for those columns:

INSERT INTO Users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');

Note: Strings in SQL must be enclosed in single quotes (') or double quotes ("). Numeric values do not need quotes.

1. MySQLi (Object-Oriented)

In this example, we connect to a database named "myDB" and insert a single record into a table named "Users".

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

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

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

    $sql = "INSERT INTO Users (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";

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

    $conn->close();
?>

2. MySQLi (Procedural)

<?php
    $conn = mysqli_connect($servername, $username, $password, $dbname);

    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "INSERT INTO Users (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";

    if (mysqli_query($conn, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    mysqli_close($conn);
?>

3. PDO

<?php
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        $sql = "INSERT INTO Users (firstname, lastname, email)
        VALUES ('John', 'Doe', 'john@example.com')";
        
        $conn->exec($sql);
        echo "New record created successfully";
    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>

Getting the Last Inserted ID

Sometimes you need to know the ID of the record you just inserted (e.g., to link it to another table). PHP makes this easy:

MySQLi (OO): $conn->insert_id;
MySQLi (Proc): mysqli_insert_id($conn);
PDO: $conn->lastInsertId();

Summary

Use the INSERT INTO statement to add data.
Ensure the number of values matches the number of columns.
Always provide a database name in your connection string when inserting data.
Use exec() in PDO for commands that don't return a result set.

Security Warning: The examples above show direct SQL queries. This is fine for learning, but in a real app, you should never put user-provided data directly into a query. Always use **Prepared Statements** to prevent SQL Injection attacks.

« Previous Lesson Next Lesson »