PHP db::in_table方法代码示例

$cursor = '-1';
while ($cursor != 0) {
    $connection->request('GET', $connection->url('1.1/friends/ids'), array('user_id' => $engagement_user_id, 'cursor' => $cursor));
    $http_code = $connection->response['code'];
    if ($http_code == 200) {
        $data = json_decode($connection->response['response'], true);
        // Get the list of friend user_ids, which will be an array
        $ids = $data['ids'];
        // Get the cursor value for the next request
        $cursor = $data['next_cursor_str'];
        // If there are any friends returned
        if (sizeof($ids)) {
            foreach ($ids as $user_id) {
                // If this friend is already in the table,
                // set the current field back to 1
                if ($db->in_table('friends', "user_id={$user_id}")) {
                    $db->update('friends', 'current=1', "user_id={$user_id}");
                } else {
                    // If this is a new friend,
                    // insert it with a current value of 1
                    $db->insert('friends', "user_id={$user_id},current=1");
                    // If this is not the first time friends have been collected,
                    // record this new friend event in the follow_log table
                    if (!$first_collection) {
                        $db->insert('follow_log', "user_id={$user_id},event='friend'");
                    }
                }
            }
        } else {
            // Stop collecting if no more friends are found
            break;

     $connection->request('GET', $connection->url('1.1/direct_messages/sent'), array('include_entities' => 'false', 'count' => 100));
 } else {
     --$max_id;
     $connection->request('GET', $connection->url('1.1/direct_messages/sent'), array('include_entities' => 'false', 'count' => 100, 'max_id' => $max_id));
 }
 if ($connection->response['response'] == '[]') {
     break;
 }
 if ($connection->response['code'] != 200) {
     break;
 }
 $results = json_decode($connection->response['response']);
 foreach ($results as $dm) {
     $dm_id = $dm->id;
     $max_id = $dm_id;
     if ($db->in_table('dms', "dm_id={$dm_id}")) {
         continue;
     }
     $dm_text = $db->escape($dm->text);
     $created_at = $db->date($dm->created_at);
     $sender_user_id = $dm->sender->id;
     $recipient_user_id = $dm->recipient->id;
     $db->insert('dms', "dm_id={$dm_id},dm_text='{$dm_text}',created_at='{$created_at}',\r\n\t\t\tsender_user_id={$sender_user_id},recipient_user_id={$recipient_user_id},sent=1");
     $screen_name = $db->escape($dm->recipient->screen_name);
     $name = $db->escape($dm->recipient->name);
     $location = $db->escape($dm->recipient->location);
     $description = $db->escape($dm->recipient->description);
     $url = $db->escape($dm->recipient->url);
     $profile_image_url = $db->escape($dm->recipient->profile_image_url);
     $created_at = $dm->recipient->created_at;
     $friends_count = $dm->recipient->friends_count;

 $query = 'SELECT cache_id, raw_tweet ' . 'FROM json_cache';
 $result = $oDB->select($query);
 while ($row = mysqli_fetch_assoc($result)) {
     $cache_id = $row['cache_id'];
     // Each JSON payload for a tweet from the API was stored in the database
     // by serializing it as text and saving it as base64 raw data
     $tweet_object = unserialize(base64_decode($row['raw_tweet']));
     // Delete cached copy of tweet
     $oDB->select("DELETE FROM json_cache WHERE cache_id = {$cache_id}");
     // Limit tweets tgo a single language,
     // such as 'en' for English
     //if ($tweet_object->lang <> 'en') {continue;}
     // The streaming API sometimes sends duplicates,
     // Test the tweet_id before inserting
     $tweet_id = $tweet_object->id_str;
     if ($oDB->in_table('tweets', 'tweet_id=' . $tweet_id)) {
         continue;
     }
     // Gather tweet data from the JSON object
     // $oDB->escape() escapes ' and " characters, and blocks characters that
     // could be used in a SQL injection attempt
     if (isset($tweet_object->retweeted_status)) {
         // This is a retweet
         // Use the original tweet's entities, they are more complete
         $entities = $tweet_object->retweeted_status->entities;
         $is_rt = 1;
     } else {
         $entities = $tweet_object->entities;
         $is_rt = 0;
     }
     $tweet_text = $oDB->escape($tweet_object->text);

     $connection->request('GET', $connection->url('1.1/direct_messages'), array('include_entities' => 'false', 'count' => 100));
 } else {
     --$max_id;
     $connection->request('GET', $connection->url('1.1/direct_messages'), array('include_entities' => 'false', 'count' => 100, 'max_id' => $max_id));
 }
 if ($connection->response['response'] == '[]') {
     break;
 }
 if ($connection->response['code'] != 200) {
     break;
 }
 $results = json_decode($connection->response['response']);
 foreach ($results as $dm) {
     $dm_id = $dm->id;
     $max_id = $dm_id;
     if ($db->in_table('dms', "dm_id={$dm_id}")) {
         continue;
     }
     $dm_text = $db->escape($dm->text);
     $created_at = $db->date($dm->created_at);
     $sender_user_id = $dm->sender->id;
     $recipient_user_id = $dm->recipient->id;
     $db->insert('dms', "dm_id={$dm_id},dm_text='{$dm_text}',created_at='{$created_at}',\r\n\t\t\tsender_user_id={$sender_user_id},recipient_user_id={$recipient_user_id},received=1");
     $screen_name = $db->escape($dm->sender->screen_name);
     $name = $db->escape($dm->sender->name);
     $location = $db->escape($dm->sender->location);
     $description = $db->escape($dm->sender->description);
     $url = $db->escape($dm->sender->url);
     $profile_image_url = $db->escape($dm->sender->profile_image_url);
     $created_at = $dm->sender->created_at;
     $friends_count = $dm->sender->friends_count;

     $connection->request('GET', $connection->url('1.1/search/tweets'), array('q' => $engagement_screen_name, 'result_type' => 'recent', 'lang' => 'en', 'include_entities' => 'true', 'count' => 100, 'max_id' => $max_id));
 }
 if ($connection->response['code'] != 200) {
     break;
 }
 $tweets_found = 0;
 $results = json_decode($connection->response['response']);
 $tweets = $results->statuses;
 foreach ($tweets as $tweet) {
     ++$tweets_found;
     $tweet_id = $tweet->id;
     $max_id = $tweet_id;
     if ($search_since_id == 0) {
         $search_since_id = $tweet_id;
     }
     if ($db->in_table('tweets', "tweet_id={$tweet_id}")) {
         continue;
     }
     $tweet_text = $db->escape($tweet->text);
     $tweet_created_at = $db->date($tweet->created_at);
     $retweet_count = $tweet->retweet_count;
     $user_id = $tweet->user->id;
     if (isset($tweet->retweeted_status)) {
         $is_rt = 1;
         $tweet_text = $db->escape($tweet->retweeted_status->text);
         $retweet_count = 0;
         $retweet_user_id = $tweet->retweeted_status->user->id;
         $entities = $tweet->retweeted_status->entities;
     } else {
         $is_rt = 0;
         $entities = $tweet->entities;

 while ($row = mysqli_fetch_assoc($result)) {
     $cache_id = $row['cache_id'];
     // Each JSON payload for a tweet from the API was stored in the database
     // by serializing it as text and saving it as base64 raw data
     $tweet_object = unserialize(base64_decode($row['raw_tweet']));
     // Delete cached copy of tweet
     $oDB->select("DELETE FROM json_cache WHERE cache_id = {$cache_id}");
     // Limit tweets tgo a single language,
     // such as 'en' for English
     if ($tweet_object->lang != 'es') {
         continue;
     }
     // The streaming API sometimes sends duplicates,
     // Test the tweet_id before inserting
     $tweet_id = $tweet_object->id_str;
     if ($oDB->in_table('tweets', 'tweet_id=' . $tweet_id)) {
         continue;
     }
     // Gather tweet data from the JSON object
     // $oDB->escape() escapes ' and " characters, and blocks characters that
     // could be used in a SQL injection attempt
     if (isset($tweet_object->retweeted_status)) {
         // This is a retweet
         // Use the original tweet's entities, they are more complete
         $entities = $tweet_object->retweeted_status->entities;
         $is_rt = 1;
     } else {
         $entities = $tweet_object->entities;
         $is_rt = 0;
     }
     $tweet_text = $oDB->escape($tweet_object->text);

require 'db_lib.php';
$db = new db();
// Get all feed URLs
$query = "SELECT name, url\r\n\tFROM autotweet_rss_feeds";
$results = $db->select($query);
while ($row = mysqli_fetch_assoc($results)) {
    $feed_name = trim($row['name']);
    $feed_url = $row['url'];
    // Load the feed as XML
    $feed_xml = simplexml_load_file($feed_url);
    $feed_items = $feed_xml->channel->item;
    // Loop through each feed item
    foreach ($feed_items as $item) {
        // Every RSS feed item has a unique ID
        $guid = $db->escape($item->guid);
        if (!$db->in_table('autotweet_rss_tweets', "guid='{$guid}'")) {
            // Assemble a tweet using the title and link (URL) elements
            // $default_tag is set in config.php
            $via_str = " (via {$feed_name})";
            $title_length = 140 - strlen(' ' . $item->link . $via_str . ' ' . $default_tag);
            if (strlen($item->title) > $title_length) {
                // Use an ellipse if tweet text is too long
                $title_str = substr($item->title, 0, $title_length - 1) . '…';
            } else {
                $title_str = $item->title;
            }
            $tweet_text = $title_str . ' ' . $item->link . $via_str . ' ' . $default_tag;
            $title = $db->escape($item->title);
            $link = $db->escape($item->link);
            $pub_date = $db->date($item->pubDate);
            // Save the feed item with the assembled tweet

$result = $db->select($query);
$row = mysqli_fetch_assoc($result);
$since_id = $row['since_id'];
while (true) {
    $connection->request('GET', $connection->url('1.1/statuses/user_timeline'), array('user_id' => $engagement_user_id, 'include_entities' => 'true', 'include_rts' => 'true', 'exclude_replies' => 'false', 'trim_user' => 'true', 'count' => 100, 'since_id' => $since_id));
    if ($connection->response['response'] == '[]') {
        break;
    }
    if ($connection->response['code'] != 200) {
        break;
    }
    $results = json_decode($connection->response['response']);
    foreach ($results as $tweet) {
        $tweet_id = $tweet->id;
        $since_id = $tweet_id;
        if ($db->in_table('tweets', "tweet_id={$tweet_id}")) {
            continue;
        }
        $tweet_text = $db->escape($tweet->text);
        $created_at = $db->date($tweet->created_at);
        $retweet_count = $tweet->retweet_count;
        $user_id = $tweet->user->id;
        if (isset($tweet->retweeted_status)) {
            $is_rt = 1;
            $tweet_text = $db->escape($tweet->retweeted_status->text);
            $retweet_count = 0;
            $retweet_user_id = $tweet->retweeted_status->user->id;
            $entities = $tweet->retweeted_status->entities;
        } else {
            $is_rt = 0;
            $entities = $tweet->entities;

    } else {
        $user = json_decode($connection->response['response']);
        $user_id = $user->id;
        $name = $db->escape($user->name);
        $screen_name = $db->escape($user->screen_name);
        $profile_image_url = $db->escape($user->profile_image_url);
        $location = $db->escape($user->location);
        $description = $db->escape($user->description);
        $url = $db->escape($user->url);
        $created_at = $db->date($user->created_at);
        $friends_count = $user->friends_count;
        $followers_count = $user->followers_count;
        $statuses_count = $user->statuses_count;
        $listed_count = $user->listed_count;
        $lang = $db->escape($user->lang);
        if (empty($user->protected)) {
            // If an account is not protected,
            // the $user->protected element is blank
            $protected = 0;
        } else {
            $protected = 1;
        }
        $last_tweet_date = $db->date($user->status->created_at);
        $field_values = "user_id={$user_id}, name='{$name}', screen_name='{$screen_name}', profile_image_url='{$profile_image_url}',\r\n\t\t\tlocation='{$location}', description='{$description}', url='{$url}', created_at='{$created_at}', friends_count={$friends_count},\r\n\t\t\tfollowers_count={$followers_count}, statuses_count={$statuses_count}, listed_count={$listed_count}, lang='{$lang}',\r\n\t\t\tprotected={$protected}, last_tweet_date='{$last_tweet_date}'";
        if (!$db->in_table('users', "user_id={$user_id}")) {
            $db->insert('users', $field_values);
        } else {
            $db->update('users', $field_values, "user_id={$user_id}");
        }
    }
}

function collect_account_profiles($query)
{
    require 'config.php';
    require 'oauth_lib.php';
    $connection = get_connection();
    require 'db_lib.php';
    $db = new db();
    // Run the SQL query passed to this script
    $results = $db->select($query);
    // Stop if there are no users to process
    if (mysqli_num_rows($results) == 0) {
        print 'All user accounts for this query have been collected';
        return;
    }
    while (true) {
        // Get 100 user_ids as comma delimited list
        $id_count = 0;
        $user_list = '';
        while (($row = mysqli_fetch_assoc($results)) && $id_count < 100) {
            $user_list .= $row['user_id'] . ',';
            ++$id_count;
        }
        // Exit if no additional users are found
        if ($id_count == 0) {
            return;
        }
        // Clip off the trailing comma after the last user_id
        $user_list = substr($user_list, 0, strlen($user_list) - 1);
        // Get the account profiles for these users
        $connection->request('GET', $connection->url('1.1/users/lookup'), array('user_id' => $user_list));
        $response_code = $connection->response['code'];
        if ($response_code != 200) {
            print "Error: {$response_code}\n";
            print $connection->response['response'];
            return;
        } else {
            // Convert the API JSON result into a PHP object
            $response_data = json_decode($connection->response['response']);
            // Extract each user's data
            foreach ($response_data as $user) {
                $user_id = $user->id;
                $name = $db->escape($user->name);
                $screen_name = $db->escape($user->screen_name);
                $profile_image_url = $db->escape($user->profile_image_url);
                $location = $db->escape($user->location);
                $description = $db->escape($user->description);
                $url = $db->escape($user->url);
                $created_at = $db->date($user->created_at);
                $friends_count = $user->friends_count;
                $followers_count = $user->followers_count;
                $statuses_count = $user->statuses_count;
                $listed_count = $user->listed_count;
                $lang = $db->escape($user->lang);
                if (empty($user->protected)) {
                    $protected = 0;
                    // Check to see if this user has ever tweeted
                    if (isset($user->status)) {
                        $last_tweet_date = $db->date($user->status->created_at);
                    } else {
                        // If there are no tweets,
                        // set the $last_tweet_date to show that
                        $last_tweet_date = '0000-00-00';
                    }
                } else {
                    $protected = 1;
                    // If the user is protected, no tweet is returned
                    $last_tweet_date = '0000-00-00';
                }
                $field_values = "user_id={$user_id}, name='{$name}', screen_name='{$screen_name}', profile_image_url='{$profile_image_url}',\r\n\t\t\t\t\tlocation='{$location}', description='{$description}', url='{$url}', created_at='{$created_at}', friends_count={$friends_count},\r\n\t\t\t\t\tfollowers_count={$followers_count}, statuses_count={$statuses_count}, listed_count={$listed_count}, lang='{$lang}',\r\n\t\t\t\t\tprotected={$protected}, suspended=0, last_tweet_date='{$last_tweet_date}'";
                if (!$db->in_table('users', "user_id={$user_id}")) {
                    $db->insert('users', $field_values);
                } else {
                    $db->update('users', $field_values, "user_id={$user_id}");
                }
            }
        }
    }
}

require 'config.php';
$engagement_user_id = 15131310;
require 'oauth_lib.php';
$connection = get_connection();
require 'db_lib.php';
$db = new db();
$connection->request('GET', $connection->url('1.1/users/show'), array('user_id' => $engagement_user_id));
$user = json_decode($connection->response['response']);
$friends_total = $user->friends_count;
$followers_total = $user->followers_count;
$statuses_total = $user->statuses_count;
$listed_total = $user->listed_count;
$previous_date = date('Y-m-d', strtotime('-2 day'));
$stat_date = date('Y-m-d', strtotime('-1 day'));
if ($db->in_table('daily_stats', "stat_date='{$stat_date}'")) {
    print "Stats already collected for {$stat_date}";
    exit;
}
$query = "SELECT friends_total, followers_total, statuses_total, listed_total\r\n\tFROM daily_stats\r\n\tWHERE stat_date = '{$previous_date}'";
$results = $db->select($query);
if ($previous_row = mysqli_fetch_assoc($results)) {
    $friends_added = $friends_total - $previous_row['friends_total'];
    $followers_added = $followers_total - $previous_row['followers_total'];
    $statuses_added = $statuses_total - $previous_row['statuses_total'];
    $listed_added = $listed_total - $previous_row['listed_total'];
} else {
    $friends_added = 0;
    $followers_added = 0;
    $statuses_added = 0;
    $listed_added = 0;

    $query = "UPDATE followers\r\n\t\tSET current = 0";
    $db->select($query);
}
// Loop through all followers
$cursor = '-1';
while ($cursor != 0) {
    $connection->request('GET', $connection->url('1.1/followers/ids'), array('user_id' => $engagement_user_id, 'cursor' => $cursor));
    $http_code = $connection->response['code'];
    if ($http_code == 200) {
        $data = json_decode($connection->response['response'], true);
        $ids = $data['ids'];
        $cursor = $data['next_cursor_str'];
        if (sizeof($ids)) {
            // Record the follower ids and add new followers to the log
            foreach ($ids as $user_id) {
                if ($db->in_table('followers', "user_id={$user_id}")) {
                    $db->update('followers', 'current=1', "user_id={$user_id}");
                } else {
                    $db->insert('followers', "user_id={$user_id},current=1");
                    if (!$first_collection) {
                        $db->insert('follow_log', "user_id={$user_id},event='follow'");
                    }
                }
            }
        } else {
            break;
        }
    } else {
        echo "not worked";
        exit;
    }