Category Archives: MySQL

Optimize PHP, Apache & MySQL for performance

Two MySQL tuner scripts

MySQLTuner (Perl) and MySQL Performance Tuning Primer (Shell) are two scripts that can help diagnose MySQL database performance issues, and provide recommendations on how to solve them.  While both scripts output similar information, the diagnosis can be slightly different with each script, so I like to use both.  Below are directions on how to use them, as well as a few screenshots.

# Download MySQLTuner and MySQL Performance Tuning Primer to ~/scripts.
# To avoid having to invoke Perl/SH each time, make both files executable.
chmod +x ~/scripts/mysqltuner.pl
chmod +x ~/scripts/tuning-primer.sh

# After that, give them a go!
~/scripts/mysqltuner.pl
~/scripts/tuning-primer.sh

Continue reading Optimize PHP, Apache & MySQL for performance

Using PHPass password hasher with Zend Framework

In this tutorial, we will learn how to use PHPass, a portable PHP password hashing component, with the “glue”-based Zend Framework.  The popular PHP-based blogging platform WordPress utilizes PHPass to hash user passwords.  Using this library adds an extra level of security over a normal MD5-protected password. Continue reading Using PHPass password hasher with Zend Framework

25+ Must-Have Cheat Sheets for Web Developers

Who wants to go to Google or break out the library of programming books every single time you get stuck on something? Below is a compilation of extremely useful cheat sheets, which can be printed, laminated and placed nearby for easy reference while doing your web development and design. Everyone has moments where a function cannot be remembered, and that is where these cheat sheets can be lifesavers. Bookmark this page for an easy portal to all of the cheat sheets, or an individual page if you find it helpful. Please don’t hesitate to post any cheat sheets we neglected to include, for this is about sharing knowledge.

1. PHP

PHP Cheat Sheet

http://www.addedbytes.com/cheat-sheets/php-cheat-sheet/ Continue reading 25+ Must-Have Cheat Sheets for Web Developers

MySQL database class using Singleton design pattern

(Note: The following is a revised tutorial I wrote earlier this year, transferred from one of my old web sites.)

Using the Singleton design pattern is a simple way to limit the number of class instances to one, helps keep database connections organized, and can also save memory.  The PHP code below is a basic example on how to use the Singleton pattern to create a database class.  This class can be used to manage a database connection throughout PHP script execution.  While this is a basic example, many improvements have been made over the original version.  If this is well received, we will be happy to add additional functionality. Click read more to view the class, and learn about the functionality of each part. Continue reading MySQL database class using Singleton design pattern

The WordPress $wpdb class explained

The WordPress MySQL database class is quite powerful.  This database class can be used for other projects with some very simple modifications, and is actually based on/borrowed from another project called ezSQL by Justin Vincent.

The WordPress database class code is located in <WordPress root>/wp-includes/wp-db.php.  The $wpdb object is created at the very bottom using the following lines of code (comments removed):

if ( ! isset($wpdb) ) {
	$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
}

Below, I listed a few of the main functions in the $wpdb object.  These functions will help make your job easier when creating plugins and extending the WordPress core, or even working on a completely different project.  I did not include functions used for debugging/error reporting, as that could easily be its own lesson.

  • set_prefix($prefix) — used to set table prefix for WordPress tables, can be used to override prefix at any time.
  • prepare($query) — safely prepares an SQL query for execution with sprintf()-like syntax.
  • query($query) — perform a MySQL database query with current connection
  • insert($table, $data, $format) — insert a row into a table via arrays.
  • update($table, $data, $where, $format, $where_format) — update a row in a table via arrays.
  • get_var($query, $x, $y) — retrieve a single variable from the database.
  • get_row($query, $output, $y) — retrieve a single row from the database.
  • get_col($query, $x) — retrieve a single column from the database in array format.
  • get_results($query, $output) — retrieve SQL result set from database… one or more rows.
/**
 * Insert a new option into the $wpdb->options table.
 * @uses $wpdb WordPress Database Object
 */
$wpdb->insert( $wpdb->options, array('option_name', 'new_option_key', 'option_value' => 'New Option Value', 'autoload' => 'yes') );

/**
 * Update an existing option in the $wpdb->options table.
 * @uses $wpdb
 */
$wpdb->update( $wpdb->options, array('option_value' => 'Newer Option Value'), array('option_name' => 'new_option_value') );

/**
 * Get a single post_id from the $wpdb->postmeta database.
 *
 * The $wpdb->prepare() function ensures that the query is
 * safe for use in the database.  The %d refers to an integer
 * and is replaced by $post_ID in a way that makes it query-safe.
 * The %s refers to a string and is also escaped.
 *
 * @uses $wpdb
 */
$post_id = $wpdb->get_var( $wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = 'enclosure' AND meta_value LIKE (%s)", $post_ID, $url . '&') );

/**
 * Delete a record from the $wpdb->options database.
 * @uses $wpdb
 */
$wpdb->query("DELETE FROM $wpdb->options WHERE option_name = '$name'");

Feel free to leave comments and ideas on what could make this tutorial better or clearer.