{"id":232737,"date":"2023-01-27T12:38:00","date_gmt":"2023-01-27T09:38:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232737"},"modified":"2022-11-10T17:33:33","modified_gmt":"2022-11-10T14:33:33","slug":"kuidas-kuvada-twitteri-arhiivi-wordpressis-php-ja-mysql-abil-crontabi-toega","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/et\/kuidas-kuvada-twitteri-arhiivi-wordpressis-php-ja-mysql-abil-crontabi-toega\/","title":{"rendered":"Kuidas kuvada Twitteri arhiivi WordPressis PHP ja MySQL abil Crontabi toega?"},"content":{"rendered":"\n

Tweeter ei suuda k\u00f5iki s\u00e4utse kuvada. Mul on praeguseks \u00fcle 600 s\u00e4utsu (enamik neist on saadetud PHP skriptiga<\/a> VPS<\/a> -is ). Kui soovite salvestada k\u00f5igi oma ajalooliste s\u00e4utsude koopiad oma andmebaasi kohapeal, v\u00f5ite loomulikult taotleda arhiivi oma Twitteri kontol [Seaded \u2013 Konto \u2013 Taotle arhiivi].<\/p>\n

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

p\u00e4ring-twitter-arhiiv<\/p>\n

Kuidas oleks sellega, et saate salvestada k\u00f5ik s\u00e4utsud ja kuvada need oma ajaveebis (WordPressi lehel)? Idee on suurep\u00e4rane, sest s\u00e4utse v\u00f5ib vaadelda kui SEO eesm\u00e4rkidel m\u00f5eldud artikleid. Seega peaksite WordPressi andmebaasis looma tabeli, n\u00e4iteks andke meile nimi tweets<\/code>.<\/p>\n

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

s\u00e4utsu-ajalugu-tabel<\/p>\n

Meil on neli veergu, need on: s\u00e4utsu ID<\/strong>, tekst<\/strong>, kuup\u00e4ev\/kellaaeg<\/strong>, retweet count<\/strong>. Muidugi saate lisateavet twitteri API staatuste\/kasutaja_ajajoone alt<\/strong>, kuid meid huvitavad just need neli.<\/p>\n

Kohalik s\u00e4ilitamine<\/h3>\n

Twitteri API-de kasutamisel on alati kiiruspiirang, nii et te ei saa neid v\u00e4ga sageli kasutada. Siin on aga m\u00f5te PHP-skriptil, mis t\u00f6\u00f6tab crontabis<\/a> (iga paari tunni j\u00e4rel) ja hangib s\u00e4utsud alla ja salvestab need kohapeal andmebaasi.<\/p>\n

TwitterOAuthi<\/strong> php teeki saame kasutada Twitteri API-dega suhtlemiseks:<\/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
Salvestage \u00fclalpool kui twitter.php<\/strong>, et saaksime need Twitteri API-dele juurde p\u00e4\u00e4seda iga kord.<\/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 ("SET NAMES utf8")<\/strong> tagab, et s\u00e4utsud sisestatakse andmebaasi \u00f5igesti. Mb_detect_encoding<\/strong> kontrollib, et API-dest tagastatud s\u00e4utsudel on tegelikult UTF-8 kodeering. \u00dclaltoodud PHP-skript kontrollib duplikaate (Twitteri p\u00f5hjal). ID), nii et esimest korda lisatakse uus s\u00e4uts, kuid teisel korral v\u00e4rskendatakse seda lihtsalt.<\/p>\n
\u00dche API-k\u00f5nega saab saada maksimaalne arv s\u00e4utse, nt 300. Kui teil on rohkem s\u00e4utse, v\u00f5ite alati skripti mitu korda helistada, et k\u00f5ik s\u00e4utsud alla laadida. Peame aga m\u00e4\u00e4rama parameetri max_id<\/strong>, mis tagastab sellest ID-st vanemad s\u00e4utsud (v\u00e4iksemad ID-d). Nii me saame:<\/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
Idee on kontrollida vanimat s\u00e4utsu (min id-ga) ja tagastada ainult sellest vanemad s\u00e4utsud, nii et kui k\u00e4ivitate skripte mitu korda, impordite k\u00f5ik s\u00e4utsud.<\/p>\n
N\u00fc\u00fcd on meil andmed valmis ja saame selle skripti k\u00e4ivitada VPS-i serveris<\/a> iga paari tunni j\u00e4rel (sagedus s\u00f5ltub sinust), kasutades k\u00e4sku crontab -e .<\/strong><\/p>\n
Kuva s\u00e4utsud WordPressi lehtedel<\/h3>\n
N\u00fc\u00fcd n\u00e4itab see leht helloacm.com\/tweets-history\/<\/a> k\u00f5iki andmebaasi puid. PHP\/HTML-i k\u00e4ivitamiseks WordPressi lehel oleks vaja pluginat. Selliseid wordpressi pluginaid on lihtne leida.<\/p>\n
Looge WordPressi leht ja sisestage j\u00e4rgmine:<\/p>\n
[ include ]tweets.php[ \/ include ]<\/code><\/pre>\n
Seej\u00e4rel peame sisestama PHP-skripti sellesse \u00fclaltoodud tweets.php<\/strong> -sse .<\/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
Kasutame p\u00e4ringustringi hankimiseks k\u00e4sku get_query_var(‘page’)<\/strong>, et teaksime, millist lehte me vaatame.<\/p>\n
:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Kuidas kuvada WordPressis s\u00e4utsude arhiivi PHP ja MySQL abil Crontabi toega?<\/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":[718,833,894,916,842,994,863],"tags":[1165],"class_list":["post-232737","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-arendaja","category-juhend-algajatele","category-kood","category-muud","category-opetused","category-sisuturundus","category-wordpress-4","tag-affiai-et"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/232737","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/comments?post=232737"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/posts\/232737\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media\/224614"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/media?parent=232737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/categories?post=232737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/et\/wp-json\/wp\/v2\/tags?post=232737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}