← Back to PHP Course | Chapter 11: Database | Lesson 12 of 21

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:

  1. Basic INSERT Statement
  2. Inserting Data Safely with Prepared Statements
  3. Checking Whether an Insert Succeeded
  4. Inserting Data with Special Characters
  5. Inserting a Row and Using Its Data Immediately
Syntax
php 
$sql = "INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')";

if (!mysqli_query($conn, $sql)) {
    echo "Error: " . mysqli_error($conn);
}

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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]')");
// Print "Row inserted" to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, email TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)")`
$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();
// Print "Inserted safely from user input" to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
// Declare `$result`, set to `$db->exec("INSERT INTO users (name) VALUES ('Alice')")`
$result = $db->exec("INSERT INTO users (name) VALUES ('Alice')");
// Print `$result ? "Insert succeeded" : "Insert failed: " . $db->lastErrorMsg()` to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (name) VALUES (:name)")`
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
$stmt->bindValue(':name', "O'Brien", SQLITE3_TEXT);
$stmt->execute();
// Print "Apostrophe handled safely by the prepared statement" to the output
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
// Declare `$name`, set to "Alice"
$name = "Alice";
// Declare `$email`, set to "[email protected]"
$email = "[email protected]";
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT, email TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (name, email) VALUES (:name, :email)")`
$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();
// Print "Saved $name ($email)! Confirmation sent." to the output
echo "Saved $name ($email)! Confirmation sent.";
?>
Related Topics

PHP MySQL Insert Multiple · PHP MySQL Select Data · PHP MySQL Delete Data · PHP MySQL Update Data

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. 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.
  2. 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.
  3. 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

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.