{"id":2820,"date":"2016-03-11T05:53:01","date_gmt":"2016-03-11T05:53:01","guid":{"rendered":"http:\/\/musilda.cz\/?p=2820"},"modified":"2016-03-11T05:53:01","modified_gmt":"2016-03-11T05:53:01","slug":"20-uzitecnych-sql-prikazu-pro-wordpress","status":"publish","type":"post","link":"https:\/\/affinite.io\/cs\/20-uzitecnych-sql-prikazu-pro-wordpress\/","title":{"rendered":"20 u\u017eite\u010dn\u00fdch SQL p\u0159\u00edkaz\u016f pro WordPress"},"content":{"rendered":"

WordPress ukl\u00e1d\u00e1 v\u0161echna data do MySQL datab\u00e1ze. Zkr\u00e1tka v\u0161echno co vytvo\u0159\u00edte – \u010dl\u00e1nky, str\u00e1nky, m\u00e9dia, menu, coment\u00e1\u0159e, nastaven\u00ed, prost\u011b v\u0161e se ulo\u017e\u00ed v datab\u00e1zi.<\/p>\n

Administrace WordPressu je skv\u011bl\u00e1 v tom, \u017ee v\u00e1m umo\u017e\u0148uje, v\u0161echno toto upravovat, mazat, vytv\u00e1\u0159et, a\u017e na jeden p\u0159\u00edpad.<\/p>\n

A t\u00edm jsou hromadn\u00e9 \u00fapravy.\u00a0<\/strong><\/p>\n

Pokud pot\u0159ebujete odstranit u v\u0161ech \u010dl\u00e1nk\u016f n\u011bjak\u00fd custom field, t\u0159eba po odinstalovan\u00e9m pluginu, nebo n\u00e1hledov\u00e9 obr\u00e1zky, \u010dek\u00e1 v\u00e1s nekone\u010dn\u00e9, frustruj\u00edc\u00ed rozklik\u00e1v\u00e1n\u00ed jednotliv\u00fdch \u010dl\u00e1nk\u016f.<\/p>\n

Na\u0161t\u011bst\u00ed, pomoc\u00ed jednoduch\u00fdch SQL p\u0159\u00edkaz\u016f, dok\u00e1\u017eete velmi jednodu\u0161e, dos\u00e1hnout super v\u00fdsledk\u016f.<\/p>\n

V\u017edycky z\u00e1lohujte!<\/strong><\/h2>\n

Ne\u017e cokoliv z t\u011bchto p\u0159\u00edkaz\u016f vyzkou\u0161\u00edte, ud\u011blejte si z\u00e1lohu datab\u00e1ze.<\/p>\n

Existuje \u0159ada z\u00e1lohovac\u00edch plugin\u016f pro WordPress<\/a>, nebo, pokud budete zad\u00e1vat p\u0159\u00edkazy v phpMyAdmin, tak ud\u011blejte export. U\u0161et\u0159\u00edte si spoustu starost\u00ed.<\/p>\n

U\u017eite\u010dn\u00e9 SQL p\u0159\u00edkazy pro WordPress<\/strong><\/h2>\n

P\u0159id\u00e1n\u00ed custom fieldu v\u0161em \u010dl\u00e1nk\u016fm<\/strong><\/h3>\n

 <\/p>\n

\nINSERT INTO wp_postmeta (post_id, meta_key, meta_value)\nSELECT ID AS post_id, 'novy-custom-field'\nAS meta_key 'hodnota-custom-fieldu' AS meta_value FROM wp_posts\nWHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'novy-custom-field');\n<\/pre>\n
Kompletn\u00ed smaz\u00e1n\u00ed custom fieldu<\/strong><\/h3>\n
\nDELETE FROM wp_postmeta WHERE meta_key = 'jmeno-custom-fieldu';\n<\/pre>\n
Odstran\u011bn\u00ed nep\u0159i\u0159azen\u00fdch \u0161t\u00edtk\u016f<\/strong><\/h3>\n
Kdy\u017e sma\u017eete \u010dl\u00e1nek, \u0161t\u00edtky z\u016fst\u00e1vaj\u00ed. T\u00edmto sma\u017eete v\u0161echny, kter\u00e9 nejsou p\u0159i\u0159azeny, k \u010dl\u00e1nku.<\/p>\n
\nSELECT * From wp_terms wt\nINNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;\n<\/pre>\n
Hromadn\u00e9 smaz\u00e1n\u00ed SPAMu<\/strong><\/h3>\n
\nDELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';\n<\/pre>\n
Hromadn\u00e9 smaz\u00e1n\u00ed neschv\u00e1len\u00fdch koment\u00e1\u0159\u016f<\/strong><\/h3>\n
\nDELETE FROM wp_comments WHERE comment_approved = 0\n<\/pre>\n
Uzav\u0159en\u00ed koment\u00e1\u0159\u016f, pro star\u0161\u00ed \u010dl\u00e1nky<\/strong><\/h3>\n
\nUPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';\n<\/pre>\n
Vypnut\u00ed pinback\u016f a trackbak\u016f<\/strong><\/h3>\n
\nUPDATE wp_posts SET ping_status = 'closed';\n<\/pre>\n
Smaz\u00e1n\u00ed koment\u00e1\u0159\u016f s ur\u010ditou url<\/strong><\/h3>\n
N\u011bkdo v\u00e1s otravuje nesmysln\u00fdmi koment\u00e1\u0159i? Tak je hromadn\u011b sma\u017ete.<\/p>\n
\nDELETE from wp_comments WHERE comment_author_url LIKE \"%urlspamera%\" ;\n<\/pre>\n
V\u00fdpis \u010dl\u00e1nk\u016f, star\u0161\u00edch, ne\u017e X dn\u00ed\/strong><\/h3>\n
M\u00edsto X, pou\u017eijte po\u010det dn\u00ed.<\/p>\n
\nSELECT * FROM 'wp_posts'\nWHERE 'post_type' = 'post'\nAND DATEDIFF(NOW(), 'post_date') > X\n<\/pre>\n
Smaz\u00e1n\u00ed nepot\u0159ebn\u00fdch shortcod\u016f<\/strong><\/h3>\n
Pozor – funguje pouze na nep\u00e1rov\u00e9 shortcody.<\/p>\n
\nUPDATE wp_post SET post_content = replace(post_content, '[facebook-button]', '' ) ;\n<\/pre>\n
Zm\u011bna \u010dl\u00e1nk\u016f na str\u00e1nky<\/strong><\/h3>\n
\nUPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'\n<\/pre>\n
Zm\u011bna autora u v\u0161ech \u010dl\u00e1nk\u016f<\/strong><\/h3>\n
\nUPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;\n<\/pre>\n
Kompletn\u00ed smaz\u00e1n\u00ed v\u0161ech reviz\u00ed<\/strong><\/h3>\n
\nDELETE FROM wp_posts WHERE post_type = \"revision\";\n<\/pre>\n
Deaktivace v\u0161ech plugin\u016f<\/strong><\/h3>\n
Hod\u00ed se p\u0159edev\u0161\u00edm, kdy\u017e v\u00e1m kompletn\u011b p\u0159estane fungovat web.<\/p>\n
\nUPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';\n<\/pre>\n
Zm\u011bna url u v\u0161ech \u010dl\u00e1nk\u016f<\/strong><\/h3>\n
Ide\u00e1ln\u00ed po p\u0159esunu cel\u00e9ho webu. J\u00e1 pou\u017e\u00edv\u00e1m plugin Better Search and Replace<\/p>\n
\nUPDATE wp_posts SET guid = replace(guid, 'http:\/\/www.old-site.com','http:\/\/www.new-site.com);\n<\/pre>\n
Zm\u011bna defaultn\u00edho Admin u\u017eivatele<\/strong><\/h3>\n
\nUPDATE wp_users SET user_login = 'Master' WHERE user_login = 'Admin';\n<\/pre>\n
Manu\u00e1ln\u00ed p\u0159eps\u00e1n\u00ed va\u0161eho hesla<\/strong><\/h3>\n
\nUPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;\n<\/pre>\n
Vyhled\u00e1n\u00ed a zm\u011bna obsahu v \u010dl\u00e1nku<\/strong><\/h3>\n
\nUPDATE wp_posts SET 'post_content'\n= REPLACE ('post_content',\n'OriginalText',\n'NewText');\n<\/pre>\n
Zm\u011bna url obr\u00e1zk\u016f, po p\u0159esunu webu<\/strong><\/h3>\n
\nUPDATE wp_posts\nSET post_content = REPLACE (post_content, 'src=\u201dhttp:\/\/www.myoldurl.com', 'src=\u201dhttp:\/\/www.mynewurl.com');\n<\/pre>\n
Vymaz\u00e1n\u00ed v\u0161ech transient<\/strong><\/h3>\n
\n'DELETE FROM wp_options WHERE option_name LIKE \"_transient_%\"'\n<\/pre>\n
Tak to je v\u0161e, nezapome\u0148te z\u00e1lohovat a pokud pou\u017e\u00edv\u00e1te jin\u00fd prefix datab\u00e1ze, ne\u017e wp_ tak mus\u00edte p\u0159\u00edkazy upravit dle toho.<\/p>\n","protected":false},"excerpt":{"rendered":"
WordPress ukl\u00e1d\u00e1 v\u0161echna data do MySQL datab\u00e1ze. Zkr\u00e1tka v\u0161echno co vytvo\u0159\u00edte – \u010dl\u00e1nky, str\u00e1nky, m\u00e9dia, menu, coment\u00e1\u0159e, nastaven\u00ed, prost\u011b v\u0161e se ulo\u017e\u00ed v datab\u00e1zi. Administrace WordPressu je skv\u011bl\u00e1 v tom, \u017ee v\u00e1m umo\u017e\u0148uje, v\u0161echno toto upravovat, mazat, vytv\u00e1\u0159et, a\u017e na jeden p\u0159\u00edpad. A t\u00edm jsou hromadn\u00e9 \u00fapravy.\u00a0 Pokud pot\u0159ebujete odstranit u v\u0161ech \u010dl\u00e1nk\u016f n\u011bjak\u00fd custom<\/p>\n","protected":false},"author":1,"featured_media":2635,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_uag_custom_page_level_css":"","footnotes":""},"categories":[19,30],"tags":[361],"class_list":["post-2820","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-snippety","category-wordpress","tag-sql-wordpress"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet.jpg",650,400,false],"thumbnail":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet-150x150.jpg",150,150,true],"medium":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet-300x185.jpg",300,185,true],"medium_large":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet.jpg",640,394,false],"large":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet.jpg",640,394,false],"1536x1536":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet.jpg",650,400,false],"2048x2048":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet.jpg",650,400,false],"archive-list":["https:\/\/affinite.io\/cs\/wp-content\/uploads\/sites\/2\/2015\/12\/wordpress-snippet-400x265.jpg",400,265,true]},"uagb_author_info":{"display_name":"Affinite","author_link":"https:\/\/affinite.io\/cs\/author\/affinite\/"},"uagb_comment_info":2,"uagb_excerpt":"WordPress ukl\u00e1d\u00e1 v\u0161echna data do MySQL datab\u00e1ze. Zkr\u00e1tka v\u0161echno co vytvo\u0159\u00edte – \u010dl\u00e1nky, str\u00e1nky, m\u00e9dia, menu, coment\u00e1\u0159e, nastaven\u00ed, prost\u011b v\u0161e se ulo\u017e\u00ed v datab\u00e1zi. Administrace WordPressu je skv\u011bl\u00e1 v tom, \u017ee v\u00e1m umo\u017e\u0148uje, v\u0161echno toto upravovat, mazat, vytv\u00e1\u0159et, a\u017e na jeden p\u0159\u00edpad. A t\u00edm jsou hromadn\u00e9 \u00fapravy.\u00a0 Pokud pot\u0159ebujete odstranit u v\u0161ech \u010dl\u00e1nk\u016f n\u011bjak\u00fd custom","_links":{"self":[{"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/posts\/2820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/comments?post=2820"}],"version-history":[{"count":0,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/posts\/2820\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/media\/2635"}],"wp:attachment":[{"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/media?parent=2820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/categories?post=2820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/affinite.io\/cs\/wp-json\/wp\/v2\/tags?post=2820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}