Alex Alex - 6 months ago 20
PHP Question

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 :(



Answer

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.