Category: Tutorials

10 sql tips to speed up your database

Design your database with caution

This first tip may seems obvious, but the fact is that most database problems come from badly-designed table structure.
For example, I have seen people storing information such as client info and payment info in the same database column. For both the database system and developers who will have to work on it, this is not a good thing.
When creating a database, always put information on various tables, use clear naming standards and make use of primary keys.
Source: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

Know what you should optimize

If you want to optimize a specific query, it is extremely useful to be able to get an in-depth look at the result of a query. Using the EXPLAIN statement, you will get lots of useful info on the result produced by a specific query, as shown in the example below:

EXPLAIN SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

Source: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

The fastest query… Is the one you don’t send

Each time you’re sending a query to the database, you’re using a bit of your server resources. This is why, on high traffic sites, the best thing you can do in order to speed up your database is to cache queries.

There’s lots of solutions to implement a query cache on your server. Here are a few:

• AdoDB: AdoDB is a database abstraction library for PHP. It allows you to use the database system of your choice (MySQL, PostGreSQL, Interbase, and way much more) and it is designed for speed. AdoDB provides a simple, yet powerful caching system. And last but not least, AdoDB is licenced under the BSD, which means that you can use freely on your projects. A LGPL licence is also available for commercial projects.
• Memcached: Memcached is a distributed memory caching system which is often used to speed up dynamic database-driven websites by alleviating database load.
• CSQL Cache: CSQL Cache is an open-source data caching infrastructure. Never tested it personally, but it seems to be a great tool.

Don’t select what you don’t need

A very common way to get the desired data is to use the * symbol, which will get all fields from the desired table:

SELECT * FROM wp_posts;

Instead, you should definitely select only the desired fields as shown in the example below. On a very small site with, let’s say, one visitor per minute, that wouldn’t make a difference. But on a site such as Cats Who Code, it saves a lot of work for the database.

SELECT title, excerpt, author FROM wp_posts;

Use LIMIT

It’s very common that you need to get only a specific number of records from your database. For example, a blog which is showing ten entries per page. In that case, you should definitely use the LIMIT parameter, which only selects the desired number of records.
Without LIMIT, if your table has 100,000 different records, you’ll extract them all, which is unnecessary work for your server.

SELECT title, excerpt, author FROM wp_posts LIMIT 10;

Avoid queries in loops

When using SQL along with a programming language such as PHP, it can be tempting to use SQL queries inside a loop. But doing so is like hammering your database with queries.
This example illustrates the whole “queries in loops” problem:

foreach ($display_order as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

Here is what you should do instead:

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

Source: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

Use join instead of subqueries

As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE author_id = a.id)
AS latest_post FROM authors a

Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
    ON (a.id = p.author_id)
GROUP BY a.id

Source: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

Be careful when using wildcards

Wildcards are very useful because they can substitute for one or more characters when searching for data in a database. I’m not saying that you shouldn’t use them, but instead, you should use them with caution and not use the full wildcard when the prefix or postfix wildcard can do the same job.
In fact, doing a full wildcard search on a million records will certainly kill your database.

#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

Source: http://hungred.com/useful-information/ways-optimize-sql-queries/

Use UNION instead of OR

The following example use the OR statement to get the result:

SELECT * FROM a, b WHERE a.p = b.q or a.x = b.y;

The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster:

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y

Source: http://www.bcarter.com/optimsql.htm

Use indexes

Database indexes are similar to those you can find in libraries: They allow the database to find the requested information faster, just like a library index will allow a reader to find what they’re looking for without loosing time.
An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order.

The following query will create an index on the Model column from the Product table. The index is called idxModel:

CREATE INDEX idxModel ON Product (Model);

Source: http://www.sql-tutorial.com/sql-indexes-sql-tutorial/

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

10 sql tips to speed up your database

WordPress: How to easily create a Thematic child theme

What is a Theme framework? And why use it?

Basically, a Theme framework is a WordPress theme that you can extend using functions, styles and child themes.
Child themes are most of the times two files: function.php and style.css. An optional directory containing images can be used as well. Child themes can’t work without a parent theme, which is a synonym for “Theme framework”.

So, why use a Theme framework instead of a “classic” theme? The answer is quite simple: When you need to modify one of WordPress’ functionalities, you don’t edit the core file. Instead, you use a plugin of a hook to modify what you need without editing the core files. That way, you can customize WordPress to fit your needs while at the same time being able to upgrade it without loosing your mods.

