使用php将数据从Twitter API插入MySQL数据库 [英] Inserting data from Twitter API into a MySQL Database using php

问题描述

PHP编码的新手-我已经建立了一个基于服务器的MySQL数据库，以便将twitter api数据插入其中.

Very new to PHP coding - I've set up a server based MySQL database to insert twitter api data into.

我可以检索twitter api数据并将其在网络上进行配置( http://www.pdanalytics.ca/StephenHarperTweets.php ).使用PHP，如何将其插入数据库.

I can retrieve twitter api data and profile it on the web (http://www.pdanalytics.ca/StephenHarperTweets.php). Using PHP, how do I insert it into my database.

我已经搜索过Google，并根据通用教程进行了尝试，但与我认为应该是一件很简单的任务相比，仍然相距遥远.

I've googled, and made attempts based on general tutorials, but still feel pretty far off what I believe should be a fairly straight forward task.

谢谢.

这是我的代码:

<?php

require_once('TwitterAPIExchange.php');

/** Set access tokens here - see: https://dev.twitter.com/apps/ **/
$settings = array(
'oauth_access_token' => "xxxxxxxxxxxxxxx",
'oauth_access_token_secret' => "xxxxxxxxxxxxxxx",
'consumer_key' => "xxxxxxxxxxxxx",
'consumer_secret' => "xxxxxxxxxxxxxxxxxxx"
);
$url = "https://api.twitter.com/1.1/statuses/user_timeline.json";

$requestMethod = "GET";

$getfield = '?screen_name=pmharper&count=20';
$twitter = new TwitterAPIExchange($settings);
$string = json_decode($twitter->setGetfield($getfield)
->buildOauth($url, $requestMethod)
->performRequest(),$assoc = TRUE);
if($string["errors"][0]["message"] != "") {echo "<h3>Sorry, there was a             problem.</h3><p>Twitter returned the following error message:</p><p>     <em>".$string[errors][0]["message"]."</em></p>";exit();}
foreach($string as $items)
{
    echo "Tweeted by: ". $items['user']['name']."<br />";       
    echo "Screen name: ". $items['user']['screen_name']."<br />";
    echo "Tweet: ". $items['text']."<br />";                
    echo "Time and Date of Tweet: ".$items['created_at']."<br />";
    echo "Tweet ID: ".$items['id_str']."<br />";
    echo "Followers: ". $items['user']['followers_count']."<br /><hr />";
}
?>

推荐答案

这是最终代码！

我使用MySQLi准备的用于插入查询的语句.我希望您已经创建了用于插入推文的数据库和表.您需要更改以下代码的前几行并开始使用它.

I used MySQLi prepared Statement for Insert Query. I hope you already created database and table for inserting tweets. You need to change first few lines of below code and start using it.

<?php
        define('DBHOST','localhost');
        define('DBUSERNAME','databaseusername');
        define('DBPASSWORD','databasepassword');
        define('DBNAME','databasename');
        define('TWEETTABLE','tweettablename');

        require_once('TwitterAPIExchange.php');

        /** Set access tokens here - see: https://dev.twitter.com/apps/ **/
        $settings = array(
        'oauth_access_token' => "xxxxxxxxxxxxxxx",
        'oauth_access_token_secret' => "xxxxxxxxxxxxxxx",
        'consumer_key' => "xxxxxxxxxxxxx",
        'consumer_secret' => "xxxxxxxxxxxxxxxxxxx"
        );
        $url = "https://api.twitter.com/1.1/statuses/user_timeline.json";

        $requestMethod = "GET";

        $getfield = '?screen_name=pmharper&count=20';
        $twitter = new TwitterAPIExchange($settings);
        $string = json_decode($twitter->setGetfield($getfield)
        ->buildOauth($url, $requestMethod)
        ->performRequest(),$assoc = TRUE);
        if($string["errors"][0]["message"] != "") {echo "<h3>Sorry, there was a             problem.</h3><p>Twitter returned the following error message:</p><p>     <em>".$string[errors][0]["message"]."</em></p>";exit();}
        foreach($string as $items)
        {
            echo "Tweeted by: ". $items['user']['name']."<br />";       
            echo "Screen name: ". $items['user']['screen_name']."<br />";
            echo "Tweet: ". $items['text']."<br />";                
            echo "Time and Date of Tweet: ".$items['created_at']."<br />";
            echo "Tweet ID: ".$items['id_str']."<br />";
            echo "Followers: ". $items['user']['followers_count']."<br /><hr />";
            echo insertTweets($items['user']['name'],$items['user']['screen_name'],$items['text'],$items['created_at'],$items['id_str'],$items['user']['followers_count']);
        }

        function insertTweets($name,$screen_name,$text,$created_at,$id_str,$followers_count){
            $mysqli = new mysqli(DBHOST, DBUSERNAME, DBPASSWORD, DBNAME);
            if ($mysqli->connect_errno) {
                return 'Failed to connect to Database: (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error; 
            }
            $prepareStmt='INSERT INTO '.DBNAME.'.'.TWEETTABLE.' (name, screen_name, text, created_at, id_str, followers_count) VALUES (?,?,?,?,?,?);';
            if ($insert_stmt = $mysqli->prepare($prepareStmt)){
                $insert_stmt->bind_param('ssssid', $name,$screen_name,$text,$created_at,$id_str,$followers_count);
                if (!$insert_stmt->execute()) {
                    $insert_stmt->close();
                    return 'Tweet Creation cannot be done at this moment.';
                }elseif($insert_stmt->affected_rows>0){
                    $insert_stmt->close();
                    return 'Tweet Added.';
                }else{
                    $insert_stmt->close();
                    return 'No Tweet were Added.';
                }
            }else{
                return 'Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error;
            }
        }              ?>

这篇关于使用php将数据从Twitter API插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！