{"id":232733,"date":"2023-01-27T12:27:00","date_gmt":"2023-01-27T09:27:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=232733"},"modified":"2022-11-10T17:32:20","modified_gmt":"2022-11-10T14:32:20","slug":"kuinka-naeyttaeae-twiittiarkisto-wordpressissae-php-n-ja-mysql-n-avulla-crontab-tuen-kanssa","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/fi\/kuinka-naeyttaeae-twiittiarkisto-wordpressissae-php-n-ja-mysql-n-avulla-crontab-tuen-kanssa\/","title":{"rendered":"Kuinka n\u00e4ytt\u00e4\u00e4 twiittiarkisto WordPressiss\u00e4 PHP:n ja MySQL:n avulla Crontab-tuen kanssa?"},"content":{"rendered":"\n

Diskantti ei n\u00e4yt\u00e4 kaikkia twiittej\u00e4 hyvin. Minulla on t\u00e4h\u00e4n menness\u00e4 yli 600 twiitti\u00e4 (useimmat niist\u00e4 l\u00e4hetet\u00e4\u00e4n PHP-skriptill\u00e4<\/a> VPS<\/a> :ss\u00e4 ). Jos haluat tallentaa kopion kaikista historiallisista twiiteist\u00e4si omaan tietokantaasi paikallisesti, voit tietysti pyyt\u00e4\u00e4 arkistoa twitter-tilill\u00e4si [Asetukset \u2013 Tili \u2013 Pyyd\u00e4 arkistoa].<\/p>\n

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

pyynt\u00f6-twitter-arkisto<\/p>\n

Ent\u00e4 jos voit tallentaa kaikki twiitit ja n\u00e4ytt\u00e4\u00e4 ne blogissasi (WordPress-sivulla)? Idea on hieno, koska twiittej\u00e4 voidaan pit\u00e4\u00e4 SEO-tarkoituksiin tarkoitettuina artikkeleina. Joten sinun on luotava taulukko wordpress-tietokantaan, esimerkiksi anna meid\u00e4n nimet\u00e4 se tweets<\/code>.<\/p>\n

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

twiittihistoria-taulukko<\/p>\n

Meill\u00e4 on nelj\u00e4 saraketta, n\u00e4m\u00e4 ovat: twiitin tunnus<\/strong>, teksti<\/strong>, p\u00e4iv\u00e4m\u00e4\u00e4r\u00e4\/aika<\/strong>, uudelleentwiitin m\u00e4\u00e4r\u00e4<\/strong>. Tietysti voit saada lis\u00e4\u00e4 tietoa twitter API statuses\/user_timeline -kohdasta,<\/strong> mutta olemme vain kiinnostuneita n\u00e4ist\u00e4 nelj\u00e4st\u00e4.<\/p>\n

Paikallinen s\u00e4ilytys<\/h3>\n

Twitter-sovellusliittymien k\u00e4yt\u00f6lle on aina rajattu nopeus, joten et voi k\u00e4ytt\u00e4\u00e4 niit\u00e4 kovin usein. Ajatuksena t\u00e4ss\u00e4 on kuitenkin olla PHP-skripti, joka toimii crontabissa<\/a> (muutaman tunnin v\u00e4lein) ja hakee twiitit ja tallentaa ne paikallisesti tietokantaan.<\/p>\n

Voimme k\u00e4ytt\u00e4\u00e4 TwitterOAuth<\/strong> php -kirjastoa vuorovaikutukseen Twitterin API:iden kanssa:<\/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
Tallenna yll\u00e4 nimell\u00e4 twitter.php<\/strong>, jotta voimme sis\u00e4llytt\u00e4\u00e4 ne aina, jos haluamme k\u00e4ytt\u00e4\u00e4 Twitterin API-liittymi\u00e4.<\/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> varmistaa, ett\u00e4 tweetit on lis\u00e4tty oikein tietokantaan. Mb_detect_encoding<\/strong> varmistaa, ett\u00e4 API:ilta palautetut twiitit ovat todella UTF-8-koodattuja. Yll\u00e4 oleva PHP-skripti tarkistaa kaksoiskappaleiden varalta (Twitterin perusteella ID), joten ensimm\u00e4isen kerran uusi twiitti lis\u00e4t\u00e4\u00e4n, mutta toisella kerralla se vain p\u00e4ivitet\u00e4\u00e4n.<\/p>\n
Yhdell\u00e4 API-kutsulla saat maksimim\u00e4\u00e4r\u00e4n twiittej\u00e4, esim. 300. Jos sinulla on enemm\u00e4n twiittej\u00e4, voit aina soittaa useita kertoja skriptille ladataksesi kaikki twiitit. Meid\u00e4n on kuitenkin m\u00e4\u00e4ritett\u00e4v\u00e4 max_id-<\/strong> parametri, joka palauttaa t\u00e4t\u00e4 tunnusta vanhemmat twiitit (pienemm\u00e4t tunnukset). Jotta voimme:<\/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
Ajatuksena on tarkistaa vanhin twiitti (min id:ll\u00e4) ja palauttaa vain sit\u00e4 vanhemmat twiitit, joten jos suoritat skriptej\u00e4 useita kertoja, tuot kaikki twiitit.<\/p>\n
Nyt meill\u00e4 on tiedot valmiina ja voimme saada t\u00e4m\u00e4n skriptin toimimaan VPS-palvelimella<\/a> muutaman tunnin v\u00e4lein (taajuus riippuu sinusta) k\u00e4ytt\u00e4m\u00e4ll\u00e4 crontab -e<\/strong> -komentoa.<\/p>\n
N\u00e4yt\u00e4 twiitit WordPress-sivuilla<\/h3>\n
Nyt t\u00e4m\u00e4 sivu helloacm.com\/tweets-history\/<\/a> n\u00e4ytt\u00e4\u00e4 kaikki tietokannan puut. Tarvitset laajennuksen suorittaaksesi PHP\/HTML:n WordPress-sivulla. L\u00f6yd\u00e4t helposti t\u00e4llaisia \u200b\u200b\u200b\u200bWordPress-laajennuksia.<\/p>\n
Luo wordpress-sivu ja laita seuraava:<\/p>\n
[ include ]tweets.php[ \/ include ]<\/code><\/pre>\n
Sitten meid\u00e4n on asetettava PHP-skripti t\u00e4h\u00e4n yll\u00e4 m\u00e4\u00e4ritettyyn tweets.php-tiedostoon<\/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
K\u00e4yt\u00e4mme get_query_var(’page’)<\/strong> saadaksemme kyselymerkkijonon, jotta tied\u00e4mme, mit\u00e4 sivua katselemme.<\/p>\n
:  helloacm.com<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Kuinka n\u00e4ytt\u00e4\u00e4 twiittiarkisto WordPressiss\u00e4 PHP:n ja MySQL:n avulla Crontab-tuen kanssa?<\/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":[719,895,917,834,843,995,864],"tags":[1166],"class_list":["post-232733","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-kehittaejae","category-koodi","category-muut","category-opas-aloittelijoille","category-opetusohjelmia","category-sisaltomarkkinointi","category-wordpress-5","tag-affiai-fi"],"_links":{"self":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/232733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/comments?post=232733"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/posts\/232733\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media\/224614"}],"wp:attachment":[{"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/media?parent=232733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/categories?post=232733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wordpress.mediadoma.com\/fi\/wp-json\/wp\/v2\/tags?post=232733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}