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…)

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".