How Can I Improve My MySQL Insert/Update Function Using PDO Prepared Statements?

Insert/Update Helper Function Using PDO

PDO Prepared Statements for Inserting Data

The provided helper function, dbSet, generates a SET statement for use with traditional MySQL drivers but lacks support for PDO prepared statements. To address this, a modified version of the function can be utilized, leveraging the benefits of PDO.

Modified Helper Function

The updated dbSet function now takes an array of field names and a reference to an array of corresponding values. It constructs a SET statement with placeholders for each field and adds the values to the values array.

function dbSet($fields, &$values) {
    $set = '';
    $values = array();

    foreach ($fields as $field) {
        if (isset($_POST[$field])) {
            $set .= "`$field` = ?,";
            $values[] = $_POST[$field];
        }
    }

    return rtrim($set, ',');
}

Example Usage:

$fields = explode(" ","name surname lastname address zip fax phone date");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-"$_POST['d'];

$query  = "UPDATE $table SET ".dbSet($fields, $values).", stamp=NOW() WHERE>

Advantages of PDO Prepared Statements:

• Security: Prevents SQL injection by using placeholders.
• Efficiency: Prepares the query once, reduces database overhead.
• Consistency: Ensures consistent data formatting and type conversion.

Alternative Solution: Object-Relational Mapping (ORM)

Consider using an ORM such as Doctrine ORM, which simplifies data population and eliminates the need for manual query building.

$table = new Table();
$table->fromArray($_POST);
$table->save();

This approach automates the process of mapping properties to database fields based on defined entity classes.

The above is the detailed content of How Can I Improve My MySQL Insert/Update Function Using PDO Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!