How Can I Reduce Code Repetition When Using PHP SQL Prepared Statements?

Avoiding Code Repetition with PHP SQL Prepared Statements

Introduction

Prepared statements offer a powerful technique to prevent SQL injection attacks and improve query performance. However, traditional methods of creating prepared statements, as exemplified below:

$sql = 'INSERT INTO tasks(task_name, start_date, completed_date) VALUES(:task_name, :start_date, :completed_date)';
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
    ]);

result in a considerable amount of redundancy in specifying field names. This redundancy can be frustrating, especially when making changes or maintaining the code. This article explores three different approaches that can mitigate this repetition and enhance the readability of your prepared statement code.

Raw PHP

For basic inserts, one simple technique involves omitting the fields clause in the query and using positional placeholders:

$data = [$taskName, $startDate, $completedDate];
$sql = 'INSERT INTO tasks VALUES(null, ?, ?, ?)';
$db->prepare($sql)->execute($data);

While straightforward, this approach may not be suitable in all cases.

Helper Function

Another method is to create a helper function specifically for handling inserts. This function can dynamically generate the query based on the provided field names and values:

function prepared_insert($conn, $table, $data) {
    $keys = array_keys($data);
    $fields = implode(",", $keys);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    $conn->prepare($sql)->execute(array_values($data));
}

Using this helper function simplifies the insert process:

prepared_insert($db, 'tasks',[
    'task_name' => $taskName,
    'start_date' => $startDate,
    'completed_date' => $completedDate,
]);

Baby ORM

The most advanced approach involves implementing an Object-Oriented Programming (OOP) solution, commonly referred to as a "baby ORM". This approach defines a base class with common methods for table operations and specific classes for individual tables:

class UserGateway extends BasicTableGateway {
    protected $table = 'gw_users';
    protected $fields = ['email', 'password', 'name', 'birthday'];
}

With this setup, inserts can be performed without explicitly specifying field names:

$data = [
    'email' => '[email protected]',
    'password' => 123,
    'name' => 'Fooster',
];

$userGateway = new UserGateway($pdo);

$id = $userGateway->create($data);

OOP solutions can significantly improve code maintainability, reduce repetition, and enhance the overall development experience.

The above is the detailed content of How Can I Reduce Code Repetition When Using PHP SQL Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!