.
The DATABASE LAYOUT is at the bottom of this script. I have not made
a serious attempt to optimize this; only a limited subset of searches
could be improved. This is very expensive on mySQL.
DO NOT deploy this on a high traffic site!
TO DO: Find a way to get the average review score and user tags.
Add date fields.
Get prices for other regions.
Maybe use some of the image links to nicify the results?
*/
ini_set('display_errors', 1);
date_default_timezone_set("America/Los_Angeles");
// Either create this config file
include("steam_config.php");
/*
* Or uncomment its content here:
*
$rebaseParam = "rebase"; // CHANGE THIS TO SOMETHING SECRET! This makes for a great DoS target, or even a DDoS amplifier.
mysql_connect("YourDBHost", "YourDBUser", "YourDBPassword");
*/
mysql_select_db("steam_games");
/*
* FETCH BASE ITEM IDs
*/
if (isset($_REQUEST[$rebaseParam])) {
$games = file_get_contents("http://api.steampowered.com/ISteamApps/GetAppList/v0001/");
$gameObj = json_decode($games, true);
for($i = 0; $i < sizeof($gameObj['applist']['apps']['app']); $i++) {
$game = $gameObj['applist']['apps']['app'][$i];
echo "#".$i." ".$game['name']."
\n";
$exists = my1fetch("SELECT COUNT(*) FROM items WHERE steamID = '".myreal($game['appid'])."';");
if ($exists[0])
myquery("UPDATE items SET name = '".myreal($game['name'])."' WHERE steamID = '".myreal($game['appid'])."';");
else
myquery("INSERT INTO items (steamID, name) VALUES ('".myreal($game['appid'])."', '".myreal($game['name'])."');");
}
}
/*
* FETCH ITEM DETAILS
*/
elseif (isset($_REQUEST['update'])) {
$rowCount = 0;
$q = myquery("SELECT ID, steamID FROM items WHERE updated < ".(time() - 3600 * 24)." AND updated >= 0 ORDER BY updated;");
while ($game = myfetch($q)) {
$gameRaw = @file_get_contents("http://store.steampowered.com/api/appdetails/?appids={$game['steamID']}");
echo "http://store.steampowered.com/api/appdetails/?appids={$game['steamID']} ... ";
flush();
if ($gameRaw) {
$gameData = json_decode($gameRaw, true);
$gameData = $gameData[$game['steamID']];
if (isset($gameData['success'])) {
if ($gameData['success']) {
$gameData = $gameData['data'];
echo "{$gameData['name']} ... ";
$type = $gameData['type'];
$description = $gameData['detailed_description'];
$header = $gameData['header_image'];
$website = $gameData['website'];
$metascore = $gameData['metacritic']['score'];
// Release date format consistency is still a question mark.
if (!$gameData['release_date']['date'])
// We used $gameData['release_date']['coming_soon'] before, but if we have a future release date, let's save it.
$release = 0;
else {
$release = strtotime(str_replace(",", "", $gameData['release_date']['date']));
echo $gameData['release_date']['date'] ." == ".date("m/Y", $release)." ... ";
}
$price = $gameData['price_overview']['final'];
$currency = $gameData['price_overview']['currency'];
if ($currency == "USD" || $currency == "EUR") $price /= 100;
// Genres
myquery("DELETE FROM itemGenres WHERE item = {$game['ID']};");
for ($g = 0; $g < sizeof($gameData['genres']); $g++) {
$rawGen = $gameData['genres'][$g];
$genId = my1fetch("SELECT ID FROM genres WHERE steamID = '{$rawGen['id']}';");
if (!$genId[0]) {
myquery("INSERT INTO genres (steamID, name) VALUES ('".myreal($rawGen['id'])."', '".myreal($rawGen['description'])."');");
$genId[0] = mysql_insert_id();
}
myquery("INSERT INTO itemGenres (item, genre) VALUES ('{$game['ID']}', '{$genId[0]}');");
}
// Categories
myquery("DELETE FROM itemCats WHERE item = {$game['ID']};");
for ($g = 0; $g < sizeof($gameData['categories']); $g++) {
$rawCat = $gameData['categories'][$g];
$catId = my1fetch("SELECT ID FROM categories WHERE steamID = '{$rawCat['id']}';");
if (!$catId[0]) {
myquery("INSERT INTO categories (steamID, name) VALUES ('".myreal($rawCat['id'])."', '".myreal($rawCat['description'])."');");
$catId[0] = mysql_insert_id();
}
myquery("INSERT INTO itemCats (item, category) VALUES ('{$game['ID']}', '{$catId[0]}');");
}
$linux = $gameData['platforms']['linux'];
$windows = $gameData['platforms']['windows'];
$mac = $gameData['platforms']['mac'];
myquery("UPDATE items SET updated = ".($type == 'game' ? time() : -2).", type = '".myreal($type)."', `release` = '".myreal($release)."', metascore = '".myreal($metascore)."', description = '".myreal($description)."', price = '".myreal($price)."', currency = '".myreal($currency)."', linux = '".myreal($linux)."', windows = '".myreal($windows)."', mac = '".myreal($mac)."' WHERE ID = {$game['ID']};");
echo("OK");
} else {
// If we get success = false, we're not rate limited, but have an unlisted whatever.
myquery("UPDATE items SET updated = -1 WHERE ID = {$game['ID']};");
echo "UNSUPPORTED";
}
} else {
// This shouldn't happen.
echo("NO DATA");
}
$rowCount++;
} else {
// Yay for rate limit.
echo("NO RESPONSE");
// We wait 5 seconds.
sleepLoudly(10);
// Then try that set again.
mysql_data_seek($q, $rowCount);
}
// Next! Get us in line with Valve's rate limit.
// This varies by server latency, increase if you get a lof of "NO RESPONSE" breaks.
flush();
usleep(1100000);
echo "
\n";
}
}
/*
* INTERFACE
*/
else {
?>
This page allows you to search the Steam Store database with more advanced options. It helps me give Valve more money, so they do not have to enhance their own game search.
Prices on this page are likely outdated, only available for the Euro zone, and not including discounts. If game data seems too far out, you can try updating the search cache.
Click here to download this script.
{$_POST['priceFrom']} ";
if (is_numeric($_POST['priceTo'])) $SQL .= "AND price < {$_POST['priceTo']} ";
// OS and Meta
if (is_numeric($_POST['metaMin']) && $_POST['metaMin']) $SQL .= "AND metascore >= {$_POST['metaMin']} ";
if (isset($_POST['linux'])) $SQL .= "AND linux = 1 ";
if (isset($_POST['windows'])) $SQL .= "AND windows = 1 ";
if (isset($_POST['mac'])) $SQL .= "AND mac = 1 ";
// Release
if ($_POST['releaseFrom']) {
$releaseFrom = strtotime($_POST['releaseFrom']);
if ($releaseFrom)
$SQL .= "AND `release` >= ".$releaseFrom." ";
else
echo "
Invalid start date!
";
}
if ($_POST['releaseTo']) {
$releaseTo = strtotime($_POST['releaseTo']);
if ($releaseTo)
$SQL .= "AND `release` <= ".$releaseTo." ";
else
echo "
Invalid end date!
";
}
echo $releaseTo;
// Genres
$q = myquery("SELECT * FROM genres ORDER BY name;");
$gYes = "";
$gNo = "";
while ($g = myfetch($q)) {
if (isset($_POST['gno-'.$g['ID']]))
$gNo .= ($gNo?", ":"").$g['ID'];
if (isset($_POST['gyes-'.$g['ID']]))
$gYes .= ($gYes?", ":"").$g['ID'];
}
if ($gYes) $SQL .= "AND (SELECT COUNT(*) FROM itemGenres gSubYes WHERE genre IN ({$gYes}) AND item = items.ID) > 0 ";
if ($gNo) $SQL .= "AND (SELECT COUNT(*) FROM itemGenres gSubNo WHERE genre IN ({$gNo}) AND item = items.ID) = 0 ";
// Order
$validOrder = Array("desc" => "DESC", "asc" => "ASC");
$validSort = Array("metascore" => "metascore", "price" => "price", "release" => "`release`");
$SQL .= "ORDER BY {$validSort[$_POST['sort']]} {$validOrder[$_POST['order']]}";
// RUN
$q = myquery($SQL);
echo "
".mysql_num_rows($q)." results.";
while ($game = myfetch($q)) {
?>
Metascore ".$game['metascore']."" : "-" ?>
€ EUR
{$g['name']} ";
}
?>