Querying Keyword Collection from Twitter Display

[Justin Kovac]

Hey all,

Pardon my ignorance, I'm not extremely familiar with how PHP queries are constructed.  I was hoping for some insights as to how I'd go about querying words in keyword collection tables (from Adam's February update) so I can return only tweets containing a specific keyword collection row entry for display purposes.  

$query = 'SELECT profile_image_url, created_at, screen_name, 
  name, tweet_text, tweet_id
  FROM tweets ';
// Query string of last=[tweet_id] means that this script was called by site.js
// when the More Tweets button was clicked
if (isset($_GET['last'])) {  
  $query .= 'WHERE tweet_id < "' . $_GET['last'] . '" ';
}
$query .= 'WHERE tweet_text == specific_keyword';
$query .= 'ORDER BY tweet_id DESC LIMIT ' . TWEET_DISPLAY_COUNT;
$result = $oDB->select($query);

 I know what I have listed above is nowhere close, but hopefully it helps construct the idea -- using Twitter_Display example to only return tweet data that matches the defined keyword.

I'm sorry this post falls into the 'have someone else do the work for you' motif but I'm open to suggestions as well as solutions if you've happened to work on this in the past.

Thanks!

[Justin Kovac]

I was able to get this working by using:

$query = 'SELECT profile_image_url, created_at, screen_name, 
  name, tweet_text, tweet_id

  FROM tweets WHERE tweet_text LIKE "%thekeyword%"';

but am currently getting an error attempting to view additional (next 30) tweets.  From there my main concern is that it's not the most efficient way to do this?  If it is or isn't, please let me know your thoughts.

Thanks,

[Alex Saidani]

Did you ever get this working?

Here's an example of what I did, in this case it was for a specific hashtag - works perfectly.

Ignoring the fact i'm using two separate tables, simply adjust it for your query. 

$query = 'SELECT tweets.profile_image_url, tweets.created_at, tweets.screen_name, tweets.name, tweets.tweet_text, tweets.tweet_id FROM tweets JOIN tweet_tags ON tweets.tweet_id=tweet_tags.tweet_id WHERE tweet_tags.tag = "'.$_SESSION["hashtag"].'"';

// Query string of last=[tweet_id] means that this script was called by site.js

// when the More Tweets button was clicked

if (isset($_GET['last'])) {  

  $query .= 'AND tweets.tweet_id < "' . $_GET['last'] . '" ';

}

$query .= 'ORDER BY tweets.tweet_id DESC LIMIT ' . TWEET_DISPLAY_COUNT;

$result = $oDB->select($query);