I'm a Drupal PHP/MySQL web developer, living and working in Seattle, WA.
My professional interests are optimization, MySQL, front-end usability, efficient coding, and making Drupal do cool things.
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:
FIELD()function comes in handy.
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.
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 used this on my old WordPress archive page--I wanted 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.
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".