citytree

when you move your wordpess blog into another location, youy need to update fields in mysql

Code block

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://localhost/yoursite','http://www.yoursite.com');
UPDATE wp_options SET option_value = replace(option_value, 'http://localhost/yoursite','http://www.yoursite.com');
UPDATE wp_posts SET guid = replace(guid, 'http://localhost/yoursite/','http://www.yoursite.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://localhost/yoursite','http://www.yoursite.com');

I will take an example to explain this tip.

imagine we want to export our drupal users with their roles. if a user has 3 roles, the result of the join query will be 3 rows for him.

Using GROUP_CONCAT will give us all roles in one single row.

Code block

SELECT u.uid, u.name, u.mail,
 GROUP_CONCAT(DISTINCT r.name SEPARATOR '/') as roles
from users u
 inner join users_roles ur on ur.uid = u.uid
 inner join role r using(rid)
 GROUP BY u.uid

notice here the GROUP BY u.uid (otherwise all users will be concatenated in 1 row).

see the syntax of this function

another useful mysql tip is to sort result in a particular order (not asc or desc)

imagine we have a table of animals, a field with the names and another with the type :  bird, dog and cat

in our result we want to see all cats at first then birds then dogs

we will use the ORDER BY FIELD(fieldname , ‘value1′, ‘value2′,…)

Code block

SELECT * FROM animals
 ORDER BY FIELD(type, 'cat',bird','dog')

Hello,

ever wanted to sort your nodes by post or comment date like forums do ( last “action” first) ?

you can do that easily with mysql and greatest select

Code block

SELECT n.nid, title,
GREATEST(n.created, last_comment_timestamp) as dateup
 
FROM node n
INNER JOIN `node_comment_statistics` using(nid)
 
order by dateup desc