Building web applications with YQL and PHP, Part 1

Use PHP and YQL to retrieve and combine data from multiple web services

The Yahoo! Query Language (YQL) provides a unified interface to multiple third-party web services, allowing you to search, add, update, and delete data through a simple, SQL-like syntax. Combine powerful XML processing tools in PHP with YQL and you can easily add data from online services to a web application quickly, efficiently, and without wading through pages of API documentation.

Share:

Vikram Vaswani, Founder, Melonfire

Photo of Vikram VaswaniVikram Vaswani is the founder and CEO of Melonfire, a consulting services firm with special expertise in open-source tools and technologies. He is also the author of the books Zend Framework: A Beginners Guide and PHP: A Beginners Guide.



30 November 2010 (First published 16 November 2010)

Also available in Chinese Japanese Portuguese

30 Nov 2010 - Added link to Part 1 in Introduction and Conclusion sections. Under "Learn" in Resources, added resource item for Part 2.

Introduction

Today, almost every popular web site has a developer API, allowing web application programmers to access and manipulate data using standard frameworks such as REST and SOAP. For example, Google offers the Google Data APIs for access to Google services such as Google Buzz, Google Docs, and Google Calendar; Twitter has a REST API that allows users to search and post tweets; and Facebook offers the Graph API to manage user profiles and connections.

Frequently used acronyms

  • API: Application program interface
  • DOM: Document Object Model
  • ECS: E-Commerce Service
  • HTML: HyperText Markup Language
  • ISBN: International Standard Book Number
  • JSON: JavaScript Object Notation
  • RDBMS: Relational database management system
  • REST: REpresentational State Transfer
  • RSS: Really Simple Syndication
  • SOAP: Simple Object Access Protocol
  • SQL: Structured Query Language
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

While these APIs certainly make your life more exciting and open the door to a variety of creative new applications, some challenges remain. For example, a key implementation challenge is the lack of uniformity between the APIs of different sites. Some sites use REST while others use SOAP; some use Atom to encode response data, but others use RSS, and still others use plain XML or JSON. As a result, every time you decide to integrate a new web service into an application, first you must do a fair amount of reading and then a fair amount of testing to fully grasp the mechanics of the service API before you begin integration.

To solve this very problem, the developers at Yahoo! decided to invent Yahoo! Query Language, better known by the acronym YQL. YQL offers a unified, SQL-like interface to multiple web service APIs, significantly simplifying the task of integrating third-party data into a web application. In this two-part article, I introduce you to YQL, illustrating how you can use it in combination with my favourite language, PHP, to build sophisticated web applications.


Understanding YQL

If you're familiar with SQL, YQL will immediately look familiar to you. Very simply, YQL treats individual web services as though they were data tables, allowing developers to formulate SQL-like queries to extract information from them. The YQL service takes care of parsing the query string, executing it on the remote service, and returning the results in standard XML or JSON format. The query string itself is passed to the YQL service using REST, as a GET request.

To better understand this approach, consider a simple example. Suppose you want to search Twitter for all posts containing the term "master chief." In a non-YQL world, you'd usually do this through the Twitter Search API, by formulating a request like this:

http://search.twitter.com/search.atom?q=master%20chief&lang=en

In return, the Twitter Search API sends back an Atom feed of results, such as the one in Figure 1.

Figure 1. An Atom feed of search results from the Twitter Search API
Screen capture of an Atom feed of search results for 'master chief' from the Twitter Search API

With YQL, you can simplify things by accessing the Twitter data table with a YQL query, as follows:

SELECT * FROM twitter.search WHERE q='master chief'

Notice that this is almost exactly like a standard SQL query: The SELECT keyword indicates this is a data retrieval operation, the FROM keyword specifies the data source, and the WHERE clause specifies the filter. After you submit this query, YQL returns a standard XML or JSON document with the results of your query from Twitter, such as the one in Figure 2.

Figure 2. The results of a YQL query on the Twitter data table
Screen capture of the XML results of a YQL query on the Twitter data table

Now, skip a few days ahead and suppose that you decide to add some more data to your web application. Specifically, assume that you'd like to use the geocoding information that accompanies Twitter search results to also display a map of the geographical area from which each post originated. Also assume that you'd like to accompany the Twitter search results with a list of news headlines about the search term "master chief."

In a non-YQL world, you'd need to spend some time reading the API documentation for Yahoo! Maps and Google News before you can accomplish this task. With YQL, it's as simple as adding a couple of queries:

