You need more tables for storing item classes, subclasses and any other shared references you may want to cache.
Start by creating a table structure for classes that can hold the information returned by /data/wow/item-class/index and /data/wow/item-class/:id.
After that create a table for storing subclasses returned by /data/wow/item-class/:class_id/item-subclass/:id. For this table you’ll need a composite primary key using both item_class_id and item_subclass_id, item_class_id should also be a foreign key to the previous class table.
Once you have classes and subclasses you can start working on your items table using a composite foreign key to the item subclass table.
After that you need to modify your auction table to include a foreign key to your items table.
Have a look at the table structure at wow-query.dev as it is also a relational database and stores the item structure like this.
A few observations
It is better to have two separate routines in this scenario:
One for keeping the item structure up to date by taking advantage of the new item search endpoint and its pagination feature.
One for fetching and updating the actual auction data.
Since you are using PHP, make sure to use multi_curl for a big performance improvement by fetching multiple items at once, of course you’ll need some sort of queue for updating the items.
Remember to monitor the HTTP status 429 in case you exceed your API limit (usually 36k requests / hour).
Thank you for your detailed answer. It took me a bit of time to set up the multi_curl to fetch the item informations. Now it works, but I think I have the problem that it makes more than 100 calls per second so I dont get all the item informations stored in mysql.
Is this possible? You write something “queue for updating the items”. Can you explain it a bit more. I prefer an easy solution here. Maybe something directly in the multi_curl function.
Would be CURLMOPT_MAX_HOST_CONNECTIONS a solution?
I also have a question regarding
taking advantage of the new item search endpoint and its pagination feature.
In my multi_curl url I use the endpoint:
/data/wow/item/{itemId}
Which advantage do I have with the search endpoint?
$page = 1;
do {
$data = fetch("https://us.api.blizzard.com/data/wow/search/item?namespace=static-us&orderby=id&_page=$page&access_token=XXX");
foreach ($data->results as $entry) {
// Insert/update the item on your DB
}
$page++;
} while(count($data->results) > 0);
An important note on the use of multi_curl: It will increase resource usage, so you have to be aware of the infrastructure you’ll use. Also in this scenario if you don’t have to fetch each item individually using the /data/wow/item/{itemId} you might use it to fetch 10000 items at a time by fetching 100 pages from search in parallel.
Please note I’m a former PHP developer, so the information I provided might be outdated, please double check this on PHP docs. I also wrote those functions directly in the forum, some errors are expected, it should serve as a starting point tho.
Oh boy. That is new to me. The documentation does not mention a cap for those endpoints, at least I haven’t seen that.
I was in the middle of updating my own projects to use this, to be honest so far I had only used 5 pages to check the consistency of IDs returned by this endpoint. I’ll run some tests to see if there is a workaround. I’m sorry for this inconvenience.
I’ll post an update here if I find anything useful for that.
It seems this endpoint is actually capped at 1000 records per query. You can get 10 pages of 100 records, or only one page with 1000 records.
There is a workaround for this, we can use the min feature to get the next 1000 items by setting the minimum item ID to the next item from the last query:
https://us.api.blizzard.com/data/wow/search/item?namespace=static-us&orderby=id&_pageSize=1000&id=[1]&_page=1&access_token= will return the first 1000 items, we then get the id of the last item (2429) and add one, then query the next 1000 like this:
$startingItem = 1;
do {
$data = fetch("https://us.api.blizzard.com/data/wow/search/item?namespace=static-us&orderby=id&_pageSize=1000&id=[$startingItem,]&_page=1&access_token=");
foreach ($data->results as $entry) {
// Insert/update the item on your DB
}
$startingItem = $data->results[array_key_last($data->results)]->data->id + 1;
} while(count($data->results) > 0);
Important: While there is no restriction, we should wait on a blue answer as if this workaround is acceptable. It doesn’t seem like this endpoint was designed to allow such use, otherwise there wouldn’t be a cap.
In this example I posted I assume the fetch function is already returning the decoded json data, the foreach is iterating the decoded result.
Since we cant safely use the pagination anymore, it is not possible to use multi_curl to fetch multiple searches in parallel, on the other hand we are getting 1k records per request.
There is no fetch function on PHP, I didn’t provide you a ready to use code, just a sample on how to do the logic.
You have to implement your own function for fetching data from the API using cURL or whatever, fetch in that context is the function retrieving the decoded json from the search endpoint.
The 1000 result limit is indeed an intentional limitation applied to the result set. Furthermore, the pattern described by Schiller using the min functionality is indeed an acceptable usage of the Search APIs, so no worries there.