PDO to draw data from parent tables through the conjunction table

Question

How could you draw data from the parent tables, through the conjunction table using PDO in PHP & MySQL?

I have a Games table with gameId and gameTitle. and a Platform table with platformId and platformName. the conjunction is GamePlatform but it only includes gameId and platformId therefore I can't get either names from this conjunction and so I have to draw from Games and Platform simultaneously through the conjunction. In my PHP I have the following classes:

Games - gameId{PK}, gameTitle
Platforms - platformId{PK}, platformname
GamePlatform - gameId{FK}, platformId{FK} - (Composite PK)

after some digging around, i found an answer directing me to use join statements:

function getPlatformByGameTitle($gameTitle) {
 global $pdo;
 $sql = '
     SELECT g.gameTitle, p.platformName 
     FROM GamePlatform gp
     INNER JOIN Games g ON gp.gameId = g.gameId
     INNER JOIN Platforms p ON gp.platformId = p.platformId
     WHERE g.gameTitle = ?
 ';
 $statement = $pdo->prepare($sql);
 $statement->execute([$gameTitle]);
 $result = $statement->fetchAll(PDO::FETCH_OBJ);
 return $result;
}

i think this might work but the next issue i have is actually displayig the platformName to the screen based on gameTitle search. My controller includes:

<?php
 require_once "../model/gameplatform.php";
 require_once "../model/dataAccess.php";
 session_start();

 if (isset($_REQUEST["searchTitle"]) && $_REQUEST["searchTitle"] != "") 
  {
    $results = getPlatformByGameTitle($_REQUEST["searchTitle"]);
  }  else {
     $results = [];
  }

require_once "../view/machinelist_view.php";
 ?>

i know that i would need a for each loop in the view, but there seems to be an issue where not all of the gamePlatforms that operate on those games will actually display.

<tbody>

  <?php foreach ($results as $platform): ?>

    <tr><td><?= $platform->platformName ?></td></tr>

  <?php endforeach; ?>

</tbody>

Would i also need to include the parent table classes in the controller? Apologies for such a messy explanation, but i dont know how else i could explain this.

Find yourself a beginner (My)SQL tutorial, and read up on JOINs. — C3roe
– C3roe, Commented Mar 7 at 12:05

Ranadip Dutta · Accepted Answer · 2025-03-07 12:08:51Z

0

Kindly read about JOINs.

Updated Function:

function getPlatformByGameTitle($gameTitle) {
    global $pdo;
    $sql = '
        SELECT g.gameTitle, p.platformName 
        FROM GamePlatform gp
        INNER JOIN Games g ON gp.gameId = g.gameId
        INNER JOIN Platforms p ON gp.platformId = p.platformId
        WHERE g.gameTitle = ?
    ';
    $statement = $pdo->prepare($sql);
    $statement->execute([$gameTitle]);
    $result = $statement->fetchAll(PDO::FETCH_OBJ);
    return $result;
}

answered Mar 7 at 12:08

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PDO to draw data from parent tables through the conjunction table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related