Incorporating Prepared Statements with INSERT INTO

Upon traversing the labyrinthine depths of PHP: Data Objects, a perplexing conundrum arises when attempting to execute MySQL queries using prepared statements, specifically for INSERT INTO operations. Consider the following code snippet:

$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
        VALUES('Bob','Desaunois','18')");

$statement->execute();

Despite adhering to the purported prescribed method, the database stubbornly remains desolate. Let us explore the missing elements that have hindered our progress.

The key to unlocking the potential of prepared statements for INSERT INTO queries lies in parameter binding, a technique that allows for the secure and dynamic integration of values into the SQL statement. This is achieved by incorporating placeholders into the query and subsequently providing the corresponding values as an associative array during execution.

Observe the revised code:

$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
    VALUES (:fname, :sname, :age)');

$statement->execute([
    'fname' => 'Bob',
    'sname' => 'Desaunois',
    'age' => '18',
]);

Note the presence of parameter names, ':fname', ':sname', and ':age', within the query. These serve as placeholders for the actual values, which are then provided as an associative array in the execute() function.

Alternately, you may utilize the '?' syntax as placeholders and pass an array of values without specifying the parameter names:

$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
    VALUES (?, ?, ?)');

$statement->execute(['Bob', 'Desaunois', '18']);

Both approaches offer their respective advantages and drawbacks. Utilizing named parameters enhances readability, while the '?' syntax simplifies the process of binding values. However, ultimately, the choice between the two is a matter of personal preference.