For a first try, this is basically OK. Of course, AlexL's concerns are all valid, and when you get to writing production code, you simply have to take all the things he mentioned into account. The golden rule of web applications is, after all, never trust the network.
However, I just though I might add a couple of tips and tricks, and point out a couple of gotcha's to complete Alex' answer:
PDO's methods can all cause errors, but by default,
PDO will not throw exceptions when calling stuff like
PDO::prepare. The constructor takes a fourth argument (an array), which allows you to configure the connection a bit more. Use that to your advantage. Here's how I usually create new instances of
$db = new PDO(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
Now the what's and why's:
127.0.0.1: instead of localhost, I use the localhost IP, to avoid conflicts during name resolution that have been known to slow you down a lot. It's to do with IPv4 vs IPv6 conflicts and MySQL's settings
port: Even if I'm connecting through the default port, I consider it good form to be complete, and provide
PDO with as much information as it needs to connect to the DB. This includes the port, obviously.
UTF8: Always specify the charset, wherever and whenever you can. the DSN string allows you to do just that, seize the opportunity. I repeat this in the attributes:
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'": Again, better to be safe than sorry: we can't be sure we're only sending ASCII chars over.
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION: Force
PDO to throw exceptions on error. This helps debugging a lot, and is indispensable when working with transactions.
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL: If a field is
NULL, there are 3 possible behaviours for
- convert to empty string (which makes it hard to distinguish between a
NULL and an actual empty string)
NULL values to an actual string "NULL". That's just awful in every way, IMHO
- Natural nulls, which I use: no conversion. An empty string is an empty string, a
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ: determine what the return type of
PDOStatement::fetch calls will be if no constant is provided:
$row = $stmt->fetch();//<-- with settings above, $row is an instance of stdClass
$row = $stmt->fetch(PDO::FETCH_ASSOC);//row is assoc array.
This mainly saves you the hasstle of typing too much :)
As far as the
PDO::ERRMODE_EXCEPTION is concerned, I've already mentioned transactions. So how do exceptions tie in with all this? Well, imagine you have to insert a couple of records in various tables, that are relational. If you inserted data into table 1, but then something went wrong (ie: malformed query) you have what is known as orphaned data. That's not what we want.
So we bundle all our
INSERT queries into a transaction, and commit all the queries (ie save the new records) only when we're certain all the queries were successful. If at any point, one query failed, we can still undo or rollback the
$insert1 = $db->prepare('INSERT INTO tbl1 (foo, bar) VALUES (:foo, :bar)');
$insert2 = $db->prepare('INSERT INTO tbl2 (foo, more) VALUES (:foo, :more)');
':foo' => $foo,
':bar' => $bar
$id = $db->lastInsertId();
':foo' => $id,
':more' => $more
$db->commit();//if everything worked, save the inserts
catch (PDOException $e)
$db->rollback();//don't save changes, no table is altered
$errorLog->error($e->getMessage(), $e->getCode());//log error
//exit, or throw new RuntimeException...
You can even use the same insert statement multiple time in a loop, in a single transaction:
$stmt = $db->prepare('INSERT INTO foo (random) VALUES (:rand)');
':rand' => mt_rand(1,10)*(i+1)
$stmt->closeCursor();//close stmt, so we can reuse it
catch (PDOException $e)
Spend some time browsing through the
PDO manual pages, look at the examples and read the docs for all the methods I used here that you don't already know.