Two advanced MySQL tips today:

1. Never use a subquery inside a WHERE field IN(…) list

2. How-to: Update values in a table from a calculation that uses the table’s own values, in a single query

(Read more…)

How fast do your dynamic PHP/MySQL pages load? Answer: probably not fast enough. A truly optimized website should serve pages instantaneously to its visitors. There should be no lag time, even 200 milliseconds of server-side script execution time should be considered a burden worth removing.

The keyword for performance is “caching.” By taking the load off the processor and database, a website can easily handle hundreds of thousands of visitors. Many frameworks offers some sort of caching method that stores the final rendered HTML. This is easy to implement for anonymous users, but what about logged-in users?

I recently released an open-source Drupal module called Authenticated User Page Caching (Authcache) (and a demo site) for logged-in users. Drupal is a great PHP/MySQL CMS/framework for building community-based websites, though it suffers from a “one-more-query” syndrome–a single page request can have hundreds of SQL queries! The reason why authenticated caching is difficult is because most of the time, when you’re logged into a website, the content changes according to your account (like a “You’re logged in as [username]” link), so it’s pointless to save the final HTML to the server’s cache. Instead the entire page has to be rendered by PHP on each request, requiring precious CPU cycles & database hits. Ajax/JavaScript can be used for customizing the cached HTML, however. Of course, this technique can’t be used with every website–some sites are just too dynamic–but it can work if the content of pages is similar across users.

Caching Flowchart

(Read more…)

Everyone knows backups are important, not just for restoring from a system failure, but also for fixing user/admin/developer mistakes. Here’s an efficient way to automatically backup files on your Linux/CentOS server using a spare Windows or Linux machine. You should already have a basic grasp of the Linux shell prompt for this guide.

(Read more…)

1. Write HTML like it’s 1999 – Email clients and web clients such as Gmail love to strip out CSS within <style> tags. For this reason, use inline CSS. Also, using tables to build your newsletters is a safe idea as well, since the float property isn’t always supported. Outlook 2007 is extremely limited when rendering HTML–no background CSS images, no forms, no custom bullet images.

2. Don’t use PHP’s mail() function – This function opens and closes a connection to your mail server every time, which is extremely inefficient. It is possible to write your own bulk email sender in PHP, however! Use an SMTP class for more control and speed.
3. In the “To” field, always have the user’s email address – The easiest way to have your email eaten by a spam filter is to not use the user’s actual email in the “To” field. Some like to send out more emails by placing multiple email addresses within the BCC header, but this will add spam points to the email.
4. Set up an SPF TXT record for your domain name – This is another anti-spam filter technique to make sure your emails get through. If you look at the source headers of an email, you may see an entry that says Received-SPF: neutral or Received-SPF: fail. It should only take 5 minutes to set this up and make it say “pass.” For example, my domain register allows me to add DNS TXT records, so for the host field I would type “httpremix.com.” and for the address field I would add "v=spf1 a mx -all" (quotes included for the TXT).

   (Read more…)

I love databases because I always feel there is a way for retrieving my data in any shape or form I desire. Trying to find the correct syntax can sometimes be frustrating, however. Here are two tricks that have helped me during those special times:

1. Custom "ORDER BY"
   It's easy to order by ASC or DESC, but what if we want a custom order returned? This is when the FIELD() function comes in handy.
   SQL copy to clipboard
   1. SELECT * FROM table_name ORDER BY FIELD(field_name, 'Small','Medium','Large');

   
   

   SELECT * FROM table_name ORDER BY FIELD(field_name, 'Small','Medium','Large');
   

   The disadvantage is that this will slow down your query. For maximum performance, store the field as an ENUM with the values defined in the correct order.

2. Retrieving values from multiple rows as a single comma-delimited field

   Scenario: We have multiple values spread out across rows a table, but need to retrieve them together in a single field without performing multiple queries. I'm doing this for my archive page--I want to retrieve a list of topic tags for each article, but I want to do it with the same query that selects all my articles. GROUP_CONCAT() is the answer for string concatenation.

   SQL copy to clipboard
   1. SELECT GROUP_CONCAT(wp_terms.slug) AS topic, p.post_date, p.post_title, p.post_count, p.post_name, p.comment_count
   2. FROM wp_posts p
   3. LEFT JOIN wp_term_relationships r ON (r.object_id = p.ID)
   4. LEFT JOIN wp_term_taxonomy t ON (r.term_taxonomy_id = t.term_id)
   5. LEFT JOIN wp_terms ON (wp_terms.term_id = t.term_id)
   6. WHERE t.count> 0 AND t.taxonomy = 'category' AND p.post_status = 'publish'
   7. GROUP BY p.ID
   8. ORDER BY post_date DESC

   
   

   SELECT GROUP_CONCAT(wp_terms.slug) AS topic, p.post_date, p.post_title, p.post_count, p.post_name, p.comment_count
   FROM wp_posts p
   LEFT JOIN wp_term_relationships r ON (r.object_id = p.ID)
   LEFT JOIN wp_term_taxonomy t ON (r.term_taxonomy_id = t.term_id)
   LEFT JOIN wp_terms ON (wp_terms.term_id = t.term_id)
   WHERE t.count> 0 AND t.taxonomy = 'category' AND p.post_status = 'publish'
   GROUP BY p.ID
   ORDER BY post_date DESC
   

   The "topic" column could then return values such as "php,mysql,css".

What if you need to grab a string from text, such as the name of the department below:

Here's a quick way to pick out that string:

$match = preg_match_all("/Department:(.*)/", strip_tags($body), $matches, PREG_SET_ORDER);
$deptName = trim($matches[0][1]);