Category: Wordpress

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

Top 10 best practices for front-end web developers

Explain which div you’re closing

Most of the time when I’m viewing a website source, I see, at the very bottom of the page, an almost endless list of closing </div> tags. In fact, many beginners think they just have to use divs instead of tables to produce quality code. Divs are cleaners than tables, but without proper code organization, it can be as (or even sometimes more) messy as table based code.

Using indentation is a good start. But a tip that can definitely make you save lot of time is to comment every div tag you’re closing, as shown in the example below:

<div id="header">
  <div id="sub" class="first left">
    ...
  </div><!-- #sub.first.left -->
</div><!-- #header -->

Use a CSS reset

Unless you’re a beginner or if you were on vacation on a desert island for the last 6 years, you might already know how useful a CSS reset it. Because by default, browsers don’t apply the same default styling to HTML elements, a CSS reset will ensure that all element have no particular style so you can define your own without the risk of many cross-browser rendering issues.

html, body, div, span, applet, object, iframe,
h1, h2, h3, h4, h5, h6, p, blockquote, pre,
a, abbr, acronym, address, big, cite, code,
del, dfn, em, font, img, ins, kbd, q, s, samp,
small, strike, strong, sub, sup, tt, var,
b, u, i, center,
dl, dt, dd, ol, ul, li,
fieldset, form, label, legend,
table, caption, tbody, tfoot, thead, tr, th, td {
	margin: 0;
	padding: 0;
	border: 0;
	outline: 0;
	font-size: 100%;
	vertical-align: baseline;
	background: transparent;
}
body {
	line-height: 1;
}
ol, ul {
	list-style: none;
}
blockquote, q {
	quotes: none;
}
blockquote:before, blockquote:after,
q:before, q:after {
	content: '';
	content: none;
}

/* remember to define focus styles! */
:focus {
	outline: 0;
}

/* remember to highlight inserts somehow! */
ins {
	text-decoration: none;
}
del {
	text-decoration: line-through;
}

/* tables still need 'cellspacing="0"' in the markup */
table {
	border-collapse: collapse;
	border-spacing: 0;
}

Source: http://meyerweb.com/eric/tools/css/reset/index.html

Don’t use @import

CSS files can be included using the @import directive. This can be useful when, for example, you want to include a stylesheet into another. Another common practice is to include CSS file in html documents using the following:

<style type="text/css>
  @import url('a.css');
  @import url('b.css');
</style>

While it works, the @import directive is much slower than the other way to include stylesheets into a html document:

<link rel='stylesheet' type='text/css' href='a.css'>
<link rel='stylesheet' type='text/css' href='proxy.css'>

It will not make a difference on low traffic websites, but if you have the chance to own a popular website, don’t waste your visitor’s time using @import.
Source: http://www.stevesouders.com/blog/2009/04/09/dont-use-import/

“Smush” your images

Being a developer, I always found that optimizing my images for the web wasn’t easy. I tried the good old “Save for web” Photoshop command, but most of the time, I ended up with images that were either too big or without a sufficient quality.
As a result, I had the bad habit of using unoptimized images on my websites. This isn’t a problem when you don’t have to care about your site’s bandwidth, but after my recent switch on my vps.net virtual private server, I had to be careful with image sizes.

At this time, I found a very cool tool named Smush It: You enter your unoptimized image url, and Smush It will create a perfectly optimized image for you. You can save up to 70% of the file size, while keeping the original quality. As an example, all the images from my list of online code editors have been “smushed”.

Don’t mix CSS with HTML

As a markup language, the right use of HTML is to organize documents by defining a header, a footer, lists, blockquotes, etc. Some time ago, front-end web developers often used now deprecated HTML attributes to style a particular element.
Nowadays, the style attribute allows developers to insert CSS directly into a html document. This is very useful for testing or when you’re in a hurry. But the style attribute is bad practice, that goes completely against the CSS philosophy.

The following example illustrates how dirty and hard to read a simple line of code can become, with the style attribute:

<a href="http://www.catswhocode.com" style="background:#069;padding:3px;font-weight:bold;color:#fff;">Cats Who Code</a>

Don’t mix Javascript with HTML

Just like mixing your html code with css is bad practice, you shouldn’t use any Javascript in your html documents. The following bad practice illustrates an onclick event:

<a id="cwc" href="http://www.catswhocode.com" onclick="alert('I love this site!');">Cats Who Code</a>

The same result can be achieved using unobstructed Javascript. In this example, I’m using the popular jQuery framework:

$(document).ready(function() {
  $('#cwc').click(function() {
    alert('I love this website');
  });
});

This may seems a bit harder at first, especially for beginners; but it is definitely not, and it will keep your html document clean.

Use conditional comments

You know it, IE sucks, and some clients suck even more by requiring you to create webpages which are compatible with this obsolete browser. To target specific versions of IE, you can use the well known IE hacks, as shown below:

height: 200px; /* normal browsers */
_height: 300px; /* IE6 */
.height: 250px; /* IE7 */
*height: 350px; /* All IEs */

Those hacks are extremely useful sometimes, but they are not the best way to target a specific version of IE, and it will cause your CSS validation to fail.

Instead, you should use the conditional comment shown below to target IE6.

<link href="style.css" rel="stylesheet" type="text/css" />

<!--[if lte IE 6]>
  <link href="ie.css" rel="stylesheet" type="text/css" />
<![endif]-->

Place Javascript file at the bottom

A popular practice of the late 90’s/early 2000’s was to place Javascript files within the <head> and </head> tags. The problem is that your javascript files will be loaded first, and consequently your content will be loaded after.

By placing Javascript files at the bottom of your documents, you’ll ensure that JS files will be loaded only when the content has been properly displayed.

    ...
    <script type='text/javascript' src='jquery.js?ver=1.3.2'></script>
  </body>
</html>

Use HTML semantically

HTML is not a programming language. It is a markup language, used to create structured documents by denoting structural semantics for text such as headings, paragraphs, lists, and more.
If you started to create websites in the good old 90’s or in the beginning of the century, you know how dirty the markup was at the time. But happilly, it has evolved.
Among other things, it is important to use html element semantically. As an example, a navigation menu should always be an unordered list:

<ul>
  <li><a href="#">Home</a></li>
  <li><a href="#">About</a></li>
  <li><a href="#">Contact</a></li>
  <li><a href="#">Blog</a></li>
</ul>

Test WHILE you build to avoid cross-browser issues

One of the biggest mistake I ever made when developing html, CSS, and javascript, was not to test my pages on multiple browser while I was writing them. Instead, I used to write all my code and just view in Firefox to see how it was rendered.
In theory, this should be good. But as you know, cross-browser issues are a major problem for front-end developers, especially due to IE. If you test your documents on Firefox/IE/Chrome while your writing it, cross-browser rendering problems will be much easier to fix. I have lost hours not doing it, so I hope this final tip will help you saving your precious time. To test on multiple versions of IE, I use this very handy tool. Happy coding

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

Top 10 best practices for front-end web developers

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