MySQL Insert Data

Adding Rows to a Table

In simple terms: Once a table exists, you add data to it one row at a time (or several at once) with SQL's INSERT statement — a new customer signs up, an order is placed, a product is added to the catalog. From PHP, you send the INSERT statement over the database connection.

Technically: INSERT INTO table (columns) VALUES (values) adds a row. Columns that are not listed receive their default values (AUTO_INCREMENT IDs, DEFAULT values, CURRENT_TIMESTAMP). With MySQLi, fixed SQL can be sent with query(), but any statement containing external data must use a prepared statement — execute_query() (PHP 8.2+) or prepare() + execute().

The examples use the gocourse_shop database from MySQL Create Table and the db() connection helper from MySQL Connect.

The INSERT Statement

INSERT INTO customers (name, email, city)
VALUES ('Asha', 'asha@example.com', 'Chennai');

id and created_at are not listed: MySQL fills them automatically (AUTO_INCREMENT and DEFAULT CURRENT_TIMESTAMP). Always list the columns explicitly — an INSERT without a column list breaks as soon as the table structure changes.

Inserting Fixed Data With query()

For SQL that contains no external input — such as seed data in a setup script — query() is fine:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$db->query("INSERT INTO customers (name, email, city) VALUES ('Asha', 'asha@example.com', 'Chennai')");

echo "Rows inserted: " . $db->affected_rows . "\n";
echo "New customer ID: " . $db->insert_id . "\n";

Output (on an empty table)

Rows inserted: 1
New customer ID: 1

• query() returns true for successful INSERT statements (and throws an exception on failure).
• affected_rows reports how many rows were inserted.
• insert_id returns the AUTO_INCREMENT value generated for the new row (see MySQL Get Last ID).

Never Build INSERT Statements From Input

Inserting form data by placing variables into the SQL string is the classic beginner mistake:

// DANGEROUS: do not do this
$name = $_POST['name'];
$db->query("INSERT INTO customers (name, email) VALUES ('$name', '$email')");

What Goes Wrong

• A legitimate name such as D'Souza contains a quote, which ends the SQL string early and causes a syntax error.
• A malicious user can submit carefully crafted text that changes the SQL statement itself — SQL injection, one of the most dangerous web vulnerabilities (see SQL Injection Prevention).

The solution is a prepared statement: the SQL contains ? placeholders, and the values are sent separately. The database never interprets the values as SQL.

Inserting With Prepared Statements

execute_query() (PHP 8.2+)

<?php
require __DIR__ . '/src/db.php';

$db = db();

$customers = [
    ['Bala', 'bala@example.com', 'Madurai'],
    ['Charu', "charu@example.com", 'Chennai'],
    ["Dev D'Souza", 'dev@example.com', null],
];

foreach ($customers as [$name, $email, $city]) {
    $db->execute_query(
        'INSERT INTO customers (name, email, city) VALUES (?, ?, ?)',
        [$name, $email, $city]
    );

    echo "Inserted $name with ID {$db->insert_id}\n";
}

Output (continuing after Asha)

Inserted Bala with ID 2
Inserted Charu with ID 3
Inserted Dev D'Souza with ID 4

How It Works

1. The SQL contains three ? placeholders instead of values.
2. execute_query() prepares the statement, sends the values separately, and executes it — in one call.
3. The apostrophe in D'Souza is harmless, because the value is never inserted into the SQL text.
4. A PHP null becomes SQL NULL (allowed here because city is nullable).

prepare() + execute() (All PHP 8 Versions)

<?php
$stmt = $db->prepare('INSERT INTO customers (name, email, city) VALUES (?, ?, ?)');
$stmt->bind_param('sss', $name, $email, $city);

$name = 'Asha';
$email = 'asha@example.com';
$city = 'Chennai';
$stmt->execute();

bind_param() binds variables by reference with a type string (s \= string, i \= integer, d \= double, b \= blob). Since PHP 8.1, values can also be passed directly to execute([$name, $email, $city]). Prepared statements are covered in depth in MySQL Prepared.

Handling Duplicate Entries

The customers.email column has a UNIQUE key. Inserting an existing email throws an exception with error code 1062:

<?php
require __DIR__ . '/src/db.php';

function registerCustomer(mysqli $db, string $name, string $email, ?string $city): string
{
    try {
        $db->execute_query(
            'INSERT INTO customers (name, email, city) VALUES (?, ?, ?)',
            [trim($name), strtolower(trim($email)), $city]
        );

        return "Welcome, $name! Your customer ID is {$db->insert_id}.";
    } catch (mysqli_sql_exception $e) {
        if ($e->getCode() === 1062) {
            return 'This email is already registered.';
        }
        throw $e;
    }
}

echo registerCustomer(db(), 'Elango', 'elango@example.com', 'Trichy') . "\n";
echo registerCustomer(db(), 'Asha K', 'ASHA@example.com', 'Chennai') . "\n";

Output

Welcome, Elango! Your customer ID is 5.
This email is already registered.

Why Rely on the Database?

Checking "does this email exist?" with a SELECT before inserting is not enough: two requests arriving at the same moment can both pass the check. The UNIQUE constraint is the only reliable guard; the code translates its error into a friendly message and re-throws any other error. (The email is normalized to lowercase before inserting; the case-insensitive collation would also treat ASHA@example.com as a duplicate.)

Insert or Update: ON DUPLICATE KEY UPDATE

Sometimes you want "insert this row, or update it if it already exists" — an upsert. For example, receiving new stock for a product that may or may not be in the catalog yet:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$sql = 'INSERT INTO products (sku, name, price, stock, category)
        VALUES (?, ?, ?, ?, ?) AS new
        ON DUPLICATE KEY UPDATE stock = products.stock + new.stock';

foreach ([['PEN-01', 'Gel Pen', 20, 100, 'stationery'], ['PEN-01', 'Gel Pen', 20, 20, 'stationery']] as $row) {
    $db->execute_query($sql, $row);
    echo "affected_rows: {$db->affected_rows}\n";
}

Output (when PEN-01 does not exist yet)

affected_rows: 1
affected_rows: 2

• The first call inserts the product (affected_rows \= 1).
• The second call finds the existing SKU (via the UNIQUE key) and adds 20 to the stock instead (affected_rows \= 2 means "an existing row was updated"; 0 would mean "the row already had these values").
• The AS new alias (MySQL 8.0.19+) refers to the values that would have been inserted. The older VALUES(stock) syntax is deprecated in MySQL 8.

INSERT IGNORE silently skips rows that would cause duplicate-key errors — but it also hides other problems, so prefer explicit handling.

Common Mistakes

Putting Variables Into SQL Strings

Effect: Broken queries for names with quotes, and SQL injection.

Fix: Always use prepared statements for external data.

Omitting the Column List

Effect: Queries break or insert values into the wrong columns when the table changes.

Fix: Always list columns explicitly.

Checking for Duplicates Only in PHP

Effect: Race conditions allow duplicate rows.

Fix: Use UNIQUE constraints and handle error 1062.

Ignoring Failed Inserts

Effect: Data is silently lost (in old code without exception mode).

Fix: Use exception mode (default since PHP 8.1) and handle exceptions.

Best Practices

• Use query() only for fixed SQL without external values.
• Insert user data with execute_query() or prepare()/execute().
• List columns explicitly and rely on defaults for IDs and timestamps.
• Enforce uniqueness in the database and translate error 1062 into a friendly message.
• Normalize values (trim, lowercase emails) before inserting.

Official reference: https\://www.php.net/manual/en/mysqli.query.php