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

Avoiding Code Repetition in PHP SQL Prepared Statements

In most PHP SQL prepared statement examples, the field names are repeated multiple times. This repetition can be verbose and error-prone if changes need to be made. Here are some solutions to minimize redundancy:

Raw PHP

By omitting the fields clause in the query and using positional placeholders, you can reduce the verbosity. Ensure that all table columns have values, even if they are null or default values.

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

Helper Function

Creating a helper function for inserts can further simplify the code. This function should be wary of SQL injection through field names.

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

Baby Object-Relational Mapper (ORM)

This approach uses object-oriented programming to automate the creation of inserts. A base class contains common methods, and specific tables are defined as child classes.

public function create($data): int
{
    $fields = $this->makeFieldList($data);
    $placeholders = str_repeat('?', count($data) - 1) . '?';

    $sql = "INSERT INTO `$this->table` ($fields) VALUES ($placeholders)";
    $this->sql($sql, array_values($data));

    return $this->db->lastInsertId();
}

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

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

$userGateway = new UserGateway($pdo);

$id = $userGateway->create($data);
echo "Create: $id", PHP_EOL;

These solutions eliminate redundancy by using less code and avoiding duplicate field names.

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