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.

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.

• database
• model-view-controller
• pdo

Share Follow edited Mar 7 at 12:35 n30n0n3 asked Mar 7 at 12:00 n30n0n3n30n0n3 733 bronze badges 1

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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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;
}