Replacing a pulled SQL ID value with its name from

问题:

I have some code (see below) that populates a table with all the records. I however want to replace the ID that is presented for site_id with its actual name which is stored in another table. The site_id for example is the primary key in a table called sites_tbl, and I want to pull the associated sitename and display this in the table rather than the ID which comes from the sheets_tbl as a foreign key. I assume I need to do some kind of loop, where foreach site_id within the $data variable Select the sitename Where site_id = the $row['site_id'] However I cannot get it to work.

$sql  = "SELECT * FROM sheet_tbl";
$stmt = $conn->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();


  <?php foreach ($data as $row): ?>
    <tr>
        <td><?=$row['sheet_id']?></td>
        <td><?=$row['username']?></td>
        <td><?=$row['site_id']?></td>
   </tr>

回答1:

I would advise a very simple SQL join. Assuming the site name is sitename in the sites_tbl:

$sql  = "SELECT sheet.sheet_id, sheet.username, site.sitename FROM sheet_tbl S
       JOIN sites_tbl ST ON ST.site_id = S.site_id ";
$stmt = $conn->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();


  <?php foreach ($data as $row): ?>
    <tr>
        <td><?=$row['sheet_id']?></td>
        <td><?=$row['username']?></td>
        <td><?=$row['sitename']?></td>
   </tr>

So now you not only have the data from sheet_tbl but also the associated data from sites_tbl that you can now use directly.

Read more about joins here: http://www.w3schools.com/sql/sql_join.asp

回答2:

You can join the tables together;

select  sheet.sheet_id
,       sheet.username
,       site.sitename
from    sheet_tbl sheet
join    sites_tbl site
on      sheet.site_id = site.site_id