How Can I Reduce Code Repetition in PHP SQL Prepared Statements?

Overcoming Code Repetitions in PHP SQL Prepared Statements

In typical PHP SQL prepared statements, the field names are mentioned multiple times, leading to redundancy. To address this issue, consider the following solutions:

Raw PHP Techniques

• Omit the fields clause in the query and add default values in the values clause for missing fields.
• Use positional placeholders, providing a value for all columns, including null values or default values defined in the table definition.

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

Helper Function Approach

Create a helper function for inserts that accepts the table name and a data array, handling SQL injection concerns:

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

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));
}

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

Object-Oriented Programming

Implement a baby ORM using Object Oriented Programming, defining a prototype class with common methods and creating specific classes for tables with the table name and column list.

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;

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