Re: Dumping, Storing, and Displaying XML data with PHP and MySQL
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
On Fri, Jan 9, 2009 at 3:03 PM, paradigm.webmas...@gmail.com
wrote:
>
> 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 wrote:
>> 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());
>>
>> echo "Data Inserted!";
>>
>> mysql_close ();
>> ------
>
Re: Dumping, Storing, and Displaying XML data with PHP and MySQL
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 wrote:
> 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());
>
> echo "Data Inserted!";
>
> mysql_close ();
> ------
Re: Dumping, Storing, and Displaying XML data with PHP and MySQL
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());
echo "Data Inserted!";
mysql_close ();
--
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 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
Re: Dumping, Storing, and Displaying XML data with PHP and MySQL
(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
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 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
>
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