Theme frameworks use the same logic: Modify as you need, but don’t edit core file so you’ll be able to upgrade.

In this tutorial, I’m using the Thematic theme framework, a GPL licenced theme brought to you by Ian Stewart. Thematic is in my opinion very powerful and optimized. Therefore, you may be interested in taking a look at other WordPress frameworks as well, such as Hybrid, Headway, Thesis or WP Framework.

Creating the child theme

Right now, you should know what a Theme Framework is and why you should use them. But enough theory for now, let’s get ready to create our own child theme for Thematic.

Download Thematic

Of course, the first thing to do is to download the Thematic Theme Framework. Once finished, unzip the file on your hard drive.

If you want, you can activate Thematic and take a look at your blog. Thematic can be used as a parent theme, or in standalone mode. Without a child theme, Thematic is ready to use and features a really gorgeous typography. There’s no images or even colors, so that you can create your child theme and make Thematic fit your needs, either in its look or functionality.

Create your child theme directory

After you unzipped the Thematic zip file, open the directory. You’ll find a sub directory called thematicsamplechildtheme. Copy it and paste it on the wp-content/themes directory. Rename it with your desired theme name. In this tutorial, I’ll use the name catmatic.

Modify the stylesheet info

Navigate to your new catmatic (or whatever you named it) directory. you’ll find two files: The first is functions.php and the other one is style.css. Open style.css in your favorite text editor. You’ll find the following lines:

/*
Theme Name: A Thematic Child Theme
Theme URI:
Description: Use this theme to start your Thematic Child Theme development.
Author: Ian Stewart
Author URI: http://themeshaper.com/
Template: thematic
Version: 1.0
Tags: Thematic
.
Thematic is © Ian Stewart http://themeshaper.com/
.
*/

What you have to do is simply to name your child theme and give more info about it, as in the following example :

/*
Theme Name: Catmatic
Theme URI: http://www.catswhocode.com
Description: A Thematic child theme.
Author: Jean-Baptiste Jung
Author URI: http://www.catswhocode.com
Template: thematic
Version: 1.0
Tags: Thematic
.
Thematic is © Ian Stewart http://themeshaper.com/
.
*/

Once finished, save the style.css file.

Styling the theme

At this point of the tutorial, we have prepared our child theme but we haven’t started to code yet. So what are we waiting for? The first stage of child theme development is to give the desired look to the Thematic framework, using CSS and images.

Add CSS Styles

Defining CSS styles on your theme child is definitely easy. All you have to do is to add styles to the style.css file from the catmatic directory.

Add images

Adding images to your child theme isn’t hard either. To do so, navigate to wp-content/themes/catmatic and create a new directory named images. (You can name it the way you want, but images is pretty much self-explanatory)

Then, simply put image files in the images directory. If you want to link to those images using CSS, you’ll do the following:

body{
    background: #fff url(images/bg.gif) repeat-x top left;
}

Supercharging Thematic

Well, after having some CSS fun, your Thematic child theme should look as you want it to. Though, what if you want to modify the theme? Should you edit Thematic theme files?
No, you shouldn’t. Of course, it is possible to do it but it is not a good practice at all. If you’re familiar with WordPress development, you probably know about hooks, which allow you to “hook” a custom function to another. In addition to WordPress hooks, Thematic also adds lots of hooks that allow you to do many things to customize your child theme.

Add functions/hooks

Add your custom functions to your child theme is easier than it seems: Open the functions.php file from your child theme directory and paste your functions there.

To help you get started, here is a bunch of ready-to-use functions:

Modify theme link:

function my_footer($thm_footertext) {
	$thm_footertext = 'Powered by WordPress, theThematic Theme framework and the Catmatic child theme.';
	return $thm_footertext;
}
add_filter('thematic_footertext', 'my_footer');

Define where to use excerpt/full posts

$full_content = false;
function childtheme_content($content) {
	if ($full_content) {
		$content= 'full';
	} elseif (is_home() || is_front_page()) {
		$content= 'excerpt';
	} elseif (is_single()) {
		$content = 'full';
	} elseif (is_tag()) {
		$content = 'excerpt';
	} elseif (is_search()) {
		$content = 'excerpt';
	} elseif (is_category()) {
		$content = 'excerpt';
	} elseif (is_author()) {
		$content = 'excerpt';
	} elseif (is_archive()) {
		$content = 'excerpt';
	}
	return $content;
}
add_filter('thematic_content', 'childtheme_content');

