How to get more that 10 results for a query using YQL?

Question

I am trying to use YQL in php to get product information from Amazon using the amazon.prodlist table and Amazon Product Advertising API.

The query that I used:

select * from amazon.prodlist where Title='harry potter' and SearchIndex='Books' and ResponseGroup='Images,ItemAttributes'

It returns only 10 results. How can I get it to display more that 10 results on the same page? Also, without pagination.

The full PHP code:

<?php
$BASE_URL = "https://query.yahooapis.com/v1/public/yql";

$key="my_key";
$secret="my_secret";
$title="harry potter";
$sindex="Books";
$rgroup="Images,ItemAttributes";
$events="";
     

// Form YQL query and build URI to YQL Web service
$yql_query = "use 'http://www.datatables.org/amazon/amazon.ecs.xml' as amazon.prodlist;
set AWSAccessKeyId='$key' on amazon.prodlist;
set secret='$secret' on amazon.prodlist; 
select * from amazon.prodlist where Title='$title' and SearchIndex='$sindex' and ResponseGroup='$rgroup' ";

$yql_query_url = $BASE_URL . "?q=" . urlencode($yql_query) . "&format=json";

// Make call with cURL
$session = curl_init($yql_query_url);
curl_setopt($session, CURLOPT_RETURNTRANSFER,true);
$json = curl_exec($session);
// Convert JSON to PHP object 
$phpObj =  json_decode($json);

// Confirm that results were returned before parsing
if(!is_null($phpObj->query->results)){
  // Parse results and extract data to display
  foreach($phpObj->query->results->Item as $event){
    $events .= "<div><h2>" . $event->ItemAttributes->Title . " by " . $event->ItemAttributes->Author . "</h2></div>";
}
}
// No results were returned
if(empty($events)){
  $events = "Sorry, no events matching result";
}
// Display results and unset the global array $_GET
echo $events;
unset($_GET);

?>

This displays 10 results on a page. Whereas, when I search for 'harry potter' in 'Books' on Amazon website, I get more than 3k results. Is there any way to get all the results on one page itself? Please advise.

Answer 1

The amazon.ecs open data table (at the time of writing your question) does not support paging of results, so you are stuck with only retrieving the 10 items. This is a common oversight on the part of the open data table author.

I have amended the data table source in my own fork of the YQL tables repository, and issued a pull request (here) to hopefully get the changes back into the main sources. You will then be able to use the table.name([offset,]count) syntax (docs) to get more (or fewer!) results.

If you want to get up and running right away, then you need to change the URL to the data table to point at my changes in a special branch just for this question:

https://github.com/salathe/yql-tables/blob/so-6269923/amazon/amazon.ecs.xml

Your full query would look something like the following (very similar to your existing query):

use 'https://raw.github.com/salathe/yql-tables/so-6269923/amazon/amazon.ecs.xml' as amazon.prodlist;
use AWSAccessKeyId='my_access_key' on amazon.prodlist;
use secret='my_secret' on amazon.prodlist; 

select *
from amazon.prodlist(50) 
where Title='harry potter' 
  and SearchIndex='Books' 
  and ResponseGroup='Images,ItemAttributes';

When (if... keep an eye on the pull request) the changes are pulled back into the main YQL tables repository, you will be able to go back to using the http://www.datatables.org/amazon/amazon.ecs.xml URL.