Reputation: 1322
I have a two tables and one is prices which stores different prices:
prices
appid de us ru nl gb
29382 899 999 1299 899 699
48371 1299 1599 1899 1299 999
58193 699 899 999 899 599
And other table is games which stores various info about games:
games
appid title releasedate controler language
29382 title 1 1358197200 1 en
48371 title 2 1329858000 0 en
58193 title 3 1201554000 1 en
As you can see both tables have same appid and i need to match that with outer id's from json file
And at the end i have a json file which looks like this:
{
"response": {
"games": [
{
"appid": 58193,
"playtime_forever": 0
},
{
"appid": 29382,
"playtime_forever": 0
},
{
"appid": 48371,
"playtime_forever": 151
}
]
}
}
Now if this was just pull an info from one table based on json field i would know how to do it but for me this seems a bit complicated. I tried various solutions but never got an working one.
The final code i have now is this:
$listusergames = json_decode(file_get_contents('http://path_to_file.json'));
foreach ($listusergames->response->games as $game) {
$gameid = $game->appid;
$querygamename = mysqli_query($conn, "SELECT * FROM games WHERE appid='$gameid'");
$rowgame = mysqli_fetch_array($querygamename);
if($rowgame[0] > 1 ) {
echo 'Your game: ' . $rowgame['title'] . ' is worth $' . $rowgame['us'] . ' and game id is - '. $gameid . '<br/>';
}
}
In this code $rowgame['us'] is invalid right now because i don't know how to pull data from both tables based on json field.
In the solution that i have right now, each appid is pulled from json file and then i selected all values from games table based on appid values from json file, and used if($rowgame[0] > 1 ) because otherwite it will echo empty line for each appid from json file that doesn't exist in database.
But i don't know how to select both tables and display game title from one table and different prices from other table and match them with appid from json file and if it doesn't exist in database skip that appid.
Edit: One more quick question, since mysql_query is in foreach loop wouldn't that consume too much resources since query will be executed for each $game so if i have 500 games i will have 500 queries for every loop, and making my site very slow, or im wrong ?
Upvotes: 0
Views: 1997
Reputation: 13354
Updated query to JOIN both tables:
SELECT g.*, p.* FROM games g
LEFT JOIN prices p on (g.appid = p.appid)
WHERE g.appid='$gameid'
Traditionally you could implode()
an array from your JSON results into one string, and use WHERE/IN SQL statements. But, because you are looking at the object's appid parameter, and not a simple array, you cannot implode()
without a bit of extra effort.
You could foreach()
through the JSON results to build your query, and execute just one MySQL statement which will save some overhead.
Full updated code:
// Grab JSON list
$listusergames = json_decode(file_get_contents('http://path_to_file.json'));
// Setup new array
$games = array();
// Add member to array for each game's appid
// Resulting array looks like ( [0]=>'58193', [1]=>'29382', [2]=>'48371 )
foreach ($listusergames->response->games as $game) {
$games[] = $game->appid;
}
// Implode games array into a comma-separated string like: "58193,29382,48371"
$games_list = implode( ',' , $games );
// Updated SQL statement using WHERE/IN list
$querygamename = mysqli_query($conn, "SELECT g.*, p.* FROM games g
LEFT JOIN prices p on (g.appid = p.appid)
WHERE g.appid IN ($games_list) ");
// Loop through result set
while ( $rowgame = mysqli_fetch_array($querygamename) ){
if($rowgame[0] > 1 ) {
echo 'Your game: ' . $rowgame['title'] . ' is worth $' . $rowgame['us'] . ' and game id is - '. $gameid . '<br/>';
}
}
Upvotes: 1