PDO Statement does nothing?

Question

$columns = "name, address, city, state, zip";
$values = "'$name', '$address', '$city', '$state', '$zip'";
$query = "insert into customer values ($values)";
$statement = $dbh->prepare($query);
$statement->execute();

So I am completely new to this. Kindergarten examples and baby talk will not be offensive! : )

The form executes with no result and no error.

Answer 1

First of all, you've completely missunderstood statements. :)

Here's how it should be done:

$query = "insert into customer (name, address, city, state, zip) values (?, ?, ?, ?, ?)";
$statement = $dbh->prepare($query);
$statement->execute(array($name, $address, $city, $state, $zip));

As you no doubt can see yourself, you didn't even utilize your $columns variable in your example. That should be part of the query.

Your second mistake was that you tried to interpolate the data variables directly into the string. The entire point of prepared statements is to avoid that. Instead you use placeholders (in this case the ? character). PDO will when substitute the placeholders for the actual variables in the same order the you provide them. This is done by sending them to PDO as a parameter to the execute-statement using an array.

This is far superior to the old method of building the full query as a string, since it eliminates all security risks. The vulnerabilities caused by building a query the way you did are very real, and are probably the most common security hole on the internet. Try searching for "SQL injection" if you want to know more.

The reason you don't get an error is probably because you haven't set the error reporting mechanism. Try adding this after connecting:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Answer 2

Try this. (there are many ways to do this)

$sql = "INSERT INTO customer (name,address,city,state,zip) VALUES (:name,:address,:city:,state,:zip)";

$sth = $dbh->prepare($sql);
$sth->execute(array(
     ':name' => $name, 
     ':address' => $address, 
     ':city' => $city,
     ':state' => $state,
     ':zip'   => $zip
));