How to Create a Custom PHP Function to Insert Data into MySQL

When working with PHP and MySQL, you often need a reusable function to simplify database operations. One common operation is inserting data into a database table. In this article, we’ll walk through creating a robust PHP function that makes it easy to insert data while maintaining flexibility and security.

Prerequisites

Before we dive into the function, ensure that:

• You have a MySQL database set up.
• A connection to the database is already established and available globally.

The Goal

We want to create a function, insertData, that:

• Accepts the table name and data as arguments.
• Automatically adds create_datetime and create_user_id columns during insertion.
• Utilizes prepared statements to prevent SQL injection.

Let’s get started!

The Function

Here’s the complete insertData function:

function insertData($tableName, $data) {
    global $connection; // Use the globally established database connection

    // Add default columns
    $data['create_datetime'] = date('Y-m-d H:i:s');
    $data['create_user_id'] = 1; // You can dynamically fetch the user ID as needed

    // Extract columns and values
    $columns = array_keys($data);
    $placeholders = array_fill(0, count($columns), '?');
    $values = array_values($data);

    // Construct the SQL query
    $sql = "INSERT INTO `$tableName` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $placeholders) . ")";

    // Prepare the statement
    $stmt = $connection->prepare($sql);
    if (!$stmt) {
        die("SQL Error: " . $connection->error);
    }

    // Bind parameters dynamically
    $types = str_repeat('s', count($values)); // Assuming all values are strings. Modify as needed for other data types.
    $stmt->bind_param($types, ...$values);

    // Execute the statement
    if ($stmt->execute()) {
        return $stmt->insert_id; // Return the ID of the inserted row
    } else {
        return false; // Insertion failed
    }
}

How It Works

• Default Columns: The function automatically adds create_datetime (current timestamp) and create_user_id (hardcoded to 1 in this example). These can be customized based on your application’s requirements.
• Dynamic Columns and Placeholders: The function dynamically builds the SQL query using the keys and values from the $data array. This approach ensures flexibility—you can insert data into any table by simply passing the appropriate $data.
• Prepared Statements: The function uses prepared statements to bind parameters securely, reducing the risk of SQL injection.
• Return Value: On success, the function returns the insert_id (useful for tracking or referencing the inserted record). If it fails, it returns false.

Usage Example

Here’s how you can use the insertData function:

// Example: Insert data into the 'users' table
$tableName = 'users';
$data = [
    'name' => 'John Doe',
    'email' => 'john.doe@example.com',
    'status' => 'active'
];

$result = insertData($tableName, $data);

if ($result) {
    echo "Data inserted successfully. Insert ID: $result";
} else {
    echo "Failed to insert data.";
}

Why Use This Function?

• Reusability: You can use this function for multiple tables by simply passing different table names and data arrays.
• Scalability: Automatically handles dynamic columns, making it suitable for various scenarios.
• Security: Protects against SQL injection using prepared statements.

Conclusion

The insertData function is a powerful utility for PHP developers working with MySQL. It reduces repetitive code, improves security, and makes your application more maintainable. By incorporating features like automatic timestamps and user IDs, you can ensure consistency in your database operations.

Feel free to adapt and extend this function to suit your project’s specific needs. Happy coding!