troubleshooting Question

Data from one MySQL table to be used in another table

Avatar of mar2195
mar2195 asked on
PHPMySQL Server
11 Comments1 Solution296 ViewsLast Modified:
SUMMARY:
When displaying the data from TABLE2, replace the number in TABLE2's TOPIC field with the associated TOPIC_NAME from TABLE1.

DETAILS:
I have 2 tables in the same MySQL DB. I need help populating the array from TABLE1 to be used by TABLE2 (if I even need to use an array).  If it matters, I'm using PDO.

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

============================
TABLE1 is a list of topics.

TABLE1 Syntax:
ID,TOPIC_NUM,TOPIC_NAME

Sample Record from TABLE1:
1,Topic1
2,Topic2
etc...
============================
TABLE2 is a list of individual people and associated data.

TABLE2 Syntax:
ID,NAME,STATE,TOPIC,YEAR

Sample Record from 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.

Here's my code:
(*I'm new to PDO.  If you see anything that requires corrections, etc., please let me know.)

<?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
?>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros