Tag Archives: database

Getting Your Feet Wet with PDO and Migrating Old MySQL Code

Aug 5, 2011 by Matt | Posted in Coding, Featured One Comment

You may have heard that the old MySQL extension for PHP is going to eventually be deprecated in favor of the newer (and potentially more secure) MySQLi and PDO extensions. You’re going to need to update your old code sooner or later, so why not make it sooner?

I’m going to recommend PDO over MySQLi simply because it’s available on more systems, and it’s syntax may be a little bit easier to learn for newbies. PDO has been bundled with the main PHP distribution since PHP 5.1, and has been in PECL even longer, while MySQLi has only been included since 5.3. Whichever you use is up to personal preference and project requirements of course, but I will be sticking with PDO for the duration of this tutorial.

Continue reading →

WordPress’s WPDB Class

Oct 19, 2009 by Matt | Posted in WordPress No Comments

WordPress has a class, $wpdb, that contains several useful functions for manipulating the database. (The $wpdb object is a global variable that WordPress automatically creates.) This is used throughout the core, and by plugin and theme developers to easily run custom MySQL operations. It even has functions for building and executing prepared statements.

You can find a nice introduction and examples at WP Engineer. Here’s a sample of how you would go about changing the title of a post with a certain ID:

$wpdb->update( $wpdb->posts, array( 'post_title' => $new_post_title ),
 array( 'ID' => $the_post_id ) );

That’s just scratching the surface, but it gives a good idea of how the database functions work.

For even more in-depth reading, there’s a good Codex page on the subject as well.

Learning MySQL [Book Review]

Oct 8, 2008 by Matt | Posted in Coding No Comments

Almost anyone who’s played around with PHP before has run into the subject of databases. MySQL databases are the most common method of storing massive amounts of data to later be sorted through and retrieved for display via script. WordPress stores all of it’s posts and settings in MySQL tables, forum scripts are powered by databases, sites like Amazon, YouTube, and of course IMDB make extensive use of databases. If you think about it, most modern websites are just pretty user interfaces for databases.

As common as they may be, databases sure are mysterious critters. It look me awhile to grasp the concept of them at first, and even longer for me to pick-up the skills required to make use of them. In addition to knowing a scripting language like PHP or Perl, and how to submit a query to the DB server, you also need to know the SQL language.

I just finished a great book on MySQL. Learning MySQL by Seyed M.M. Tahaghoghi and Hugh Williams is a comprehensive and well-explained book that teaches you from the ground up about MySQL databases and how to work with them. It introduces the concept of a database, walks you through installing the MySQL server software (if you’re not already running it), explains querying, then moves on to the real meat of the book: Structured Query Language, or SQL. It covers basic SELECTS and INSERTs, JOINS, nested queries, table and column types, and really everything you need to know to get started.

Following chapters cover topics like using PHP or Perl to interact with databases (as opposed to using a MySQL prompt) and securing web applications. There are also sections on planning database structures optimally, optimizing performance.

The book is written in a manner that should make it easy to follow, it’s full of code examples to try out, and overall is enough to give you a general to intermediate knowledge of MySQL. It also works as a handy reference.