How to Insert Data into a MySQL Database using PHP

Inserting data is how a PHP application writes to a MySQL database — saving a new user, logging an order, recording a comment. Every such write is an SQL INSERT INTO statement that PHP sends over an open database connection. This article walks through the full flow with the mysqli extension, then shows the safer, modern way using prepared statements that you should reach for in real projects.

What you will learn

• How to open a connection and run a basic INSERT
• Why concatenating values into SQL is dangerous, and how prepared statements fix it
• How to read back the auto-generated ID of the row you just inserted
• How to insert many rows efficiently

Prerequisites

Before we start, you should have:

• A web server with PHP (7.4+) and the mysqli extension installed
• A running MySQL (or MariaDB) database
• A database management tool such as phpMyAdmin, or command-line access

These examples assume a users table. You can create one with:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    email      VARCHAR(100)
);

If you are new to creating tables, see Create a MySQL Table.

Step 1: Establish a Connection

The first step is to connect to the database with mysqli_connect(), which takes four arguments: the server host, the username, the password, and the database name. It returns a connection object you reuse for every query.

<?php

$server   = "localhost";
$username = "root";
$password = "password";
$database = "database_name";

$conn = mysqli_connect($server, $username, $password, $database);

// Always check the connection before continuing.
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

?>

mysqli_connect_error() returns a human-readable reason when the connection fails — a wrong password, an unreachable host, or a missing database — so you are not left guessing.

Step 2: Run a Basic INSERT

With the connection ready, you can run an INSERT query using mysqli_query(). It takes the connection and the SQL string, and returns true on success or false on failure.

<?php
$sql = "INSERT INTO users (first_name, last_name, email)
        VALUES ('John', 'Doe', '[email protected]')";

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

This works for hard-coded values, but the moment any part of the query comes from user input, it becomes unsafe. A value like O'Reilly (with an apostrophe) breaks the SQL, and a malicious value can rewrite your query entirely — this is SQL injection. Never build queries by gluing user input into a string.

Step 3: Insert Safely with Prepared Statements

A prepared statement sends the SQL skeleton and the data separately. Placeholders (?) mark where values go, and mysqli_stmt_bind_param() binds each value with its type (s for string, i for integer, d for double, b for blob). MySQL treats bound values strictly as data, so injection is impossible and quoting is handled for you.

<?php
// 1. Prepare the statement with placeholders.
$stmt = mysqli_prepare(
    $conn,
    "INSERT INTO users (first_name, last_name, email) VALUES (?, ?, ?)"
);

// 2. Bind the input values: three strings -> "sss".
$first_name = "Jane";
$last_name  = "Smith";
$email      = "[email protected]";
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

// 3. Execute, then clean up.
if (mysqli_stmt_execute($stmt)) {
    echo "New record created successfully";
} else {
    echo "Error: " . mysqli_stmt_error($stmt);
}

mysqli_stmt_close($stmt);
?>

This is the approach you should use in any real application. For a deeper look, see PHP MySQL Prepared Statements.

Getting the Inserted ID

When a table has an AUTO_INCREMENT primary key, MySQL assigns the new row's id automatically. Read it back with mysqli_insert_id() — useful when you need the ID for a follow-up query or to return it to the user.

<?php
mysqli_query($conn, "INSERT INTO users (first_name, last_name, email)
                     VALUES ('Amy', 'Lee', '[email protected]')");

$new_id = mysqli_insert_id($conn);
echo "Inserted row has ID: " . $new_id;
?>

See Get the Last Inserted ID for the full picture, including how it behaves with prepared statements.

Inserting Multiple Rows

To add several rows at once, list multiple value sets in a single statement. One round trip to the database is far faster than running INSERT in a loop.

<?php
$sql = "INSERT INTO users (first_name, last_name, email) VALUES
        ('John', 'Doe', '[email protected]'),
        ('Jane', 'Smith', '[email protected]'),
        ('Amy', 'Lee', '[email protected]')";

if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . " records inserted";
} else {
    echo "Error: " . mysqli_error($conn);
}
?>

For inserting user-supplied data in bulk, bind values inside a loop on a prepared statement — see Insert Multiple Rows.

Step 4: Close the Connection

When you are done, close the connection with mysqli_close() to free up resources. PHP closes connections automatically at the end of a request, but closing explicitly is good practice in long-running scripts.

<?php

mysqli_close($conn);

?>

Common Pitfalls

• Forgetting to check the result. mysqli_query() returns false on error; always inspect mysqli_error() so a silent failure does not look like success.
• Unescaped apostrophes. Names like O'Brien break a string-built query. Prepared statements remove the problem entirely.
• Mismatched column count. The number of columns must match the number of values, or MySQL rejects the statement.
• Wrong bind type. Binding an integer as "s" usually works, but mismatches (e.g. binding a string to an i column) can silently corrupt data.

Conclusion

You now know how to insert data into MySQL from PHP: open a connection, run an INSERT, and close the connection. For anything beyond hard-coded test values, use prepared statements to stay safe from SQL injection. Next, learn how to read your data back with Select Data.

Practice