来自一个MySQL表的数据将在另一个表中使用

SUMMARY:

When displaying the data from TABLE2, replace the number in TABLE2's TOPIC field with the associated TOPIC_NAME from TABLE1.

显示TABLE2中的数据时,将TABLE2的TOPIC字段中的数字替换为TABLE1中关联的TOPIC_NAME。

DETAILS:

I have 2 tables in the same MySQL DB. I need help populating the array from TABLE1 to be used by TABLE2. If it matters, I'm using PDO.

我在同一个MySQL DB中有2个表。我需要帮助从TABLE1填充数组以供TABLE2使用。如果重要,我正在使用PDO。

No UPDATE or INSERT... just display the words instead of the number.

没有更新或插入...只显示单词而不是数字。

Do I nest the TABLE2 Foreach display loop within TABLE1's Foreach loop?

我是否将TABLE2 Foreach显示循环嵌套在TABLE1的Foreach循环中?

or...

Can I populate an array with the TOPIC data first and then execute the TABLE1 Foreach display loop?

我可以先使用TOPIC数据填充数组,然后执行TABLE1 Foreach显示循环吗?

or...

Can I just reference the TOPIC_NAME from TABLE1 using the data from TABLE2 without creating an array?

我可以使用TABLE2中的数据从TABLE1引用TOPIC_NAME而无需创建数组吗?

or...

???

============================

TABLE1 is a list of topics.

TABLE1是主题列表。

TABLE1 Syntax:

TOPIC_NUM,TOPIC_NAME

Sample Record from TABLE1:

TABLE1的示例记录:

1,Topic1

2,Topic2

etc...

============================

TABLE2 is a list of individual people and associated data.

表2是个人和相关数据的列表。

TABLE2 Syntax:

ID,NAME,STATE,TOPIC,YEAR

Sample Record from TABLE2:

TABLE2中的示例记录:

1,John Smith,MA,2,2005

2,Jane Doe,AZ,1,2009

etc...

============================

FYI: In the Sample Record above, the number after the State abbreviation is the TOPIC.

仅供参考:在上面的样本记录中,State缩写后的数字是TOPIC。

Here's my code:

这是我的代码:

<?php 

try {

    $db = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $db->exec("SET CHARACTER SET utf8");


// TABLE1
$tablename1 = 'topics';
$topics_col1 = 'TOPIC_NUM';
$topics_col2 = 'TOPIC_NAME';

// TABLE2
$tablename2 = 'people';
$items_col1 = 'NAME';
$items_col2 = 'STATE';
$items_col3 = 'TOPIC';
$items_col4 = 'YEAR';


$items_q = "SELECT $items_col1, $items_col2, $items_col3 FROM $tablename2";
$items = $db->query($items_q);

foreach ($items as $items_row) {
?>

<h2><?php 
/*


THIS IS WHERE THE TOPIC_NAME FROM TABLE1 SHOULD DISPLAY


*/
?></h2>

<p>
<?php echo $items_row[$items_col1];?>
<br />
<?php echo $items_row[$items_col2];?>
<br />
<?php echo $items_row[$items_col3];?>
<br />
<?php echo $items_row[$items_col4];?>
</p>

<?php   
} // end FOREACH

    $db = null; // close the database connection

} // end TRY
catch(PDOException $e) {
    echo '<span class="error">ERROR:</span><br />'.$e->getMessage() . "<br />";
    die();
} // end CATCH

?>

2 个解决方案

#1

simply use INNER JOIN

只需使用INNER JOIN

SELECT  a.ID, a.NAME, a.STATE, b.TOPIC_NAME , a.YEAR
FROM    table2 a
        INNER JOIN table1 b
            ON  a.topic = b.topic_num

• SQLFiddle Demo

#2

For future reference, here is the final version of this script. I have included a commented section that shows the alternative coding method for the query using the "dot" method.

供将来参考,以下是此脚本的最终版本。我已经包含了一个注释部分,该部分使用“点”方法显示了查询的替代编码方法。

I have tested this script (using both methods) and it functions perfectly.

我已经测试了这个脚本(使用两种方法)并且它完美地运行。

<?php 

try {

    $db = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $db->exec("SET CHARACTER SET utf8");


// TABLE1
$tablename1 = 'topics';
$topics_col1 = 'TOPIC_NUM';
$topics_col2 = 'TOPIC_NAME';

// TABLE2
$tablename2 = 'people';
$items_col1 = 'NAME';
$items_col2 = 'STATE';
$items_col3 = 'TOPIC';
$items_col4 = 'YEAR';


$items_q = "SELECT $items_col1, $items_col2, $items_col3, $items_col4, $topics_col2 
FROM $tablename2 
JOIN $tablename1 on ($items_col3 = $topics_col1)";

/* ALTERNATE METHOD FOR QUERY

(*No need for all of the vars above using this method.)
$items_q = "SELECT $tablename2.NAME, $tablename2.STATE, $tablename2.TOPIC, $tablename2.YEAR, $tablename1.TOPIC_NAME 
FROM $tablename2 
INNER JOIN $tablename1 on ($tablename2.TOPIC = $tablename1.TOPIC_NUM)";

*/

$items = $db->query($items_q);

foreach ($items as $items_row) {
?>

<h2><?php echo $items_row[$topics_col2];?></h2>

<p>
<?php echo $items_row[$items_col1];?>
<br />
<?php echo $items_row[$items_col2];?>
<br />
<?php echo $items_row[$items_col3];?>
<br />
<?php echo $items_row[$items_col4];?>
</p>

<?php   
} // end FOREACH

    $db = null; // close the database connection

} // end TRY
catch(PDOException $e) {
    echo '<span class="error">ERROR:</span><br />'.$e->getMessage() . "<br />";
    die();
} // end CATCH

?>