CODEWITHSUNDEEP
phpyql
Alex
Alex

Reputation: 68036

Include additonal field in select query

I'm using YQL to get a list of feeds, like this:

SELECT title, link, pubDate FROM rss
    WHERE url IN ('.implode("','", array_values($urls)).')

$urls contains the feed urls:

$urls = array(
  'delicious' => 'http://feeds.delicious.com/v2/rss/foo',
  'delicious' => 'http://feeds.delicious.com/v2/rss/anotherfoo',
  'lastfm'    => 'http://ws.audioscrobbler.com/1.0/user/foo/recenttracks.rss',
  ...
);

And I get an array of items with title / link / pubdate fields. Besides the "link" field, I don't get any info that might tell me from where the item comes from.

How can I include an additional field from array_keys($urls) that corresponds to the URL (value) ? Basically I need a field that tells me from what site does the item come from, like delicious, lastfm etc.

For lastfm I could check with strpos if the "last.fm" string is present in $item['url'], but with delicious links this is not possible :(

Upvotes: 5

Views: 158

Answers (3)

hakre
hakre

Reputation: 197785

And I get an array of items with title / link / pubdate fields. Besides the "link" field, I don't get any info that might tell me from where the item comes from.

To get the info from where each item comes from, add the source field:

SELECT title, link, pubDate, source FROM rss
    ...

The result will then contain a source element in each entry, which has a url attribute which contains the url the item has been fetched from:

<results>
    <item>
        <title>Git Immersion - Brought to you by EdgeCase</title>
        <pubDate>Wed, 21 Dec 2011 22:15:44 +0000</pubDate>
        <link>http://gitimmersion.com/</link>
        <source url="http://feeds.delicious.com/v2/rss/geek">geek's links</source>
    </item>

The YQL Demo.

You can look for all fields by using *, this is how I found out about the source field:

SELECT * FROM rss
    WHERE url IN ('http://feeds.delicious.com/v2/rss/geek', 'http://feeds.delicious.com/v2/rss/foo')

YQL Query Demo

I think this should answer your question. To reduce the amount of data transferred, you can also only select the url attribute of source like so:

SELECT title, link, pubDate, source.url FROM rss
    ...

See as well Using YQL to load and convert RSS feeds really, really fast and How to use YQL to merge 2 RSS feeds sorted by pubDate?.

The following is an older comment on YQL syntax:

Please take note that implode("','", array_values($urls)) will leave your YQL in a invalid syntax:

... IN( urlA','urlB','urlC )

You see the missing single quotes at the beginning and end? Add them:

"'".implode("','", $urls)."'"

and you should be fine. At least you should have one error less.

Upvotes: 1

Corubba
Corubba

Reputation: 2243

Since YQL is not SQL, and only use the syntax of SQL for its queries, there is no chance to SELECT the keyword (in your case url) used in the WHERE clause. SELECT can only access fields of the xml file, given with the url keyword. YQL will parse it for you and return the wanted fields. And cause there are many different rss standards, and not every standard force the feed to contain its own url, its not possible to select a field in the feed that will return the url in every case.

I think, your only chance will be to iterate through the $urls array and do one query per feed. Only this way you can keep the relation between feed-url and the items of this feed.

Upvotes: 1

Sparky
Sparky

Reputation: 15085

Can't you simply add URL to your field list? Or are you looking for a function to parse the URL?

SELECT title,link,pub date,URL FROM rss

Am I missing why the above won't work?

Upvotes: 1

Related Questions