Display Thematic menu above header

function remove_thematic_actions() {
    remove_action('thematic_header','thematic_access',9);
}
add_action('wp','remove_thematic_actions');
add_action('thematic_aboveheader','thematic_access');

Add a favicon

function childtheme_favicon() { ?>
	<link rel="shortcut icon" href="<?php echo bloginfo('stylesheet_directory') ?>/images/favicon.png"/>
<?php }
add_action('wp_head', 'childtheme_favicon');

Activate your theme

Right now, you should have a child theme that fit your needs in both the look and functionality. The last thing we have to do is to upload the theme to your wp-content/themes directory and activate it. Make sure that Thematic is available on your wp-content/themes directory as well.

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

WordPress: How to easily create a Thematic child theme

How to easily monitor your web server using PHP

Prerequisites

Maybe I’m stating the obvious, but the PHP script has to be on a different server than the one used for the website you’d like to monitor. If the script is hosted on the same server as your site, then it becomes pretty useless: In fact, if your server is down the script will not be able to run and will not let you know.
The best solution is of course a dedicated server, but a home server can be ok as well. Shared web hosting like those provided by Hostgator or WpWebHost have a low price, but most don’t allow you to set up cron jobs, so be careful if you plan to buy.

The last part of this tutorial will show you how to get sms alerts using Gmail. Please note that depending on your location and cellular phone provider, this part of the tutorial may not work.

1. Creating the monitoring script

The first part of this tutorial is to create the monitor script. Pick up your favorite text editor and create a file named monitor.php. The script is very simple: we only need two functions, one to test if a specific site is available, and the other to alert you by sending an email.

Paste the following in your monitor.php file:

function check($host, $find) {
    $fp = fsockopen($host, 80, $errno, $errstr, 10);
    if (!$fp) {
        echo "$errstr ($errno)\n";
    } else {
       $header = "GET / HTTP/1.1\r\n";
       $header .= "Host: $host\r\n";
       $header .= "Connection: close\r\n\r\n";
       fputs($fp, $header);
       while (!feof($fp)) {
           $str .= fgets($fp, 1024);
       }
       fclose($fp);
       return (strpos($str, $find) !== false);
    }
}

function alert($host) {
    mail('[email protected]', 'Monitoring', $host.' down');
}

$host = 'www.catswhoblog.com';
$find = 'Cats Who Code';
if (!check($host, $find)) alert($host);

The first function we created here, check(), takes two parameters: The first is the server you’d like to check availability (for example, www.catswhocode.com) and the second parameter is to find some text on the webpage. This second parameter is an additional security: In fact, by checking if a specific word is contained on the site homepage, we ensure that the site content hasn’t been modified, for example, after a hacking.

If the server isn’t available or if the text to find hasn’t been found, the alert() function is executed, and will send an email to the account of your choice.

Save the monitor.php file and upload it to your monitoring server.

2. Defining a cron job

At this point of the tutorial, we have a working monitoring script, but we have to type http://mymonitoringserver.com/monitor.php in a web browser to check our website, which makes our script almost useless.
The solution to that problem is to create a cron task to make the server execute monitor.php every hour. Open a SSH console to your monitor server and type the following:

0 * * * * /usr/local/bin/php -q /htdocs/www/monitor.php

If your PHP scripts do not have executable permissions, 644 or -rw-r–r– for example, then as part of the command portion of the cron line, you must specify the php interpreter and pass it the filename of the PHP script (including full path to the script from your home directory) that you want executed.

3. Setting up SMS alerts

Right now, we have a working monitoring PHP script, as well as a cron job that will execute the script every hour. If a problem happens, you’ll receive an email.
Due to the popularity of iPhones, Blackberries and other SmartPhones, a lot of people are able to receive emails everywhere they are. Though, some are still using cell phones with no email capability. In this optional step of the tutorial, let’s see how we can easily receive alerts by sms.

Doing so is quite easy. First you have to use Gmail. As it is a free service, you can create a dedicated account for your monitoring alerts. Once finished, login to your account and click on the “Settings” link located on the top right side of the browser window.
Then, select “Forwarding and POP/IMAP”:

