Dumping, Storing, and Displaying XML data with PHP and MySQL

[thegrea...@gmail.com]

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 . "<br />\n"; //timestamp
print $status->text . "<br />\n"; //body
print $status->favorited . "<br />\n"; //favorited (returns true or
false)
print $status->user->name . "<br />\n"; //user's real name
print $status->user->description . "<br />\n"; //user's description
print $status->user->profile_image_url . "<br />\n"; //location of
user's profile pic
print $status->user->url . "<br />\n"; //user's homepage
print $status->id . "<br /><br />\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

[Chad Etzel]

(1) You'll need to use something like mysql_query(...) to insert the
data (see http://www.tizag.com/mysqlTutorial/mysqlinsert.php for info,
or google around about it)

(2) If you're on a unix/linux system, use a cron job (see
http://www.aota.net/Script_Installation_Tips/cronhelp.php3 or 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

[thegrea...@gmail.com]

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" <jazzyc...@gmail.com> 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

[fastest963]

Do:

----------------------------------------------------------------------------------
//connect to database
(code)

$query = sprintf("INSERT INTO TABLE+NAME (`time`, `body`, `favorited`,
`name`,
`description`, `avatar`, `url`, `twitterid`) VALUES ('%s', '%s', '%s',
'%s', '%s', '%s', '%s', '%s')",

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));
mysql_query($query) or die(mysql_error());

[paradigm....@gmail.com]

This is exactly what I need. I was trying to set this up last night
with no success. I was using variables, but this wouldn't insert into
my database. So I am guessing this is the correct way to get a
SimpleXML object into mysql. How would I then generate this into an
html table from the database? Sorry if this is a silly question, I
have just really been struggling with this.

On Jan 9, 8:25 am, fastest963 <fastest...@gmail.com> wrote:
> Do:
> ---------------------------------------------------------------------------­-------

> ---------------------------------------------------------------------------­-------

[Chad Etzel]

http://www.phpbuilder.com/board/archive/index.php/t-10207538.html
should help get you started.

At last count, there are eleventy-billion tutorials online how to do this.

Look up mysql_fetch_assoc and mysql_result on php.net and google
around for things like "php mysql data html table tutorial"

Sorry to be somewhat short, but we're deviating from twitter API stuff here....

-Chad