根据相同的值从两个表返回数据 [英] Return data from two tables based on identical value

问题描述

我有两个表，每个表都有3列

人

  id_number /名字/姓氏/城镇
 
 T1234 /詹姆斯/史密斯/丹特
 T1235 /彼得/曼特尔/丹特
 T1236 /米莉/王/ Mormer 
 T1237 /苏珊/谭/丹特
  

日期

 编号/ id_reference /有效期
 
 T1234 /学生证/ 2018/12/24 
 T1235 /借书卡/ 23/07/2019 
 T1236 /借书卡/ 16/07/2019 
 T1235 /许可证/ 02/03/2018 
  

我正在尝试运行查询以有效显示这两个数据，但是出现问题。基本上，我需要运行查询并使用两个表中的数据获取如下所示的答案，id_number和number相同。所以我需要查询从一个表中查找数据，然后从另一个表中获取到期日期：-

  id_number / last_name /到期
 
 T1234 / Smith / 2018年12月24日
 T1235 /地幔/ 23/07/2019 
 T1237 / Tan / 02/03/2018 
 
<？php 
 
 $ dbconn = pg_connect（ host = 127.0.0.1 dbname = sammy_nt user = sammy_nt password = **********）或die（'无法连接：'。 pg_last_error（））; 
 $ town = pg_escape_string（$ _ POST ['town']）; 
 $ query = SELECT * FROM People p，date d 
 WHERE p.id_number = d。数字
和p.town ='$ town'
 ORDER BY p.id_number; 
 
 $ result = pg_query（$ query）; 
 if（！$结果）{
 echo查询问题。$ query。< br />; 
 echo pg_last_error（）; 
 exit（）; 
} 
 
 while（$ myrow = pg_fetch_assoc（$ result））{
 printf（< tr >< td>％s< / td> td>％s< / td< td>％s< / td< / tr>，$ myrow ['id_number']，htmlspecialchars（$ myrow ['last_name']），htmlspecialchars（$ myrow ['expiry']））; 
} 
？> 
  

请协助。

解决方案

在您的情况下，您有2个表。您要从中获取数据的人和日期。您可以将以下SQL查询与 INNER JOIN 一起使用。

  SELECT人员。*，Dates。* FROM People INNER JOIN Dates on People.id_number = Dates.number 
  

请请注意，您可以从 WHERE 并使用 ORDER BY 和/或 LIMIT ，就像通常一样。

其解释如下：

首先请输入 SELECT 之后，您将得到 People。* ，这表示表中的所有人员为人，因此您输入表名和。和字段（全部为 * ）。添加一个逗号，之后，然后是具有相同语法的下一个表格。在您的情况下，它是 Dates。* 。然后添加 FROM 第一个表名，之后添加 INNER JOIN 和第二个表名，然后在这两个表之间通过定义引用ON ，然后两个表中的字段 Peop le.id_number = Dates.number 。语法是一样的， Table.Field 。

现在，您可以简单地正常显示结果。

请注意，我已为您提供了可以插入代码中的SQL查询。

如果需要进一步说明，请随时让我知道。

p>希望这会有所帮助。

I have two tables with 3 columns each

People

id_number / first_name / last_name / town

T1234     / James      / Smith     / Dant
T1235     / Peter      / Mantle    / Dant
T1236     / Milly      / Wong      / Mormer
T1237     / Susan      / Tan       / Dant

Dates

number / id_reference / expiry

T1234  / Student id   / 24/12/2018
T1235  / Library Card / 23/07/2019
T1236  / Library Card / 16/07/2019
T1235  / Licence      / 02/03/2018

I'm trying to run a query to display data from both efficiently but having issues. Basically what I need is to run a query and get an answer like below with data from both tables, id_number and number are the same. So I need the query to find the data from one table, then get the expiry date from another:-

id_number / last_name / expiry

T1234     / Smith     / 24/12/2018
T1235     / Mantle    / 23/07/2019
T1237     / Tan       / 02/03/2018

<?php

        $dbconn = pg_connect("host=127.0.0.1 dbname=sammy_nt user=sammy_nt password=******) or die('Could not connect: ' . pg_last_error());
    $town = pg_escape_string($_POST['town']);
        $query = "SELECT * FROM People p, Dates d
            WHERE p.id_number=d.number
            AND p.town='$town'
            ORDER BY p.id_number";

        $result = pg_query($query); 
        if (!$result) { 
            echo "Problem with query " . $query . "<br/>"; 
            echo pg_last_error(); 
            exit(); 
        } 

        while($myrow = pg_fetch_assoc($result)) { 
            printf ("<tr><td>%s</td><td>%s</td><td>%s</td></tr>", $myrow['id_number'], htmlspecialchars($myrow['last_name']), htmlspecialchars($myrow['expiry']));
        } 
        ?> 

Please assist.

解决方案

In your case you have 2 tables. People and Dates from which you want to fetch data. You can use the below SQL Query with INNER JOIN.

SELECT People.*, Dates.* FROM People INNER JOIN Dates ON People.id_number = Dates.number"

Please note that you can append SQL Query from WHERE and with ORDER BY and/or LIMIT as you would normally do.

Explanation of this is below:

First you have SELECT. After that you have People.*, which means that All from table People. So you write table name and the . and field (* for All). Add a comman , after this and then the next table with the same syntax. In your case it is Dates.*. Then you add FROM First Table Name. After this you add INNER JOIN and Second Table Name. After this you define the reference between the two tables by ON and then the fields from both the tables People.id_number = Dates.number. Syntax is the same, Table.Field.

Now you can simply display the result normally.

Please note that I have provided you with the SQL Query that you can insert in your code. It might be PDO or Prepared Statement.

If you require further clarification, do not hesitate in letting me know.

Hope this helps.

这篇关于根据相同的值从两个表返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！