As shown in the previous screenshot, the only thing you have to do is to check “Forward a copy of incomming mail” and fill the field with your phone number @ your provider service.
For example, If your phone provider is AT&T, you’ll have to type [email protected].

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

How to easily monitor your web server using PHP

10+ useful online code editors

Amy Editor

Created in 2007 by Petr Krontorád, Amy Editor is an advanced editor with a look and feel of a Mac. Amy Editor features lots of useful options, such as line numbers, syntax highlighting, snippets for more than 20 languages, collaboration, and more.
Amy can edit, save and export files. It can also manage projects.

» http://www.amyeditor.com/

JSBin

As you can guess, JSBin is an online text editor primarily focused on Javascript. I really love the clean and simple interface. Each code can be tested using a powerful “Preview” feature, and then exported into a text file.
Another good thing to note is that JSBin can import popular Javascript frameworks such as jQuery or Mootools, so you can test your js plugins as well.

» http://jsbin.com/

Bespin

Using HTML5 quite intensively, Bespin is a new project from Mozilla Labs. This online editor is very powerful and has lots of cool options. In order to use Bespin, you have to create an account. Note that Bespin can be downloaded and then embedded in any kind of web project, only by adding two files in your header!

» https://bespin.mozilla.com/

Kodingen

Kodingen is another great online editor, probably one of the most powerful tool on this whole list. It can be used unregistered or you can create an account to use advanced functions as such as SVN repositories, collaborative work, etc.
This editor features templates for most programming languages, syntax highlighting, line numbering and more. A must!

» http://kodingen.com/

EditPad

Unlike the first few editors featured in this post, EditPad is simple and minimal. No syntax highlighting, no project management…Just a plain page to type your text without any distractions. I’m not a big fan, but this “online notepad” can be a life saver on a particularly slow machine.

» http://www.editpad.org/

TypeIt

TypeIt isn’t a code editor and I hesitated to feature it in this post. This handy tool helps you to access special characters such as French accents, like a visual keyboard does. Definitely a site to have in your bookmarks if you’re often working on multi-language sites.

» http://www.typeit.org/

PractiCode

PractiCode is a very basic code editor. It has very limited functions (Handles CSS, HTML and VbScript) but it is perfect to make quick and dirty code.

» http://www.landofcode.com/online-code-editor.php

9ne

9ne (Pronounced Nine) is a nice online text editor, based on the well known GNU Emacs. 9ne provides most of the basic Emacs functionalities and currently supports XML and Javascript syntax highlighting modes.

» http://robrohan.com/projects/9ne/

jsvi

Vi has always been one of my favorite text editors of all times. Why? Because it is powerful, fast, and you’ll find it everywhere: GNU/linux distros, Mac, web servers… Now, you’ll also find Vi online with this implementation called JSVI. Most Vi functions have been implemented into this web-based version.

» http://gpl.internetconnection.net/vi/

HTMLedit

As the name says, HTMLedit is a (very basic) HTML editor that can be used for quick and dirty coding. However, its interest is limited, particularly if compared to most other items from this list.

» http://htmledit.squarefree.com/

DarkCopy

Have you ever felt distracted when working on a buggy piece of code? If yes, there’s no doubt that you will enjoy DarkCopy. This simple online text editor has limited functions but it provides a dark, clutter-free environment so you can concentrate on the most important: getting things done.

» http://darkcopy.com/

SimpleText

SimpleText.ws may have a cool retro Apple look, but it is also a powerful tool that allows you to create, edit, and save plain text files in your web browser. Another good point of SimpleText is that you can host it yourself if you want, using Google Apps Engine. This guide will show you how to do.

» http://www.simpletext.ws/

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

10+ useful online code editors

WordPress : 10+ life saving SQL queries

How to execute SQL queries

For those who don’t know yet, SQL queries have to be executed within the MySQL command line interpreter or a web interface such as the popular PhpMyAdmin. Since we’re going to work on WordPress, you should note that the SQL Executionner plugin provides an easy-to-use interface that allows you to run SQL queries directly on your WordPress blog dashboard.

Although all the queries from this article have been tested, don’t forget that you shouldn’t test any of those on a production blog. Also, make sure that you always have a working database backup.

Manually change your password

It may sound like the thing that only happens to others but forgetting a password can happen to any of us. In case you lost your blog admin password, the only solution is to create a new one directly in your MySQL database.
The following query will do it. Notice that we use the MD5() MySQL function to turn our password into an MD5 hash.

UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;

Source : http://www.wprecipes.com/how-to-manually-reset-your-wordpress-password

Transfer posts from one user to another

Most WordPress newcomers tend to use the good old “admin” account instead of creating an account with their real name. If you made that mistake and created another account later, you can easily transfer your old “admin” posts to your new account with the SQL query below.
You’ll need the user id of both your old and new accounts.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Source : http://www.wprecipes.com/how-to-change-author-attribution-on-all-posts-at-once

Delete post revisions and meta associated to those revisions

Post revisions are very useful, especially in the case of a multi author blog. However, the problem of post revisions is definitely the number of database records it creates. For exemple, if your blog has 100 posts, which has 10 revisions each, you’ll end up with 1000 records in the wp_posts tables, while only 100 of them are necessary.
Executing this query will delete all post revisions as well as all meta info (custom fields, etc) associated to it. The whole process will result in a consequent gain of database space.

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

Source : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Batch delete spam comments

Imagine that you’re coming back from holidays, where you haven’t had any access to the Internet. If you haven’t installed Akismet and depending on your blog popularity, you may end up with 1000, 2000 or even 10,000 comments to moderate.
You can spend a whole day to moderate the lot, or you can use this life-saving query to delete all unapproved comments. And for your next holidays, don’t forget to install Akismet!

DELETE from wp_comments WHERE comment_approved = '0';

Source : http://www.wprecipes.com/mark-asked-how-to-batch-deleting-spam-comments-on-a-wordpress-blog

Find unused tags

Tags are recorded on the wp_terms table. If for some reason a tag has been created but is not used anymore, it stays in the table. This query will let you know which tags are on the wp_terms table without being used anywhere on your blog. You can delete those safely.

SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Source : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Find and replace data

This tip isn’t specific to WordPress and is a must know for anyone who’s working with MySQL databases. The MySQL function replace() lets you specify a field name, a string to find, and a replacement string. Once the query is executed, all occurrences of the string to replace will be replaced by the replacement string.
In case of a WordPress blog, this can be useful to batch replace a typo (For example people who repeatedly call the software WordPress…) or an email address.

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

Source : http://perishablepress.com/press/2007/07/25/mysql-magic-find-and-replace-data/

Get a list of your commentators emails

Have you ever received unsolicited emails from blogs you previously commented? I’m sure you did, just like me. The fact is that getting a list of emails from your commentators is extremely easy using the following query. The DISTINCT parameter will make sure that we’ll only get each email once, even if the user commented more than once.
Please note that this is only a proof of concept: Don’t send your users unwanted emails.

SELECT DISTINCT comment_author_email FROM wp_comments;

Source : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Disable all your plugins at once

When things go wrong, especially on a production site, you have to be quick. Considering the fact that plugins are often the source of problems, disabling all your plugins in a second can prevent lots of problems.
Just run the following query:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Source : http://www.wprecipes.com/how-to-disable-all-your-plugins-in-a-second

Delete all tags

In WordPress, tags are recorded in the wp_terms tables, along with categories and taxonomies. If you wish to remove all tags, you can’t simply empty or delete the wp_terms as you’ll destroy categories at the same time!
If you want to get rid of your tags, run this query. It will remove all tags and relationships between tags and posts, while leaving categories and taxonomies intact.

DELETE a,b,c
FROM
	database.prefix_terms AS a
	LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
	LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
	c.taxonomy = 'post_tag' AND
	c.count = 0
	);

Source : http://wordpress.org/support/topic/311665

List unused post meta

Post meta is created by plugins and custom fields. They are extremely useful, but they can quickly make your database grow in size. The following query will show you all the records in the postmeta table that doesn’t have corresponding records in the post table.

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Source : http://wordpress.org/support/topic/337412

Disable comments on older posts

Everyone who has been in blogging for more than one year will know: Even after some months, your old posts still receive interest from the public and lots of comments, mostly because they are indexed by search engines. This is a good thing of course, but the problem is for people like me who own technical blogs and have to answer lots of questions related to their old (and sometimes obsolete) posts.
The solution to this problem is to automatically close comments on posts which are too old. This SQL query will close comments on all posts published before January 1, 2009.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2009-01-01' AND post_status = 'publish';

