Get info from two tables in database where id match an array?

Question

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 ?

Answer 1

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/>';
    }
}