PHP MySQL Insert Query

Inserting into MySQL Database Using PHP

The INSERT INTO statement in SQL is used to add new rows to a database table. In PHP, this can be implemented using the following methods:

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

Creating Table Using MySQLi Object-Oriented Procedure

This method uses the object-oriented style of MySQLi to create a table.

Example

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

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

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

// SQL to create table
$sql = "CREATE TABLE Students (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table Students created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Explanation:

1. Establish a connection to the MySQL server.
    
2. Define the SQL query to create a table named Students with fields for ID, name, email, and registration date.
    
3. Execute the query using $conn->query().
    
4. Check the query's success or failure and output the result.

Creating Table Using MySQLi Procedural Procedure

The procedural method uses functional calls rather than object-oriented syntax.

Example

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

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

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

// SQL to create table
$sql = "CREATE TABLE Courses (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    course_code VARCHAR(10) NOT NULL,
    start_date DATE,
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
    echo "Table Courses created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}


mysqli_close($conn);
?>

Explanation:

1. Use mysqli_connect() to establish the database connection.
    
2. Run the CREATE TABLE SQL command with mysqli_query().
    
3. Check if the table was created successfully.
    
4. Close the connection using mysqli_close().

Creating Table Using MySQLi PDO Procedure

PDO (PHP Data Objects) is a more flexible and secure way to interact with databases.

Example:

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

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

    // SQL to create table
    $sql = "CREATE TABLE Employees (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        email VARCHAR(50),
        reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";

    $conn->exec($sql);
    echo "Table Employees created successfully";
} catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

Explanation:

1. Use new PDO() to create a connection object.
    
2. Execute the CREATE TABLE query with $conn->exec().
    
3. Handle errors using a try-catch block.

Inserting Data into a Table

Once tables are created, you can insert data using the following methods.

Inserting Single Row Using MySQLi Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "my_database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO Students (name, email) VALUES ('John Doe', 'john@example.com')";

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

$conn->close();
?>

Inserting Multiple Rows into a Table

<?php
$conn = new mysqli("localhost", "root", "", "my_database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO Courses (course_name, course_code, start_date) VALUES
    ('PHP Basics', 'PHP101', '2025-01-01'),
    ('Java Fundamentals', 'JAVA101', '2025-02-01')";

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

$conn->close();
?>

How to Execute the Insert Query?  

Executing an INSERT query in PHP involves more than just writing the query. You need to ensure the data is inserted securely & dynamically, especially when dealing with user input. Let’s break this down step-by-step with a complete example.  

Step 1: Connect to the Database  

Before executing any query, you need to establish a connection to the database. Let’s see how you can do it:  

<?php
$servername = "localhost"; // Server name
$username = "root"; // Database username
$password = ""; // Database password
$dbname = "test_db"; // Database name

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

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

Step 2: Prepare the Data  

In most cases, the data you insert will come from user input, such as a form. Let’s assume we have a form with fields for `username`, `email`, & `password`. Let’s see how you can collect & sanitize the data:  

<?php
// Collect form data
$username = $_POST['username'];
$email = $_POST['email'];
$password = $_POST['password'];

// Sanitize the data (optional but recommended)
$username = htmlspecialchars($username);
$email = htmlspecialchars($email);
$password = htmlspecialchars($password);
?>

Step 3: Write & Execute the Query  

To execute the INSERT query dynamically, you can use prepared statements. Prepared statements are a secure way to handle user input & prevent SQL injection attacks. Let’s see how you can do it:  

<?php

// Step 1: Connect to the database (already done above)

// Step 2: Prepare the SQL statement

$sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";

// Step 3: Create a prepared statement

$stmt = $conn->prepare($sql);

// Step 4: Bind parameters to the statement

$stmt->bind_param("sss", $username, $email, $password);

// Step 5: Execute the statement

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $stmt->error;
}

// Step 6: Close the statement & connection
$stmt->close();
$conn->close();
?>

In this Code: 

1. Prepared Statements:  

The `?` placeholders in the SQL query are used to represent the values that will be inserted dynamically. The `bind_param()` method binds the variables (`$username`, `$email`, `$password`) to the placeholders. The `"sss"` argument specifies that all three values are strings.  

2. Security:  

Using prepared statements ensures that user input is treated as data, not executable code. This prevents SQL injection attacks.  

3. Execution:  

The `execute()` method runs the query. If successful, it returns `true`; otherwise, it returns `false` & an error message is displayed.  

4. Closing Resources:  

Always close the prepared statement & database connection to free up resources.  

Step 4: Handling Errors  

It’s important to handle errors gracefully. For example, if the email is already in the database (assuming it’s a unique field), you can catch the error & display a user-friendly message:  

<?php
if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    if ($stmt->errno == 1062) { // Error code for duplicate entry
        echo "Error: This email is already registered.";
    } else {
        echo "Error: " . $stmt->error;
    }
}
?>

Frequently Asked Questions

What is the purpose of the INSERT INTO statement? 

The INSERT INTO statement adds new rows to a database table.

Why should I use PDO over MySQLi? 

PDO supports multiple database types and offers advanced security features like prepared statements.

Can I insert multiple rows using a single query? 

Yes, you can insert multiple rows by separating the values with commas in the INSERT INTO query.

Conclusion

In this article, we discussed how to create tables and insert data into a MySQL database using PHP. We looked at how PHP MySQL Insert Query works. We covered MySQLi object-oriented and procedural methods, as well as PDO. With proper examples and explanations, you can now build and manage database-driven applications effectively.