{"id":232730,"date":"2023-01-27T12:52:00","date_gmt":"2023-01-27T09:52:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232730"},"modified":"2022-11-10T17:31:50","modified_gmt":"2022-11-10T14:31:50","slug":"jak-wyswietlic-archiwum-tweetow-w-wordpress-za-pomoca-php-i-mysql-z-obsluga-crontab","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/pl\/jak-wyswietlic-archiwum-tweetow-w-wordpress-za-pomoca-php-i-mysql-z-obsluga-crontab\/","title":{"rendered":"Jak wy\u015bwietli\u0107 archiwum tweet\u00f3w w WordPress za pomoc\u0105 PHP i MySQL z obs\u0142ug\u0105 Crontab?"},"content":{"rendered":"\n

G\u0142o\u015bnik wysokotonowy nie jest dobry w wy\u015bwietlaniu wszystkich tweet\u00f3w. Mam do tej pory ponad 600 tweet\u00f3w (wi\u0119kszo\u015b\u0107 z nich jest wysy\u0142ana przez skrypt PHP<\/a> na VPS<\/a> ). Je\u015bli chcesz zapisa\u0107 kopi\u0119 wszystkich swoich historycznych tweet\u00f3w lokalnie we w\u0142asnej bazie danych, mo\u017cesz oczywi\u015bcie poprosi\u0107 o archiwum na swoim koncie Twitter [Ustawienia \u2013 Konto \u2013 Popro\u015b o archiwum].<\/p>\n

\"Jak<\/a><\/p>\n

\u017c\u0105danie-archiwum-twittera<\/p>\n

Co powiesz na to, \u017ce mo\u017cesz zapisa\u0107 wszystkie tweety i wy\u015bwietli\u0107 je na swoim blogu (strona WordPress)? Pomys\u0142 jest \u015bwietny, poniewa\u017c tweety mog\u0105 by\u0107 postrzegane jako artyku\u0142y do \u200b\u200bcel\u00f3w SEO. Tak wi\u0119c musia\u0142by\u015b utworzy\u0107 tabel\u0119 w bazie danych wordpress, na przyk\u0142ad nazwijmy j\u0105 tweets<\/code>.<\/p>\n

\"Jak<\/a><\/p>\n

tweet-historia-tabela<\/p>\n

Mamy cztery kolumny, s\u0105 to: identyfikator tweeta<\/strong>, tekst<\/strong>, data\/godzina<\/strong>, liczba retweet\u00f3w<\/strong>. Oczywi\u015bcie mo\u017cesz uzyska\u0107 wi\u0119cej informacji z twitter API statuss\/user_timeline<\/strong>, ale nas interesuj\u0105 tylko te cztery.<\/p>\n

Przechowywanie lokalne<\/h3>\n

Zawsze istnieje limit szybko\u015bci korzystania z interfejs\u00f3w API Twittera, wi\u0119c nie mo\u017cna ich u\u017cywa\u0107 zbyt cz\u0119sto. Pomys\u0142 polega jednak na tym, aby skrypt PHP uruchamia\u0142 si\u0119 w crontab<\/a> (co kilka godzin) i pobiera\u0142 tweety i zapisywa\u0142 je lokalnie w bazie danych.<\/p>\n

Mo\u017cemy u\u017cy\u0107 biblioteki php TwitterOAuth<\/strong> do interakcji z API Twittera:<\/p>\n

\/\/ <a class=\"vglnk\" target=\"_blank\" href=\"http:\/\/helloacm.com\" rel=\"nofollow\"><span>helloacm<\/span><span>.<\/span><span>com<\/span><\/a>\n\/\/ How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support\nrequire('TwitterOAuth-1\/TwitterOAuth\/TwitterOAuth.php');\nrequire('TwitterOAuth-1\/TwitterOAuth\/Exception\/TwitterException.php');\nrequire('TwitterOAuth-1\/config.php'); \u00a0\nuse TwitterOAuthTwitterOAuth;\ndate_default_timezone_set('UTC');\n\u00a0\n$config = array(\n\u00a0 \u00a0 'consumer_key' \u00a0 \u00a0 \u00a0 => $twitter_apikey, \/\/ API key\n\u00a0 \u00a0 'consumer_secret' \u00a0 \u00a0=> $twitter_apisecret, \/\/ API secret\n\u00a0 \u00a0 'oauth_token' \u00a0 \u00a0 \u00a0 \u00a0=> $twitter_accesstoken, \/\/ not needed for app only\n\u00a0 \u00a0 'oauth_token_secret' => $twitter_accesstokensecret,\n\u00a0 \u00a0 'output_format' \u00a0 \u00a0 \u00a0=> 'object'\n);\n$tw = new TwitterOAuth($config);<\/code><\/pre>\n
Zapisz powy\u017cej jako twitter.php<\/strong>, aby\u015bmy mogli je uwzgl\u0119dni\u0107 za ka\u017cdym razem, je\u015bli chcemy uzyska\u0107 dost\u0119p do API Twittera.<\/p>\n
\/\/ <a class=\"vglnk\" target=\"_blank\" href=\"http:\/\/helloacm.com\" rel=\"nofollow\"><span>helloacm<\/span><span>.<\/span><span>com<\/span><\/a>\n\/\/ How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support\n\u00a0 require(\"twitter.php\"); \n\u00a0\n\u00a0 $params = array(\n\u00a0 \u00a0 \u00a0 'screen_name' => $twitter_screenname,\n\u00a0 \u00a0 \u00a0 'user_id' => $twitter_ownerid,\n\u00a0 \u00a0 \u00a0 'count' => 300,\n\u00a0 \u00a0 \u00a0 'include_rts' => 1,\n\u00a0 \u00a0 \u00a0 'exclude_replies' => false\n\u00a0 );\n\u00a0 $tweets = $tw->get('statuses\/user_timeline', $params);\n\u00a0\n\u00a0 mysql_query(\"SET NAMES utf8\");\n\u00a0 if(!empty($tweets)) {\n\u00a0 \u00a0 \u00a0 foreach($tweets as $tweet) {\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $id = $tweet->id;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $retweet_count = (integer)$tweet->retweet_count;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $created_at = $tweet->created_at;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $text = $tweet->text;\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $text = mysql_real_escape_string($text);\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $query = \"select count(1) from `tweets` where `id` = '$id'\";\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $result = mysql_query($query);\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $cnt = mysql_result($result, 0, 0);\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if ($cnt == 0) {\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $query = \"insert into `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count'\"; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 } else {\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 $query = \"update `tweets` set `id`='$id', `text`='$text', `created_at`='$created_at',`retweet_count`='$retweet_count' where `id`='$id'\"; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 mysql_query($query);\n\u00a0 \u00a0 \u00a0 }\n\u00a0 }<\/code><\/pre>\n
Mysql_query (\u201eSET NAMES utf8")<\/strong> zapewnia, \u017ce \u200b\u200btweety s\u0105 poprawnie wstawiane do bazy danych. mb_detect_encoding<\/strong> zweryfikuje, czy tweety zwr\u00f3cone przez interfejsy API s\u0105 faktycznie zakodowane w UTF-8. Powy\u017cszy skrypt PHP sprawdzi duplikaty (na podstawie Twittera). ID), wi\u0119c za pierwszym razem nowy tweet zostanie wstawiony, ale za drugim razem zostanie po prostu zaktualizowany.<\/p>\n
Maksymalna liczba tweet\u00f3w, kt\u00f3re mo\u017cesz uzyska\u0107 jednym wywo\u0142aniem API, np. 300. Je\u015bli masz wi\u0119cej tweet\u00f3w, zawsze mo\u017cesz kilkakrotnie wywo\u0142a\u0107 skrypt, aby pobra\u0107 wszystkie tweety. Musimy jednak okre\u015bli\u0107 parametr max_id<\/strong>, kt\u00f3ry zwr\u00f3ci tweety starsze ni\u017c ten identyfikator (mniejsze identyfikatory). Wi\u0119c mo\u017cemy:<\/p>\n
\/\/ <a class=\"vglnk\" target=\"_blank\" href=\"http:\/\/helloacm.com\" rel=\"nofollow\"><span>helloacm<\/span><span>.<\/span><span>com<\/span><\/a>\n\/\/ How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support\n\u00a0 $query = \"select min(`id`) from `tweets`\";\n\u00a0 $result = mysql_query($query) or die(mysql_error());\n\u00a0 $cnt = mysql_result($result, 0, 0); \u00a0 \u00a0\n\u00a0\n\u00a0 $params = array(\n\u00a0 \u00a0 \u00a0 'screen_name' => $twitter_screenname,\n\u00a0 \u00a0 \u00a0 'user_id' => $twitter_ownerid,\n\u00a0 \u00a0 \u00a0 'count' => 1000,\n\u00a0 \u00a0 \u00a0 'max_id' => $cnt,\n\u00a0 \u00a0 \u00a0 'include_rts' => 1,\n\u00a0 \u00a0 \u00a0 'exclude_replies' => false\n\u00a0 );<\/code><\/pre>\n
Chodzi o to, aby sprawdzi\u0107 najstarszy tweet (z identyfikatorem mini) i zwr\u00f3ci\u0107 tylko starsze tweety, wi\u0119c je\u015bli uruchomisz skrypt kilka razy, zaimportujesz wszystkie tweety.<\/p>\n
Teraz mamy gotowe dane i mo\u017cemy uruchomi\u0107 ten skrypt na serwerze VPS<\/a> co kilka godzin (cz\u0119stotliwo\u015b\u0107 zale\u017cy od Ciebie) za pomoc\u0105 polecenia crontab -e<\/strong>.<\/p>\n
Wy\u015bwietlaj tweety na stronach WordPress<\/h3>\n
Teraz ta strona helloacm.com\/tweets-history\/<\/a> pokazuje wszystkie drzewa z bazy danych. Potrzebujesz wtyczki do wykonywania PHP\/HTML na stronie WordPress. Mo\u017cesz \u0142atwo znale\u017a\u0107 takie wtyczki wordpress.<\/p>\n
Utw\u00f3rz stron\u0119 wordpress i umie\u015b\u0107 nast\u0119puj\u0105ce elementy:<\/p>\n
[ include ]tweets.php[ \/ include ]<\/code><\/pre>\n
Nast\u0119pnie musimy umie\u015bci\u0107 skrypt PHP w podanym powy\u017cej tweets.php .<\/strong><\/p>\n
<php\n\/\/ <a class=\"vglnk\" target=\"_blank\" href=\"http:\/\/helloacm.com\" rel=\"nofollow\"><span>helloacm<\/span><span>.<\/span><span>com<\/span><\/a>\n\/\/ How to Show Tweets Archive in WordPress using PHP and MySQL with Crontab Support\n\u00a0 global $wpdb;\n\u00a0 \n\u00a0 $query = \"select count(1) from `tweets`\";\n\u00a0 $total = $wpdb->get_var($query);\n\u00a0 \n\u00a0 $per = 50;\n\u00a0 $page = get_query_var('page');\n\u00a0 if (!$page) {\n\u00a0 \u00a0 $page = 1;\n\u00a0 }\n\u00a0 \n\u00a0 \/\/ pagination\n\u00a0 $totalpages = ceil($total \/ $per);\n\u00a0 if ($page > $totalpages) {\n\u00a0 \u00a0 $page = 1;\n\u00a0 }\n\u00a0 \n\u00a0 $lowerbound = ($page - 1) * $per;\n\u00a0 $upperbound = $lowerbound + $per;\n\u00a0 $lmt=\" limit \". $lowerbound. \",\". $per;\n\u00a0 \n\u00a0 $query = \"select * from `tweets` order by `id` desc $lmt\";\n\u00a0 \n\u00a0 $result = $wpdb->get_results($query);\n\u00a0 \n\u00a0 if ($result) {\n\u00a0 \u00a0 foreach ($result as $tweet) {\n\u00a0 \u00a0 \u00a0 $created_at = $tweet->created_at;\n\u00a0 \u00a0 \u00a0 $text = $tweet->text;\n\u00a0 \u00a0 \u00a0 $retweet_count = $tweet->retweet_count;\n\u00a0 \u00a0 \u00a0 \/\/ display tweets\n\u00a0 \u00a0 \u00a0 echo \"$text - $created_at\";\n\u00a0 \u00a0 \u00a0 if ($retweet_count > 0) {\n\u00a0 \u00a0 \u00a0 \u00a0 echo \"($retweet_count Retweeted)\";\n\u00a0 \u00a0 \u00a0 }\n\u00a0 \u00a0 }\n\u00a0 } \u00a0\n\u00a0 \n\u00a0 \/\/ show prev\/next page links\n\u00a0 if ($page > 1) {\n\u00a0 \u00a0 echo \"<a href='?page=\".($page-1).\"'>Newer Tweets<\/a> - \";\n\u00a0 }\n\u00a0 if ($page < $totalpages) {\n\u00a0 \u00a0 echo \"<a href='?page=\".($page+1).\"'>Older Tweets<\/a>\";\n\u00a0 }<\/code><\/pre>\n
U\u017cywamy get_query_var(’page’),<\/strong> aby uzyska\u0107 ci\u0105g zapytania, aby\u015bmy wiedzieli, na kt\u00f3rej stronie ogl\u0105damy.<\/p>\n
\u0179r\u00f3d\u0142o nagrywania:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Jak wy\u015bwietli\u0107 archiwum tweet\u00f3w w WordPress za pomoc\u0105 PHP i MySQL z obs\u0142ug\u0105 Crontab?<\/p>\n","protected":false},"author":1,"featured_media":224614,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_wp_rev_ctl_limit":""},"categories":[721,919,897,997,836,845,866],"tags":[1169],"class_list":["post-232730","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-deweloper","category-inny","category-kod","category-marketing-tresci","category-przewodnik-dla-poczatkujacych","category-samouczki","category-wordpress-7","tag-affiai-pl"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/232730","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/comments?post=232730"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/posts\/232730\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media\/224614"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/media?parent=232730"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/categories?post=232730"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/pl\/wp-json\/wp\/v2\/tags?post=232730"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}