PDO Baby Steps

by on February 22, 2012 in
PDO Baby Steps Difficulty Level: Intermediate
 

If you’re like me and just getting into PHP, you’ll undoubtedly ask for help with your mysql_fetch_array and mysql_connect functions. And if you’re like me, you’ll have a whole lot of angry PHP guys telling you to learn MySQLi functions – OR BETTER YET – vvvunderous PDO!

I’m a PHP newb, so I’ll talk plainly about them. If you’re decent, feel free to leave a comment with a better suggestion.
PDO seems to me like an ORM (although I’m sure purists will say it’s nothing like an ORM). Anyway, it’s something that abstracts functions so your code will work with various databases. Also, PDO is object oriented. That means you’ll see stuff like:

$sth->setFetchMode(PDO::FETCH_ASSOC);

…so be a little prepared.
Alright. So here are the snippets I’ve found that actually do stuff:

Connect to a Database

// DB Settings
$host = 'localhost';
$dbname = 'my_database';
$user = 'root';
$pass = '';
 
// Establish connection
$dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

Select a Table From Your Database:

// Select table with query
$sth = $dbh->query("
    SELECT first_names FROM my_table
");

Fetch Fields from your Table:

// Set fetching mode
$sth->setFetchMode(PDO::FETCH_ASSOC);
 
// Assign $row as your key to access Table fields
<?php foreach ($sth as $row) : ?>
    <p>
        Hello, <?php echo $row['firstname']; ?>
    </p>
<?php endforeach; ?>

And for all of you who think “PDO looks like a lot of extra work”, download the Sakila database from MySQL.com and upload it to PHPMyAdmin (upload schema.sql then data.sql in that order); then try out this barebones page on your localhost. In about 8 lines of PHP (4 of which could be cut out – DB setting vars), we made a connection, selected a specific column from a table, and displayed that data.

Thanks for reading. Hope this helps some of you (us), and if you have any more little snippets like this, I’d love to hear them and start a big collection here.

Further Reading:

http://phpro.org/tutorials/Introduction-to-PHP-PDO.html
http://php.net/manual/en/book.pdo.php
http://kest66.wordpress.com/2009/08/12/using-pdo/
http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

Update

Josh explains…

“PDO seems to me like an ORM”
An ORM framework would convert database records into PHP objects that can be manipulated and extended in your code.
For example $users->findByGender(‘m’) would build and execute a query automatically, then return an array of User records. You can the define functions that are specific to the record. E.g. a function User::replacePassword($old, $new) would validate the old password is correct then run the new password through SHA with a seed so it is hashed.
The ORM mapper usually knows about table relationships too, so you can reference other models through properties that are auto-populated for free $user->profile->comments[3]->body etc.

 
If you liked this article, you might also like:
  • Pingback: PDO Baby Steps: Prepared Statements | Pressed Web | Web-design and development from the Huntington, WV, Ashland, KY, and Ironton, OH tristate area

  • Garv

    Hey Cory,

    This has really help me.

    Thanks for posting it.

    Regards

  • Bonaca

    Very useful for me. Thanks a lot.

  • Dane Gardow

    Very informative and helpful. Thanks

  • http://twitter.com/linuxslut linuxslut.net

    Thank you. very helpful resource.

  • Jon

    On this line:

    why do you open PHP back up with <?php if you were already in it?
    I assume your DB Settings, connection, query, and fetch_mode are all in PHP to begin with?

  • Jon

    Nevermind the last post, I tried typing some PHP and it filtered it out.
    My original message was, just before your foreach statement, you add a PHP open tag. It seems you were already in PHP before you got to the foreach statement.

  • CommanderWaffles

    A great help, thanks :)