I am trying to put some JSON from the twitter REST API into a SQL database. My goal is to be able to look up account information from multiple accounts in one fell swoop.
I have been successful in echoing out the right data, but am not able to write it to the database.
I have a feeling there is something wrong with my array, specifically that each account I am looking up is in an array that does not share a common key, something like:
Array ( [twitterhandle being used as the key] => Array ( [screen_name] => value [name] => value [image] => value [following] => value [listed] => value [followers] => value [tweets] =>value ) [a different twitter handle being used a key]…
Her’s the code that I am using:
{
$result = mysqli_query($link,
"SELECT
twitterhandle FROM accounts");
while ($row = mysqli_fetch_assoc($result)) //build an array of the twitter handles in the "accounts" table in the database
{
$handles[]= $row['twitterhandle'];
}
foreach ($handles as $handle) //loop each handle as a twitter api Query grabbing the following JSON array
{
$url = file_get_contents('http://api.twitter.com/1/users/show.json?screen_name='.$handle.'&include_entities=true');
$json_a = json_decode($url, true);
$owners[$handle]= array('screen_name'=>$json_a['screen_name'],'name'=>$json_a['name'],'image'=>$json_a['profile_image_url'],
'following'=>$json_a['friends_count'],'listed'=>$json_a['listed_count'],'followers'=>$json_a['followers_count'],
'tweets'=>$json_a['statuses_count']); //try to echo to see if it works, should go and grab field 'owner' from JSON file
}
$sql = "UPDATE accounts SET
account_owner='it works' //just using this as to focus on getting the WHERE clause to work
WHERE twitterhandle='$?'"; //not sure what to put here. i have tried several things but nothing works. The best I can do is get the last set of results in my array.
if (!mysqli_query($link,$sql))
{
$error = 'Error adding twitter data. ' . mysqli_error($link);
include '/includes/error.html.php';
exit();
}
}
?>
Any help would be mucho appreciated.
thanks