SELECT * FROM google.news WHERE q="master chief"

SELECT * FROM maps.map WHERE latitude="XX" AND longitude="YY"

Figure 3 and Figure 4 illustrate snapshots of the query results.

Figure 3. The results of a YQL query on the Google News data table
Screen capture of the results of a YQL query on the Google News data table
Figure 4. The results of a YQL query on the Yahoo Maps data table
Screen capture of the results of a YQL query on the Yahoo Maps data table

The query results in Figures 3 and 4 clearly indicate that YQL's greatest benefit lies in presenting a unified interface to third-party web services. By permitting you to query third-party services using commonly understood SQL syntax, YQL saves you time and effort and makes it easier to integrate data from disparate sources into a web application. The ability to select either XML or JSON as the output format is also useful, permitting you a degree of flexibility and allowing you to use either server-side programming (PHP Perl, for example) or client-side tools (jQuery or mooTools, for instance) to access and manipulate the result data.


Using the YQL Console

The easiest way to get started with YQL is through the YQL Console, an interactive online tool that allows you to formulate and test YQL queries on the fly. The YQL Console is hosted on the Yahoo! Developer Network and comes with diagnostics tools, example queries, and a list of available tables.

To see how it works, browse to the YQL Console and enter the following query into it, to get a list of music albums popular right now:

SELECT * FROM music.release.popular

When you submit the form, the query string is submitted to the YQL service as a URL-encoded query string. The YQL service then looks up the table definition, performs the query, and returns the results. Figure 5 illustrates what the output looks like in the YQL Console.

Figure 5. Query output in the YQL interactive console
Screen capture of query output in the YQL interactive console

Using YQL with PHP

As Figure 5 illustrates, YQL can return data using either XML or JSON. When building PHP web applications, XML is usually more convenient, as PHP comes with built-in XML processing extensions (SimpleXML, DOM, or XMLReader) that can be used to quickly parse the result document. With this in mind, Listing 1 attempts to perform the same query with PHP and SimpleXML:

