PHP MySQL: Insert Data into a Table

Welcome to The Coding College! In this tutorial, we’ll learn how to insert data into a MySQL database table using PHP. Whether you’re working with static data or capturing user input through forms, inserting records is a fundamental operation in any web application.

Why Insert Data with PHP?

PHP allows you to interact with your MySQL database programmatically. By inserting data using PHP, you can:

  • Dynamically store user data (e.g., from forms).
  • Populate tables with bulk or automated data.
  • Create scalable and interactive web applications.

Prerequisites

  1. A MySQL database and table should already exist. If not, refer to our guides:
    • PHP Create a MySQL Database
    • PHP Create a MySQL Table
  2. Ensure PHP and MySQL are properly set up and running on your server.

SQL Syntax for Inserting Data

The SQL command to insert data into a table is:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, if you have a Users table:

INSERT INTO Users (username, email, age)
VALUES ('JohnDoe', '[email protected]', 25);

Methods to Insert Data in PHP

There are two common ways to interact with MySQL in PHP:

  1. MySQLi (Procedural and Object-Oriented)
  2. PDO (PHP Data Objects)

Example: Insert Data into MySQL Table

1. Using MySQLi (Procedural)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query to insert data
$sql = "INSERT INTO Users (username, email, age) VALUES ('JohnDoe', '[email protected]', 25)";

if (mysqli_query($conn, $sql)) {
    echo "New record inserted successfully!";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

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

2. Using MySQLi (Object-Oriented)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query to insert data
$sql = "INSERT INTO Users (username, email, age) VALUES ('JaneDoe', '[email protected]', 30)";

if ($conn->query($sql) === TRUE) {
    echo "New record inserted successfully!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

// Close connection
$conn->close();
?>

3. Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    // Create a PDO instance
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query to insert data
    $sql = "INSERT INTO Users (username, email, age) VALUES ('MarkSmith', '[email protected]', 40)";
    $conn->exec($sql);

    echo "New record inserted successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Inserting Data from an HTML Form

To insert user input from a form into a database, follow these steps:

1. HTML Form

<!DOCTYPE html>
<html>
<head>
    <title>Insert Data into MySQL</title>
</head>
<body>
    <form method="POST" action="insert_data.php">
        <label for="username">Username:</label>
        <input type="text" id="username" name="username" required><br>

        <label for="email">Email:</label>
        <input type="email" id="email" name="email" required><br>

        <label for="age">Age:</label>
        <input type="number" id="age" name="age" required><br>

        <button type="submit">Submit</button>
    </form>
</body>
</html>

2. PHP Script to Handle Form Data (insert_data.php)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Get form data
$username = $_POST['username'];
$email = $_POST['email'];
$age = $_POST['age'];

// SQL query to insert data
$sql = "INSERT INTO Users (username, email, age) VALUES ('$username', '$email', $age)";

if (mysqli_query($conn, $sql)) {
    echo "New record inserted successfully!";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

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

⚠️ Security Note: Always sanitize user input to prevent SQL injection. Use prepared statements for secure data insertion.

Using Prepared Statements for Security

Here’s how you can use prepared statements with MySQLi:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO Users (username, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $username, $email, $age);

// Set parameters and execute
$username = "JohnDoe";
$email = "[email protected]";
$age = 25;
$stmt->execute();

echo "New record inserted successfully!";

// Close connection
$stmt->close();
$conn->close();
?>

Common Errors and Solutions

  1. Table Not Found:
    • Ensure the table name in your SQL query matches the actual table name in your database.
  2. Data Type Mismatch:
    • Check that the data types of values being inserted match the column definitions.
  3. SQL Injection:
    • Use prepared statements to prevent malicious SQL code from being executed.
  4. Duplicate Entries:
    • If your table has a UNIQUE constraint (e.g., for email), ensure no duplicate data is inserted.

Conclusion

Inserting data into a MySQL database using PHP is a core functionality for most web applications. Whether you’re working with static values or dynamic user input, PHP makes this process seamless. Remember to always prioritize security by using prepared statements or other sanitization methods.

For more PHP and MySQL tutorials, visit The Coding College. Start building your dynamic, data-driven applications today!

Leave a Comment