Source : http://perishablepress.com/press/2008/02/20/wordpress-discussion-management-enable-or-disable-comments-and-pingbacks-via-sql/

Replace commentator url

Previously in this article, I talked about the very useful replace() MySQL function. Here is a good example of how useful it is : Let’s say you previously own a site and used its url in your comments to generate backlinks to this site.
If you sell the site, you can easily replace the old url by your new site url. Simply run this query and you’ll be done!

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );

Source : http://perishablepress.com/press/2008/07/14/wordpress-link-author-comments-home-page/

Replace commentator email adress

Another good example of the replace() function. This query will replace the email adress provided in the comments field, by a new one.

UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, '[email protected]', '[email protected]' );

Source : http://perishablepress.com/press/2008/05/18/wordpress-tip-update-email-address-in-the-wordpress-database

Delete all comments with a specific url

Lately, I’ve noticied that some clever spammers left some quite relevant comments, but with a link pointing to a viagra site. Unfortunely, when I noticied it the commentator already left lots of comments. The following query will delete all comments with a specific url. The “%” signs means that any url containing the string within the % signs will be deleted.

DELETE from wp_comments WHERE comment_author_url LIKE "%wpbeginner%" ;

Source : http://perishablepress.com/press/2007/07/25/mysql-magic-find-and-replace-data/

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

WordPress : 10+ life saving SQL queries

10+ useful code snippets to develop iPhone friendly websites

Detect iPhones and iPods using Javascript

When developing for the iPhone and the iPod Touch, the first thing we have to do is obviously detect it, so we can apply specific code or styles to it. The following code snippets will detect iPhones and iPods using Javascript, and redirect those users to an iPhone specific page.

if((navigator.userAgent.match(/iPhone/i)) || (navigator.userAgent.match(/iPod/i))) {
    if (document.cookie.indexOf("iphone_redirect=false") == -1) {
        window.location = "http://m.espn.go.com/wireless/?iphone&i=COMR";
    }
}

Source: http://davidwalsh.name/detect-iphone

Detect iPhones and iPods using PHP

Although the previous snippet works great, Javascript can be disabled on the iPhone. For this reason, you may prefer to use PHP in order to detect iPhones and iPods touch.

if(strstr($_SERVER['HTTP_USER_AGENT'],'iPhone') || strstr($_SERVER['HTTP_USER_AGENT'],'iPod')) {
    header('Location: http://yoursite.com/iphone');
    exit();
}

Source: http://davidwalsh.name/detect-iphone

Set iPhone width as the viewport

How many times did you load a website in your iPhone and it just looked like a thumbnail? The reason of this is that the developer forgot to define the viewport (or didn’t know it existed). The width=device-width statement allows you to define the document width as being the same than the width of the iPhone screen. The two other statements are preventing the page from being scaled, which is useful if you’re developing an iPhone-only website. Otherwise, you can remove those statements.
Defining a viewport is easy: Just insert the following meta in the head section of your html document.

<meta name="viewport" content="width=device-width; initial-scale=1.0; maximum-scale=1.0;">

Source: http://www.engageinteractive.co.uk/blog/2008/06/19/tutorial-building-a-website-for-the-iphone/

Insert an iPhone specific icon

When a user adds your page to the home screen, the iPhone will automatically use a screenshot of your website as an icon. But you can provide your own icon, which is definitely better.
Defining a custom iPhone icon is easy: Simply paste the following in the head section of your html document. The image should be 57px by 57px in .png format. You do not need to add the shine or corners, as the iPhone will do that for you automatically.

<rel="apple-touch-icon" href="images/template/engage.png"/>

Source: http://www.engageinteractive.co.uk/blog/2008/06/19/tutorial-building-a-website-for-the-iphone/

Prevent Safari from adjusting text size on rotate

When you rotate the iPhone, Safari adjust text size. If for some reason you’d like to prevent this effect, simply use the following CSS declaration. It has to be inserted in your CSS file.
The -webkit-text-size-adjust is a webkit-only CSS property that allow you to control text adjustment.

html, body, form, fieldset, p, div, h1, h2, h3, h4, h5, h6 {
    -webkit-text-size-adjust:none;
}

Source: http://www.engageinteractive.co.uk/blog/2008/06/19/tutorial-building-a-website-for-the-iphone/

Detect iPhone orientation