Listing 1. Processing YQL results with SimpleXML
<?php
// execute query
// get list of 15 most popular music releases
// retrieve result as SimpleXML object
$xml = simplexml_load_file('
  http://query.yahooapis.com/v1/public/yql?q=
  SELECT * FROM music.release.popular
');

// iterate over query result set
echo '<h2>Popular Music</h2>';
$results = $xml->results;
foreach ($results->Release as $r) {
  echo '<p>';
  echo '<a href="' . $r['url'] . '">' . $r['title'] . 
    '</a> (' . $r['releaseYear'] . ') - ';  
  echo '<a href="' . $r->Artist['url'] . '">' . $r->Artist['name'] . 
    '</a> <br/>'; 
  echo 'Current chart position: ' . $r->ItemInfo->ChartPosition['this'] . 
    ' / Last chart position: ' . $r->ItemInfo->ChartPosition['last']; 
  echo '</p>';
}  
?>

Listing 1 begins by formulating a query to the YQL web service, passing it the same URL-encoded query string used in the previous example. This request is made through the simplexml_load_file() function, thereby ensuring that the resulting XML document is automatically parsed and converted into a SimpleXML object. The remainder of the script then iterates over the <results> node of the XML document, printing the title, link, artist, and current chart position for each album.

Figure 6 illustrates the result.

Figure 6. A list of popular music releases, retrieved through YQL
Screen capture of a list of popular music releases, retrieved through YQL

If you use the Zend Framework, you can alternatively access the YQL Web service using the Zend_Rest_Client component. Listing 2, which produces output equivalent to that of Listing 1, illustrates.

Listing 2. Processing YQL results with the Zend Framework
<?php
// set up Zend auto-loader
// load Zend REST client classes
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Rest_Client');

// execute YQL query
// get list of most popular music releases
try {
  $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
  $client->q('SELECT * FROM music.release.popular');
  $result = $client->get();
} catch (Zend_Rest_Client_Exception $e) {
    echo "Client error: " . $e->getResponse();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

// iterate over query result set
echo '<h2>Popular Music</h2>';
foreach ($result->Release as $r) {
  echo '<p>';
  echo '<a href="' . $r['url'] . '">' . $r['title'] . 
    '</a> (' . $r['releaseYear'] . ') - ';  
  echo '<a href="' . $r->Artist['url'] . '">' . $r->Artist['name'] . 
    '</a> <br/>'; 
  echo 'Current chart position: ' . $r->ItemInfo->ChartPosition['this'] . 
    ' / Last chart position: ' . $r->ItemInfo->ChartPosition['last']; 
  echo '</p>';
} 
?>

The Zend_Rest_Client component of the Zend Framework is designed specifically for developers trying to integrate PHP applications with REST-based web services. With this client, you can perform GET, POST, PUT, and DELETE responses to a REST service endpoint. REST responses are returned as instances of Zend_Rest_Client_Response objects, making it easy to access individual response properties.

Listing 2 first loads the Zend class libraries, and then initializes an instance of the Zend_Rest_Client class. This client is used to initialize an unauthenticated GET request for the YQL web service endpoint, as was done earlier in Listing 1. The returned XML file is then parsed and converted into a Zend_Rest_Client_Response object, which can then be processed using a standard foreach() loop. Notice that when using the Zend_Rest_Client object, it is not necessary to URL-encode the YQL query, as the component takes care of that step internally.


Filtering and sorting query results

As with regular SELECT queries, YQL allows you to filter query results with a WHERE clause, specify required fields, and sort results by one or more fields. To illustrate, consider the following query to the Flickr API for a list of places matching the search term "england":

SELECT * FROM flickr.places WHERE query="england"

Figure 7 illustrates the YQL response to this query.

Figure 7. A list of search results for "england", retrieved from Flickr through YQL
Screen capture of a list of search results for 'england', retrieved from Flickr through YQL

Listing 3 illustrates this query in practice.

Listing 3. Filtering YQL results with a WHERE clause
<?php
// set up Zend auto-loader
// load Zend REST client classes
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Rest_Client');

// execute YQL query
// get list of Flickr places matching search term
try {
  $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
  $client->q('SELECT * FROM flickr.places WHERE query="england"');
  $result = $client->get();
} catch (Zend_Rest_Client_Exception $e) {
    echo "Client error: " . $e->getResponse();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

// iterate over query result set
echo '<h2>Search Results</h2>';
echo '<table border=1>';
echo '<tr><th>Name</th><th>Latitude</th><th>
Longitude</th></tr>';
foreach ($result->place as $p) {
  echo '<tr>';
  echo '<td>' . $p . '</td>';  
  echo '<td>' . $p['latitude'] . '</td>';  
  echo '<td>' . $p['longitude'] . '</td>';  
  echo '</tr>';
}  
echo '</table>';
?>

Figure 8 displays the formatted output of Listing 3.

Figure 8. A formatted list of search results for "england", retrieved from Flickr through YQL
Screen capture of a formatted list of search results for 'england', retrieved from Flickr through YQL

You can add additional filter criteria with AND and OR operators. Consider this revision of the previous query, which further filters results by timezone:

SELECT * FROM flickr.places WHERE query="england" AND timezone LIKE "%europe%"

You can restrict the output of the query to specific fields only. This approach is useful when you need only a small subset of the data returned by the web service, and it also reduces the size of the response packet. Here's a revision of the previous example, which retrieves only the latitude, longitude, and name of each record:

SELECT latitude, longitude, content FROM flickr.places WHERE query="england"

YQL also supports various utility functions that can be used to sort, count, and eliminate duplicates from the result set. These functions are usually placed after the YQL query string, separated with a pipe (|) character. Consider the following query, which sorts results by latitude:

SELECT latitude, longitude, content FROM flickr.places 
  WHERE query="england" | sort (field="latitude")

You can also apply the unique filter to strip duplicates from the result, specifying the field that should be checked:

SELECT * FROM flickr.places 
  WHERE query="england" | unique (field="timezone")

Listing 4 puts all of this together to build an interactive query tool that asks the user to input a location name and returns a sortable list of results.

Listing 4. Searching for place names
<html>
  <head></head>
  <body>  
    <form method="post" action="<?php echo htmlentities
    ($_SERVER['PHP_SELF']); ?>">
    Search term:
    <input type="text" name="q" />
    Sort results by:
    <select name="s">
      <option value="timezone">Time zone</option>
      <option value="latitude">Latitude</option>
      <option value="longitude">Longitude</option>
    </select>
    <input type="submit" name="submit" value="Search" />    
    </form>
    <?php
    // check if form is submitted
    // perform necessary validation (omitted for brevity)
    if (isset($_POST['submit'])) {
      // set up Zend auto-loader
      // load Zend REST client classes
      require_once 'Zend/Loader.php';
      Zend_Loader::loadClass('Zend_Rest_Client');
      
      // execute YQL query
      // get list of Flickr places matching search term
      // sort by requested field
      try {
        $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
        $client->q(
          'SELECT latitude, longitude, timezone, content FROM flickr.places 
            WHERE query="' . $_POST['q'] .'" | sort(field="' . $_POST['s'] . '")');
        $result = $client->get();
      } catch (Zend_Rest_Client_Exception $e) {
          echo "Client error: " . $e->getResponse();
      } catch (Exception $e) {
          echo "Error: " . $e->getMessage();
      }
      
      // iterate over query result set
      echo '<h2>Search Results</h2>';
      echo '<table border=1>';
      echo '<tr><th>Name</th><th>Timezone</th>
        <th>Latitude</th><th>Longitude</th></tr>';
      foreach ($result->place as $p) {
        echo '<tr>';
        echo '<td>' . $p . '</td>';  
        echo '<td>' . $p['timezone'] . '</td>';  
        echo '<td>' . $p['latitude'] . '</td>';  
        echo '<td>' . $p['longitude'] . '</td>';  
        echo '</tr>';
      } 
      echo '</table>';
    }
    ?>
  </body>
</html>

Figure 9 illustrates Listing 4 in action.

Figure 9. An interactive search and sort filter for place names
Screen capture of an interactive search and sort filter for place names, based on code in Listing 4

For a complete list of YQL functions, look in the YQL Guide (see Resources for a link).


An example application: Weather forecasts by location

Let's now take all that you've learned so far and build a simple YQL-backed application using PHP. Listing 5 asks the user to enter his or her location into a form; it then connects to the Yahoo! Weather service through the weather.bylocation table and queries for a local weather forecast for that location. Take a look at the code in Listing 5.

Listing 5. Retrieving weather forecasts by location
<html>
  <head></head>
  <body>  
    <form method="post" action="<?php echo htmlentities
    ($_SERVER['PHP_SELF']); ?>">
    Enter city name:
    <input type="text" name="city" />
    <input type="submit" name="submit" value="Get forecast" />    
    </form>
    <?php
    // check if form is submitted
    // perform necessary validation (omitted for brevity)
    if (isset($_POST['submit'])) {
      // set up Zend auto-loader
      // load Zend REST client classes
      require_once 'Zend/Loader.php';
      Zend_Loader::loadClass('Zend_Rest_Client');

      // execute YQL query
      // get Yahoo! Weather forecast for selected zip code
      try {
        $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
        $client->env('store://datatables.org/alltableswithkeys');  
        $client->q('SELECT * FROM weather.bylocation 
          WHERE location="' . $_POST['city'] . '"');
        $result = $client->get();
      } catch (Zend_Rest_Client_Exception $e) {
          echo "Client error: " . $e->getResponse();
      } catch (Exception $e) {
          echo "Error: " . $e->getMessage();
      }

      // process and print query results
      $data = $result->results->weather->rss->channel->item;
      echo '<h2>' . $data->title . '</h2>';
      echo $data->pubDate . '<br/>';      
      echo $data->description;
    }
    ?>
  </body>
</html>

After the web form is submitted with a city name, Listing 5 uses the Zend_Rest_Client component to send a YQL query to the weather service. The query uses a WHERE clause to filter the contents of the weather.bylocation table and retrieve a weather forecast for the specified zip code. The query result is formatted and presented as an HTML page. Figure 10 illustrates the result.

Figure 10. Weather forecasts by city, retrieved through YQL
Screen capture of weather forecasts by city, retrieved through YQL, shows Mumbai, India for 23 Jul 2010

Notice also that Listing 5 adds a new variable to the query string, env. This variable is necessary because the weather.bylocation data table is a so-called community table, maintained by the community and not by Yahoo! itself. As a result, the YQL service does not automatically know where this table definition is located. The env variable is used to specify the location of the table definition files. In this case, the location is the Community Open Data Tables for YQL web site, which serves as a repository for all YQL community tables (see Resources for a link).


Using nested YQL queries

The greatest value of a traditional RDBMS comes from its ability to join individual tables together to create different views of the data contained therein. And one of the coolest things about YQL is that it lets you do the same thing with web services. Using YQL queries, it is possible to combine data from multiple web services to present new and useful views of third-party data.

While the possible applications of this feature are limited only by your creativity, a simple example can help make clear the power of this feature. Consider Listing 6, which asks the user to enter a country name and then queries the upcoming web service to list upcoming events in that country.

Listing 6. Searching for events by country
<html>
  <head></head>
  <body>  
    <form method="post" 
      action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
    Search for events in:
    <input type="text" name="country" />
    <input type="submit" name="submit" value="Search" />    
    </form>
    <?php
    // check if form is submitted
    // perform necessary validation (omitted for brevity)
    if (isset($_POST['submit'])) {
      // set up Zend auto-loader
      // load Zend REST client classes
      require_once 'Zend/Loader.php';
      Zend_Loader::loadClass('Zend_Rest_Client');

      // execute YQL query
      // get list of events in requested country
      try {
        $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
        $client->q(
          "SELECT * FROM upcoming.events 
            WHERE country_id IN 
              (SELECT id FROM upcoming.country 
                WHERE name = '" . ucfirst($_POST['country']) . "')");    
        $result = $client->get();
      } catch (Zend_Rest_Client_Exception $e) {
          echo "Client error: " . $e->getResponse();
      } catch (Exception $e) {
          echo "Error: " . $e->getMessage();
      }

      // iterate over query result set
      echo '<h2>Events in ' . ucfirst($_POST['country']) . '</h2>';
      foreach ($result->event as $e) {
        echo '<p>';
        echo '<a href="' . $e['url'] . '">' . $e['name'] . 
          '</a> <br/>';
        echo 'Starts: ' . date('d M Y', strtotime($e['start_date'])). '<br/>'; 
        echo !empty($e['end_date']) ?  'Ends: ' . 
          date('d M Y', strtotime($e['end_date'])) . '<br/>' : '';
        echo 'Location: ' . $e['venue_name'] . '<br/>';
        echo 'Address: ' . 
          sprintf('%s, %s', $e['venue_address'], $e['venue_city']) . '<br/>';
        echo $e['description'] . '<br/>';
        echo '</p>';
      } 
}
?>  
  </body>
</html>

Listing 6 makes use of two nested YQL queries. The inner query accesses the "upcoming.country" data table to retrieve the numeric ID corresponding to the country string entered by the user. This numeric ID is then passed to the outer query, to retrieve a list of events linked to that country ID.

Figure 11 illustrates an example of the result.

Figure 11. A list of upcoming events, searchable by country
Screen capture of a list of upcoming events, searchable by country, shows events in Hyderabad, India

It's important to note that technically, this is not a join, but a subquery. As of this writing, YQL doesn't allow you to link tables through common keys (a join), but only permits you to use the results of one query inside another (a subquery).


An example application: Bestseller lists and prices

As you might imagine, the ability to combine data from multiple web interfaces using simple SQL-like syntax is music to the ears of mashup developers. And so, let's consider another, slightly more complicated application: combining data from The New York Times bestseller lists with price information from Amazon.com's database to present a composite view of popular books, their prices, and their popularity.

The first step in building this application is to retrieve the current list of New York Times bestsellers. YQL makes this information available through its nyt.bestsellers table, but you need a valid API key to run queries on this table. Assuming you have this key (look in Resources for a link that explains how to get it), you can retrieve the current list of hardcover fiction bestsellers for the week of July 21, 2010 using a query like this:

SELECT * FROM nyt.bestsellers WHERE listname='Hardcover Fiction' 
AND date='2010-07-21' AND apikey='NYT-API-KEY'

Figure 12 illustrates what the output of this query looks like.

Figure 12. A list of New York Times bestsellers, retrieved through YQL
Screen capture of a list of New York Times bestsellers, retrieved through YQL

Notice that each record in the result lists the book's unique ISBN number. This is critical information for the second part of the application, which needs to look up the price of the book on Amazon.com.

Pulling pricing data from Amazon.com might seem like an onerous task at first glance, but, in reality, it couldn't be simpler. Amazon.com exposes its product database to third-party developers through its Amazon ECS web service (see Resources for a link and information on how to obtain an API key). And YQL includes a data table for Amazon ECS, making it possible to retrieve pricing information for a particular ISBN with a query like this one:

SELECT DetailPageURL, ItemAttributes, SalesRank, MediumImage FROM amazon.ecs 
WHERE AWSAccessKeyId='AWS-KEY' AND secret='AWS-SECRET-KEY' 
AND ResponseGroup='Medium' AND Operation = 'ItemLookup' 
AND ItemAttributes.Binding = 'Hardcover' AND ItemId = '1400065453'

Figure 13 illustrates the output of this query.

Figure 13. Product data from Amazon.com, retrieved through YQL
Screen capture of product data from Amazon.com, retrieved through YQL

It should be clear that the two preceding queries can be easily combined to produce the necessary information. Listing 7 has the complete script. Remember to replace the dummy API keys in the queries with your own before trying it out.

Listing 7. Retrieving bestseller lists and prices
<html>
  <head>
    <style type="text/css">
    .item {
      float: left;
      width: 400px;      
      padding:10px;   
    }
    .cover {
      float:left; 
      padding: 5px;
      border: solid 1px black;      
    }
    .data {
      margin-left: 150px;
      font-weight: bolder;  
    }
    </style>
  </head>
  <body>
<?php
// set up Zend auto-loader
// load Zend REST client classes
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Rest_Client');

// execute YQL query
// get list of NYT bestsellers
// retrieve image and price from Amazon.com
try {
  $client = new Zend_Rest_Client('http://query.yahooapis.com/v1/public/yql');
  $client->env('store://datatables.org/alltableswithkeys');  
  $client->q(
    "SELECT DetailPageURL, ItemAttributes, SalesRank, MediumImage FROM amazon.ecs 
      WHERE AWSAccessKeyId='AWS-KEY' 
      AND secret='AWS-SECRET-KEY' 
      AND ResponseGroup='Medium' 
      AND Operation = 'ItemLookup' 
      AND ItemAttributes.Binding = 'Hardcover' 
      AND ItemId IN 
        (SELECT isbns.isbn.isbn10 FROM nyt.bestsellers 
          WHERE apikey='NYT-KEY' 
          AND listname='Hardcover Fiction' 
          AND date='2010-07-20') 
    | unique(field='ItemAttributes.Title')");    
  $result = $client->get();
} catch (Zend_Rest_Client_Exception $e) {
    echo "Client error: " . $e->getResponse();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

// iterate over query result set
echo '<h2>New York Times - Hardcover Fiction Bestsellers</h2>';
$count = 1;
foreach ($result->results->Item as $r) {
  echo '<div class="item">';
  echo '<img class="cover" src="' . $r->MediumImage->URL . '"/>';
  echo '<div class="data">';
  echo $count . '. ';
  echo '<a href="' . $r->DetailPageURL . '">' . 
    $r->ItemAttributes->Title . '</a>';
  echo ' - '. $r->ItemAttributes->Author . '<br/>';
  echo 'Amazon.com Sales Rank: ' . $r->SalesRank . '<br/>';
  echo 'Amazon.com Price: ' . 
    $r->ItemAttributes->ListPrice->FormattedPrice . '';
  echo '</div></div>';
  $count++;
}  
?>  
  </body>
</html>

Listing 7 combines the two preceding queries, using the ISBN key as the common denominator, to produce a composite result containing book titles, authors, images, prices, and sales ranks. This result is then parsed and processed to generate an HTML page (Figure 14).

Figure 14. A list of bestselling books, combined with product data through YQL
Screen capture of a list of bestselling books, combined with product data through YQL

Notice that in Listing 7, the inner query specifically retrieves only the ISBN-10 numbers of the books on the bestseller lists. Because this information is a few nodes down in the tree, dot notation is used to indicate the exact hierarchical position of the required nodes to the query parser. A similar technique is used when applying the unique filter to the outer query, with dot notation used to indicate the field by which results are to be filtered.


Conclusion

As these examples illustrate, YQL is a powerful tool for web application developers: It presents a unified interface to different web services, enabling a standard SQL-like query mechanism that speeds up development and requires less information about the target service. You can filter YQL results with WHERE clauses, and combine or "mash up" data from multiple services through the use of sub-selects. Add PHP, with its powerful XML processing tools, to the equation, and you've got a combination that even the most jaded web developer can enjoy experimenting with!

The examples in this article have merely touched the tip of the iceberg. You can do a lot more with YQL, including paging result sets; extracting data from RSS, Atom, XML, and HTML documents; and adding and modifying data with CREATE and UPDATE queries. I'll cover more in the second part of this article, so make sure you come back soon.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Open source
ArticleID=580055
ArticleTitle=Building web applications with YQL and PHP, Part 1
publish-date=11302010