How Can I Reduce Code Repetition in PHP SQL Prepared Statements?-Mysql Tutorial-php.cn

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

Barbara Streisand

Release： 2024-12-26 07:06:13

Original

942 people have browsed it

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

Copy after login

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

Copy after login

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

Copy after login

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'];
}

Copy after login

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

$userGateway = new UserGateway($pdo);

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

Copy after login

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!