PHP MySQL Insert Data

A table with structure but no rows is not very useful -- inserting data means adding an actual row of values into a table, one record at a time, using an INSERT INTO statement. This is the fundamental building block behind every "save" or "submit" action a web application performs against its database.

In this page:

Basic INSERT Statement
Inserting Data Safely with Prepared Statements
Checking Whether an Insert Succeeded
Inserting Data with Special Characters
Inserting a Row and Using Its Data Immediately

Basic INSERT Statement

INSERT INTO tableName (column1, column2) VALUES (value1, value2) adds a new row to a table, explicitly naming which columns receive which values -- any column not listed gets its default value (or NULL) automatically.

Note: Always name the columns explicitly in an INSERT statement, rather than relying on the table's column order, so the statement keeps working even if the table structure changes later.

Warning: Listing more or fewer values than columns named in the INSERT statement produces an SQL error rather than a partial insert.

Example: Basic INSERT Statement

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)");
$db->exec("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')");
echo "Row inserted";
?>

Inserting Data Safely with Prepared Statements

When the values being inserted come from user input, a prepared statement -- built with mysqli_prepare(), bound placeholders, and mysqli_stmt_execute() -- is essential to prevent SQL injection, since the database treats bound values purely as data, never as part of the SQL command itself.

Note: Use prepared statements for every INSERT that includes any value originating from user input, form submissions, or external data -- never string-concatenate untrusted values into raw SQL.

Warning: Directly embedding an un-escaped, user-supplied string into an INSERT statement is one of the most common and dangerous SQL injection vulnerabilities in web applications.

Example: Inserting Data Safely with Prepared Statements

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, email TEXT)");
$stmt = $db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindValue(':name', $_POST['name'] ?? 'Alice', SQLITE3_TEXT);
$stmt->bindValue(':email', $_POST['email'] ?? '[email protected]', SQLITE3_TEXT);
$stmt->execute();
echo "Inserted safely from user input";
?>

Checking Whether an Insert Succeeded

mysqli_query() returns true if the statement executed successfully and false if it failed -- checking this return value, and reading mysqli_error() when it fails, is essential for reliably knowing whether a row was actually saved.

Note: Never assume an insert succeeded just because the script did not crash -- always check the return value and handle the failure case explicitly.

Warning: A silently-failed insert (unchecked return value) can leave your application believing data was saved when it never actually reached the database.

Example: Checking Whether an Insert Succeeded

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$result = $db->exec("INSERT INTO users (name) VALUES ('Alice')");
echo $result ? "Insert succeeded" : "Insert failed: " . $db->lastErrorMsg();
?>

Inserting Data with Special Characters

Text containing apostrophes, quotes, or other special characters needs careful handling in raw SQL -- an un-escaped apostrophe in a name like "O'Brien" would break a manually built query string, which is yet another reason prepared statements (which handle this automatically) are the safer default.

Note: Let prepared statements handle special-character escaping automatically, rather than manually calling escaping functions like mysqli_real_escape_string() and building raw query strings yourself.

Warning: An un-escaped apostrophe inside a manually concatenated SQL string will break the query's syntax, often producing a confusing syntax-error message rather than an obvious cause.

Example: Inserting Data with Special Characters

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
$stmt->bindValue(':name', "O'Brien", SQLITE3_TEXT);
$stmt->execute();
echo "Apostrophe handled safely by the prepared statement";
?>

Inserting a Row and Using Its Data Immediately

A common pattern is inserting a new row and then immediately doing something with the data that was just saved, like displaying a confirmation message or redirecting to a page showing the new record -- the same $name and $email variables used for the insert are simply reused afterward.

Note: Keep the variables used for an insert in scope afterward if you need to reference the just-inserted values, rather than re-querying the database to get back data you already had.

Warning: Re-fetching data immediately after inserting it (instead of reusing the variables you already have) adds an unnecessary extra database round-trip.

Example: Inserting a Row and Using Its Data Immediately

php

<?php
$name = "Alice";
$email = "[email protected]";
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, email TEXT)");
$stmt = $db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindValue(':name', $name, SQLITE3_TEXT);
$stmt->bindValue(':email', $email, SQLITE3_TEXT);
$stmt->execute();
echo "Saved $name ($email)! Confirmation sent.";
?>

Common Mistakes

Building an INSERT query by directly concatenating raw user input into the SQL string, which opens the door to SQL injection -- prepared statements should be used instead.
Mismatching the number or order of columns and values in an INSERT statement, which either fails outright or silently inserts data into the wrong columns.
Forgetting to check mysqli_query()'s return value, missing a failed insert (like a constraint violation) that never actually made it into the table.

Chapter Summary

INSERT INTO table (col1, col2) VALUES (val1, val2) adds one new row with the given values into the named columns.
Values inserted from raw variables should always go through a prepared statement with bound parameters, never direct string concatenation.
mysqli_query() returns true on a successful insert and false on failure -- always check this before assuming the row was saved.

Browser Support

INSERT INTO is standard SQL, and running it through mysqli_query() or prepared statements works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Create Table Next Lesson → PHP MySQL Get Last ID