Due to the fact that the iPhone allow its users to view a page in both portrait and landscape modes, you may need to be able to detect in which mode the document is being read.
This handy javascript function will detect the current iPhone orientation and will apply a specific CSS class so you can style it your way. Note that in this example, the CSS class is added to the page_wrapper ID. Replace it by the desired ID name (See line 24).

window.onload = function initialLoad() {
    updateOrientation();
}

function updateOrientation(){
    var contentType = "show_";
    switch(window.orientation){
        case 0:
	contentType += "normal";
	break;

	case -90:
	contentType += "right";
	break;

	case 90:
	contentType += "left";
	break;

	case 180:
	contentType += "flipped";
	break;
    }
    document.getElementById("page_wrapper").setAttribute("class", contentType);
}

Source: http://www.engageinteractive.co.uk/blog/2008/06/19/tutorial-building-a-website-for-the-iphone/

Apply CSS styles to iPhones/iPods only

Browser sniffing can be useful, but for many reasons it isn’t the best practice to detect a browser. If you’re looking for a cleaner way to apply CSS styles to the iPhone only, you should use th following. It has to be pasted on your regular CSS file.

@media screen and (max-device-width: 480px){
    /* All iPhone only CSS goes here */
}

Source: http://csswizardry.com/2010/01/iphone-css-tips-for-building-iphone-websites/

Automatically re-size images for iPhones

On recent websites, most images are above 480 pixels wide. Due to the iPhone small size, there’s a strong chance that images will break out of the wrapper area.
Using the following CSS code, you’ll be able to automatically re-size the website images to 100%. As the device max width is 480px, images will never be wider.

@media screen and (max-device-width: 480px){
    img{
        max-width:100%;
        height:auto;
    }
}

Source: http://csswizardry.com/2010/01/iphone-css-tips-for-building-iphone-websites/

Hide toolbar by default

On a small screen such as the iPhone screen, a toolbar is useful but also wastes a lot of space. If you’d like to hide Safari toolbar by default when an iPhone visitor open your website, just implement the following javascript code.

window.addEventListener('load', function() {
    setTimeout(scrollTo, 0, 0, 1);
}, false);

Source: http://articles.sitepoint.com/article/iphone-development-12-tips/2

Make use of special links

Do you remember those “mailto” link that were very popular some years ago? This prefix automatically open the default email client used by the person who clicked on it. The iPhone has introduced two similar prefixes, tel and sms, which allows the person who clicked on it to phone or text automatically.
I’m definitely not a fan of those, but maybe that will be useful to you. The only thing you have to do to implement this, is to paste the following anywhere on your html page.

<a href="tel:12345678900">Call me</a>
<a href="sms:12345678900">Send me a text</a>

Source: http://articles.sitepoint.com/article/iphone-development-12-tips/3

Simulate :hover pseudo class

As no one is using a mouse on the iPhone, the :hover CSS pseudo class isn’t used. Though, using some Javascript you can simulate the :hover pseudo class when the user will have his finger on a link.

var myLinks = document.getElementsByTagName('a');
for(var i = 0; i < myLinks.length; i++){
   myLinks[i].addEventListener('touchstart', function(){this.className = "hover";}, false);
   myLinks[i].addEventListener('touchend', function(){this.className = "";}, false);
}

Once you added the code above to your document, you can start css styling:

a:hover, a.hover {
    /* whatever your hover effect is */
}

Source: http://www.evotech.net/blog/2008/12/hover-pseudoclass-for-the-iphone/

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

10+ useful code snippets to develop iPhone friendly websites

10 interesting projects from Google Code

ZeroClipboard

Do you remember the old days of web development, when IE6 was the king? (ok, it sounds soooo bad now but if you were already building sites in 2002 you know what I’m talking about!) It was extremely easy to force copy to clipboard.
But, for obvious security concerns, Firefox doesn’t allow clipboard access by default. This is a good thing, but for some sites, being able to copy into clipboard is a must.

Using powerful Javascript and a .swf file, ZeroClipboard allow you copy information into the user clipboard. For a live demo, just have a look to my Coupons For Bloggers site.
» Visit ZeroClipboard

yourls

As a blogger, I know how important Twitter is to stay tuned with my readers and share my favorite links with them. But as you know, Twitter allows only 140 characters in messages. In order to create shorter urls, you can use a service like bit.ly or Tinyurl.com, or you can get yourls, and create your own service.

