PhpHow-ToBeginner · 4 min read

How to Insert Data into MySQL Using PHP: Simple Guide

To insert data into MySQL using PHP, use the mysqli extension to connect to the database, then run an INSERT INTO SQL query with mysqli_query(). Always check the connection and query success to handle errors properly.

📐

Syntax

Here is the basic syntax to insert data into a MySQL table using PHP:

mysqli_connect(): Connects to the MySQL server.
INSERT INTO: SQL command to add new data.
mysqli_query(): Executes the SQL query.
mysqli_close(): Closes the database connection.

php

<?php
$connection = mysqli_connect('localhost', 'username', 'password', 'database');

$sql = "INSERT INTO tablename (column1, column2) VALUES ('value1', 'value2')";

if (mysqli_query($connection, $sql)) {
    echo "Record inserted successfully.";
} else {
    echo "Error: " . mysqli_error($connection);
}

mysqli_close($connection);
?>

💻

Example

This example shows how to insert a new user with a name and email into a users table. It connects to the database, runs the insert query, and prints success or error messages.

php

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'testdb';

// Connect to MySQL
$conn = mysqli_connect($host, $user, $password, $dbname);

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

// Insert query
$sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";

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

// Close connection
mysqli_close($conn);
?>

Output

New record created successfully.

⚠️

Common Pitfalls

Common mistakes when inserting data into MySQL using PHP include:

Not checking if the database connection was successful.
Forgetting to escape or validate input data, which can cause SQL errors or security issues.
Using wrong table or column names in the SQL query.
Not closing the database connection.

Always handle errors and validate inputs to avoid these problems.

php

<?php
// Wrong way: no error checking and unsafe input
$conn = mysqli_connect('localhost', 'root', '', 'testdb');
$name = "O'Reilly"; // This will break the query if not escaped
$sql = "INSERT INTO users (name) VALUES ('$name')";
mysqli_query($conn, $sql); // No error check

// Right way: escape input and check errors
$conn = mysqli_connect('localhost', 'root', '', 'testdb');
$name = mysqli_real_escape_string($conn, "O'Reilly");
$sql = "INSERT INTO users (name) VALUES ('$name')";
if (!mysqli_query($conn, $sql)) {
    echo "Error: " . mysqli_error($conn);
} else {
    echo "Record inserted safely.";
}
mysqli_close($conn);
?>

📊

Quick Reference

Tips for inserting data into MySQL using PHP:

Always connect to the database before running queries.
Use mysqli_real_escape_string() to prevent SQL injection.
Check the return value of mysqli_query() to confirm success.
Close the connection with mysqli_close() when done.

✅

Key Takeaways

Use mysqli_connect() to connect to your MySQL database before inserting data.

Run INSERT INTO SQL queries with mysqli_query() and always check for errors.

Escape user input with mysqli_real_escape_string() to avoid SQL injection.

Close your database connection with mysqli_close() after operations.

Validate and sanitize all data before inserting to keep your database safe.