Requêtes SQL utiles sur WordPress

Une collection de requêtes SQL utiles pour gérer certains aspects de votre site WordPress depuis PhpMyAdmin. Les codes présentés proviennent en partie d’un article de CatsWhoCode.

Commentaires

Supprimer les commentaires d’une URL particulière :

DELETE from wp_comments WHERE comment_author_url LIKE "%spamurl%" ;

1	DELETE from wp_comments WHERE comment_author_url LIKE "%spamurl%" ;

Supprimer tous les trackbacks :

DELETE FROM wp_comments WHERE comment_type="trackback";

1	DELETE FROM wp_comments WHERE comment_type="trackback";

Fermer les trackbacks sur ous les articles à la fois :

UPDATE wp_posts SET ping_status = 'closed';

1	UPDATE wp_posts SET ping_status = 'closed';

Supprimer tous les commentaires non-approuvés à la fois :

DELETE from wp_comments WHERE comment_approved = '0';

1	DELETE from wp_comments WHERE comment_approved = '0';

Supprimer tous les commentaires marqués en spam :

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

1	DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

Afficher la liste des adresses email de tous les commentateurs :

SELECT DISTINCT comment_author_email FROM wp_comments;

1	SELECT DISTINCT comment_author_email FROM wp_comments;

Articles

Supprimer toutes les révision d’articles, ainsi que leur données associées :

DELETE a,b,c FROM wp_posts a 
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) 
WHERE a.post_type = 'revision';

DELETE a,b,c FROM wp_posts a

LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)

LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)

WHERE a.post_type = 'revision';

Remplacer un mot par un autre dans tous les articles :

UPDATE wp_post SET post_content = replace(post_content, 'old_word', 'new_word' ) ;

1	UPDATE wp_post SET post_content = replace(post_content, 'old_word', 'new_word' ) ;

Ajouter un champ personnalisé à tous les articles :

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'MyCustomField'
AS meta_key 'myvalue' AS meta_value
FROM wp_posts 
WHERE ID NOT IN(
   SELECT post_id 
   FROM wp_postmeta 
   WHERE meta_key = 'MyCustomField'
)
AND post_type = 'post';

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

SELECT ID AS post_id, 'MyCustomField'

AS meta_key 'myvalue' AS meta_value

FROM wp_posts

WHERE ID NOT IN(

SELECT post_id

FROM wp_postmeta

WHERE meta_key = 'MyCustomField'

)

AND post_type = 'post';

Changer le chemin des images :

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsite.com', 'src="http://newsite.com');

1	UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsite.com', 'src="http://newsite.com');

UPDATE wp_posts SET  guid = REPLACE (guid, 'http://www.oldsite.com', 'http://newsite.com') WHERE post_type = 'attachment';

1	UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsite.com', 'http://newsite.com') WHERE post_type = 'attachment';

Utilisateurs et auteurs

Assigner les articles d’un auteur à un autre :

UPDATE wp_posts SET post_author = 'NEWID' WHERE post_author = 'OLDID';

1	UPDATE wp_posts SET post_author = 'NEWID' WHERE post_author = 'OLDID';

Changer le mot de passe d’un utilisateur :

UPDATE wp_users SET user_pass = MD5('new_password') WHERE user_login = 'username';

1	UPDATE wp_users SET user_pass = MD5('new_password') WHERE user_login = 'username';

Divers

Désactiver tous les plugins :

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

1	UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Supprimer tous les options temporaires (_transient) :

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');

1	DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');

Supprimer tous les mots-clés non-utilisés :

DELETE FROM wp_terms wt
INNER JOIN wp_term_taxonomy wtt 
    ON wt.term_id = wtt.term_id 
WHERE wtt.taxonomy = 'post_tag' 
AND wtt.count = 0;

DELETE FROM wp_terms wt

INNER JOIN wp_term_taxonomy wtt

ON wt.term_id = wtt.term_id

WHERE wtt.taxonomy = 'post_tag'

AND wtt.count = 0;

Modifier l’URL de base sur tous les contenus :

UPDATE wp_options 
SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') 
WHERE option_name = 'home' 
OR option_name = 'siteurl';

UPDATE wp_options

SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com')

WHERE option_name = 'home'

OR option_name = 'siteurl';

UPDATE wp_posts 
SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');

1 2	UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');

UPDATE wp_posts 
SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');

1 2	UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');