CODEWITHSUNDEEP
phpmysqlarrays
Puddintane
Puddintane

Reputation: 5

Linking 2 IDs to display either one depending on which is being addressed

I have a games database and I need to create pairs of similar games in another table. So, let's say, game ID 5 and ID 12 need to be paired, which should be like this:

---------------------------
| PairID | First | Second |
---------------------------
|   1    |   5   |   12   |
---------------------------

So what I need basically is to join pairs and games tables and select and display data for all IDs of each pair depending on which game is being browsed. That is, if it's game ID 5 then ID 12 should be displayed and vice versa. Seems pretty trivial at first, except I discovered there is no neareast elegant solution to this, where the major problem is the order of IDs in the pairs table.

First of all, with this scheme I'm forced to use two separate joins of the 'games' table and select both games' data regardless like this:

SELECT simpairs.id, simpairs.first, simpairs.second,
        games_one.id AS first_id, games_two.id AS sec_id,
        games_one.title AS first_title, games_two.title AS sec_title,
        games_one.year AS first_year, games_two.year AS sec_year
        FROM simpairs
        LEFT JOIN games AS games_one ON simpairs.first = games_one.id
        LEFT JOIN games AS games_two ON simpairs.second = games_two.id
        WHERE simpairs.first = <id> OR simpairs.second = <id>

Secondly, I need the script to pick out and display the correct data like this:

$id = $_GET['id'];
while($row = mysqli_fetch_assoc($res)) {
    if($row['first_id'] != $id) {
        $game_id = $row['first_id'];
        $title = $row['first_title'];
        $year = $row['first_year'];
    } else {
        $game_id = $row['sec_id'];
        $title = $row['sec_title'];
        $year = $row['sec_year'];
    }
...
}

Ok, so this is already pretty messy. Still, the biggest issue is sorting the output alphabetically. Obviously, it can't be easily done on the SQL side unless the query is somehow rebuilt for which there is no apparent route as it must include an equivalent for PHP condition, or the pairs table should be modified to contain a mirrored pair for each new entry like this:

---------------------------
| PairID | First | Second |
---------------------------
|   1    |   5   |   12   |
---------------------------
|   2    |  12   |   5    |
---------------------------

This would work of course and I would only need 1 join instead of 2, which would also make sorting easier directly on the SQL side, yet this also doesn't seem very elegant as I would have to perform two inserts for each new pair instead of just 1 plus clutter the database with mirrored entries making it exactly 2 times larger. I mean, if this is the right way then I guess I'm fine with that.

Sorting data on the script side is no less daunting. Typically, arrays are used for the occasion, except this time around I don't see how it can be achieved effectively. I will need to store ID, game title and year of release somehow and then sort everything based on titles alone. Meaning, I should be probably using 2 dimensional arrays where titles act as parent keys for sub-arrays containing IDs and years, then I should somehow sort only the parent keys without affecting the sub-arrays and I really don't know how to do that nor if it's worth it at all. All in all, seems like unnecessary strain to both the database and the script.

So what would be the optimal solution here?

Upvotes: 0

Views: 157

Answers (1)

Erik
Erik

Reputation: 380

You have an id that relates to a game in the games table. You have a table that links other game id's to the main game id You want to show a list of those linked games and related info.

Select lg.*
FROM `games` g
LEFT JOIN `simpairs` s
ON s.first = g.id OR s.second = g.id
JOIN `games` lg // linked games
on lg.id = case when s.first = g.id then s.second else s.first end
where g.id = ?

Let me know if you have any questions, it's pretty self explanatory. Though it does assume simpairs never has reverse duplicate values in first and second otherwise you might need to filter those out.

Upvotes: 1

Related Questions