用另一个表中的名称替换已拉取的SQL ID值 [英] Replacing a pulled SQL ID value with its name from another table

问题描述

我有一些代码（见下文）用所有记录填充一个表。然而，我想用存储在另一个表中的实际名称替换为site_id提供的ID。例如， site_id 是一个名为 sites_tbl 的表中的主键，我想把关联的 sitename ，并将其显示在表中，而不是来自 sheets_tbl 的I​​D作为外键。我假设我需要做一些循环，其中foreach site_id在$ data变量中选择 sitename其中site_id = $ row ['site_id'] 但是我无法获得它的工作。

$ $ p $ code $ 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>

解决方案

一个非常简单的SQL连接。假设site_tbl中的站点名称是 sitename ：

  $ 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> 
  

所以现在你不仅有来自 sheet_tbl sites_tbl 的相关数据。

阅读更多关于连接的信息： http://www.w3schools.com/sql/sql_join.asp

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>

解决方案

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

这篇关于用另一个表中的名称替换已拉取的SQL ID值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！