Re: Dumping, Storing, and Displaying XML data with PHP and MySQL
Chad,
Thanks for the reply.
I understand the basics of the INSERT INTO mysql_query, but I was
asking how specifically I get the XML values inserted.
I think I figured it out somewhat though, but I'm still getting
errors.
--
//connect to database
(code)
//send data to database
mysql_query("INSERT INTO TABLE+NAME (time, body, favorited, name,
description, avatar, url, twitterid)
VALUES ('".$xml->status->created_at."', '".$xml->status->text."', '".
$xml->status->favorited."', '".$xml->status->user->name."', '".$xml-
>status->user->description."', '".$xml->status->user-
>profile_image_url."', '".$xml->status->user->url."', '".$xml->status-
>id."')",
mysql_real_escape_string($xml->status->created_at),
mysql_real_escape_string($xml->status->text),
mysql_real_escape_string($xml->status->favorited),
mysql_real_escape_string($xml->status->user->name),
mysql_real_escape_string($xml->status->user->description),
mysql_real_escape_string($xml->status->user->profile_image_url),
mysql_real_escape_string($xml->status->user->url),
mysql_real_escape_string($xml->status->id))
or die(mysql_error());
echo "Data Inserted!";
mysql_close ();
--
This returns:
--
Warning: Wrong parameter count for mysql_query() in /filepath/
filename.php on line 58
--
Line 58 is the one that reads:
--
mysql_real_escape_string($xml->status->id))
--
Any ideas?
Thanks
--Alex
On Jan 8, 10:15 am, "Chad Etzel" wrote:
> (1) You'll need to use something like mysql_query(...) to insert the
> data (seehttp://www.tizag.com/mysqlTutorial/mysqlinsert.phpfor info,
> or google around about it)
>
> (2) If you're on a unix/linux system, use a cron job
> (seehttp://www.aota.net/Script_Installation_Tips/cronhelp.php3or use
> google)
>
> If you're on windows, you could probably create some sort of Scheduled
> Task to call your script every so often.
>
> As for the frequency I guess that depends on how "old" you are
> willing to let your data get before pulling new data. 5 minutes? 10
> minutes? 2 hours? It's an authenticated request, so you are limited
> (currently) to 100 per hour, so choose accordingly.
>
> -Chad
>
> On Thu, Jan 8, 2009 at 2:28 AM, thegreatbund...@gmail.com
>
> wrote:
>
> > Hey all,
> > Like I said in my previous post, what I'm trying to do is essentially
> > create a site that lists my "friends_timeline." With that account, I'm
> > following a group of individuals in a particular industry.
>
> > Instead of hitting the Twitter server each time, I'm attempting to
> > store friends' tweets in a MySQL database and then display them to
> > visitors with PHP.
>
> > So, first off, I managed to authenticate and pull down the XML just
> > fine with curl.
>
> > As a test, I've stripped the data I want out and am printing it.
>
> >
> > //print XML data (temporary)
> > $xml = simplexml_load_string ($str);
> > foreach ($xml->status as $status) {
> >print $status->created_at . "\n"; //timestamp
> >print $status->text . "\n"; //body
> >print $status->favorited . "\n"; //favorited (returns true or
> > false)
> >print $status->user->name . "\n"; //user's real name
> >print $status->user->description . "\n"; //user's description
> >print $status->user->profile_image_url . "\n"; //location of
> > user's profile pic
> >print $status->user->url . "\n"; //user's homepage
> >print $status->id . "\n"; //tweet single id
> > }
> >
>
> > And that works just fine.
>
> > For the next ste
Dumping, Storing, and Displaying XML data with PHP and MySQL
Hey all,
Like I said in my previous post, what I'm trying to do is essentially
create a site that lists my "friends_timeline." With that account, I'm
following a group of individuals in a particular industry.
Instead of hitting the Twitter server each time, I'm attempting to
store friends' tweets in a MySQL database and then display them to
visitors with PHP.
So, first off, I managed to authenticate and pull down the XML just
fine with curl.
As a test, I've stripped the data I want out and am printing it.
//print XML data (temporary)
$xml = simplexml_load_string ($str);
foreach ($xml->status as $status) {
print $status->created_at . "\n"; //timestamp
print $status->text . "\n"; //body
print $status->favorited . "\n"; //favorited (returns true or
false)
print $status->user->name . "\n"; //user's real name
print $status->user->description . "\n"; //user's description
print $status->user->profile_image_url . "\n"; //location of
user's profile pic
print $status->user->url . "\n"; //user's homepage
print $status->id . "\n"; //tweet single id
}
And that works just fine.
For the next step, I've created a database and set up its table
structure to match the XML data.
Each line below represents a column in the table. For readability,
I've matched the XML data I've stripped from "friends_timeline" to
what I set up as its corresponding column in the database.
//auto increment id (not matched with XML) (int)
//created_at -> time (text)
//text -> body (text)
//favorited -> favorited (enum)
//name -> name (text)
//description -> description (text)
//profile_image_url -> avatar (text)
//url -> url (text)
//id -> twitterid (int)
Now, the part I'm stuck on: (1) I'm not sure how to get that XML data
into my database. Do I have to pass it through an array first? Do I
have to convert it to strings, or have I done that already with "$xml
= simplexml_load_string ($str)"?
The other issue I'm not sure about: (2) When a visitor comes to the
site, he'll see a "friends_timeline" from the data in the database,
not directly from twitter's servers. But how do I keep pulling data
from the XML feed and load it into the database automatically? And
what's a good interval to repeat
After the data starts populating my database, I don't think I'll have
too much trouble writing queries to display the content. But I'll
cross that bridge when I come to it. :D
I know this isn't explicitly a twitter API question, and I hope its
not out of place here. But any advice is appreciated. And sorry for
any improper terminology or poor explanations; as you can probably
tell, my web coding acumen isn't extensive.
Thanks in advance for the help!
--Alex
Re: Passing curl to XML
Worked perfectly, thanks Chad!
I'm sure I'll have another problem soon, but for now I'm rolling.
Thanks again! :D
On Jan 7, 8:05 pm, "Chad Etzel" wrote:
> I think the XML parser will choke on the headers that are being
> returned from your curl_exec.
>
> Try setting this instead:
> curl_setopt($ch, CURLOPT_HEADER, false);
>
> You'll also want to set this:
> curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
>
> so that the XML is actually stored in $str when curl_exec returns;
>
> Try that out,
> -Chad
>
> On Wed, Jan 7, 2009 at 6:29 PM, thegreatbund...@gmail.com
>
> wrote:
>
> > Hello everyone,
> > I'm working on my first Twitter-related project and am very excited to
> > be doing so.
>
> > What I'm trying to do is create a site that lists my
> > "friends_timeline." With that account, I'm following a group of
> > individuals in a particular industry.
>
> > Right now, I've been able to use curl to display the raw XML. Now I'm
> > struggling to display that data the way I need to.
>
> > Here's what I have written.
>
> >
> > > // set user/pswd
> > $username = '123';
> > $password = 'abc';
>
> > // create a new curl resource
> > $ch = curl_init();
>
> > // set URL and options
> > curl_setopt($ch, CURLOPT_URL, "http://twitter.com/statuses/
> > friends_timeline/ACCOUNT+NAME.xml");
> > curl_setopt($ch, CURLOPT_HEADER, 1);
> > curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");
>
> > // execute and pass to browser
> > $str = curl_exec($ch);
>
> > // close curl resource
> > curl_close($ch);
>
> > $xml = simplexml_load_string ($str);
> > foreach ($xml->status as $status) {
> > print $status->text . "\n";
> > }
>
> > ?>
>
> > Again, this outputs the last 20 entries in "friends_outline" as XML
> > just fine, but fails at the array. The following appears immediately
> > afterward.
>
> >
> > Warning: simplexml_load_string() [function.simplexml-load-string]:
> > Entity: line 1: parser error : Start tag expected, '<' not found in /
> > projects/tweets/test/curltest.php on line 20
>
> > Warning: simplexml_load_string() [function.simplexml-load-string]: 1
> > in /projects/tweets/test/curltest.php on line 20
>
> > Warning: simplexml_load_string() [function.simplexml-load-string]: ^
> > in /projects/tweets/test/curltest.php on line 20
>
> > Warning: Invalid argument supplied for foreach() in /projects/tweets/
> > test/curltest.php on line 21
>
> > I only have rudimentary PHP skills, but I'm a fairly quick study. Any
> > advice is appreciated!
>
> > Thanks in advance for the help!
>
> > --Alex
Passing curl to XML
Hello everyone,
I'm working on my first Twitter-related project and am very excited to
be doing so.
What I'm trying to do is create a site that lists my
"friends_timeline." With that account, I'm following a group of
individuals in a particular industry.
Right now, I've been able to use curl to display the raw XML. Now I'm
struggling to display that data the way I need to.
Here's what I have written.
http://twitter.com/statuses/
friends_timeline/ACCOUNT+NAME.xml");
curl_setopt($ch, CURLOPT_HEADER, 1);
curl_setopt($ch, CURLOPT_USERPWD, "$username:$password");
// execute and pass to browser
$str = curl_exec($ch);
// close curl resource
curl_close($ch);
$xml = simplexml_load_string ($str);
foreach ($xml->status as $status) {
print $status->text . "\n";
}
?>
Again, this outputs the last 20 entries in "friends_outline" as XML
just fine, but fails at the array. The following appears immediately
afterward.
Warning: simplexml_load_string() [function.simplexml-load-string]:
Entity: line 1: parser error : Start tag expected, '<' not found in /
projects/tweets/test/curltest.php on line 20
Warning: simplexml_load_string() [function.simplexml-load-string]: 1
in /projects/tweets/test/curltest.php on line 20
Warning: simplexml_load_string() [function.simplexml-load-string]: ^
in /projects/tweets/test/curltest.php on line 20
Warning: Invalid argument supplied for foreach() in /projects/tweets/
test/curltest.php on line 21
I only have rudimentary PHP skills, but I'm a fairly quick study. Any
advice is appreciated!
Thanks in advance for the help!
--Alex