Yourls is built in PHP and is very easy to configure. If you’re using WordPress, you’ll probably be happy to know that yourls has its own WordPress plugin.
» Visit Yourls

Minify

I know I already talked about Minify in a previous article, but I simply cannot resist to spread the word about this very cool piece of code.
Minify is extremely simple to install and will combine, minify, and cache JavaScript and CSS files on demand to speed up page loading.

Installing minify is extremely easy: you just have to upload a directory to your site document root and Minify will start to speed up your blog. Wonderful, isn’t it?
» Visit Minify

Thematic

Being a WordPress fan, I really love the concept of Theme frameworks. For those who doesn’t know what it is, Theme frameworks are WordPress themes which contain lots of functions and styles. You can extend both in looks and functionality by adding a child theme.
For example, my other blog Cats Who Blog is using the Thesis theme framework that I extended using my own styles and functions.

Many commercial frameworks are availables, but Thematic is 100% free. A definitive must download if you’re into WordPress!
» Visit Thematic

Flexlib

As you may guess, Flexlib is an open source Adobe Flex library. It provides lots of components that you can freely use in your Flex or Air projects.
The currently available components include: AdvancedForm, Base64Image, EnhancedButtonSkin, CanvasButton, ConvertibleTreeList, Draggable Slider, Fire, Highlighter, HorizontalAxisDataSelector IconLoader, ImageMap, PromptingTextArea, PromptingTextInput, Scrollable Menu Controls, SuperTabNavigator, Alternative Scrolling Canvases, Horizontal Accordion, TreeGrid, FlowBox, Docking ToolBar, and Flex Scheduling Framework.
» Visit Flexlib

Zen Coding

As a web developer, I often find it frustrating having to type lots of tags and attributes to reach the desired result. HTML tags are necessary of course, but that doesn’t mean it should consume so much typing.
This may be the idea Sergey Chikuyonok before he started to develop Zen Coding. What is Zen Coding? It is a handy set of tools for high-speed HTML and CSS coding. It integrate in your favorite text editor and then provide functions and shortcuts to speed up your development.

As an example, if you type this:

div#content>h1+p

You’ll get the following output:

<div id="content">
<h1></h1>
<p></p>
</div>

If you want to know more about Zen Coding, Smashing Magazine has a nice article about it.
» Visit Zen Coding

Sexybuttons

On the internet, design matters. Some people are good for designing, some, like me, aren’t. Happilly, those who aren’t designers (or who are bad designers!!) should take advantage of projects like this one.
Sexybuttons is a small CSS framework that allow you to instantanely create gorgeous buttons for your blog, websites and web apps. If you like CSS buttons, don’t forget to have a look to my Top 10 CSS buttons tutorial list.
» Visit Sexybuttons

jQuery transmit

Who doesn’t like jQuery? This very handy Javascript framework allows developers to enhance both the design and usability of your website. Thanks to plugins, jQuery can be easily enhanced with the functionalities you need. There’s a bunch of very cool jQuery plugins available from Google code so it was very hard to choose one. However, file upload has always been a major problem in web development and this jQuery plugin will be extremely helpful.

Using jQuery transmit is incredibely easy :

$(document).ready(function() {
    var options = {
        allowedFileTypes: [{
            description: "Images",
            extensions: "*.jpg; *.gif; *.png"
        }]
    };

    $("#transmit").transmit("http://mysite.com/upload/", options);
})

» Visit jQuery Transmit

dompdf : Convert HTML to PDF using PHP

The PDF format is useful for many thing such as invoices, and is largely used in business. dompdf is an advanced HTML to PDF converted which can download and read external stylesheets, inline style tags, and the style attributes of individual HTML elements. It also supports most presentational HTML attributes.
» Visit dompdf

stop-spam

Spam is definitely a big problem for blogs and websites. Although it is still impossible to completely prevent spam, some tools can help you a lot to fight it.
Stop-spam is one of those tools. It is lightweight, compatible with all blogs and forums (WordPress, PhpBB, Movable Type, etc) and easy to install. It automatically blacklists well known domains and IPs used by spammers. Of course, you can edit lists to blacklist/whitelist to add more domains and IPs.
» Visit stop-spam

Any other you’d like to mention? Don’t hesitate to let me know in a comment!

Have you checked out the highly recommended Digging into WordPress book by Chris Coyier and Jeff Starr?

10 interesting projects from Google Code