asked on
Data from one MySQL table to be used in another table
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.)
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
?>
PHPMySQL Server
Last Comment
mar2195
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
I would welcome input as to which of these solutions is recommended.
We basically gave you the same solution. If you look closely the two queries presented use the same logic. I just gave the extra comment that you also need to make changes in your PHP code as well.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
1. Is xImran's solution supposed to be literal? Meaning... COPY/PASTE the code (using the "a.xxxx" "b.xxxx") and it will execute correctly or is this an example of the JOIN INNER method? ... he was simply giving me an example using the "a" "b" ? I would like to know if using the "dot" method he suggests is another way to code this.
2. The solution provided by johanntagle works perfectly - except for an error/typo in the actual display of the output. It should be:
2. The solution provided by johanntagle works perfectly - except for an error/typo in the actual display of the output. It should be:
<?php echo 'TOPIC: '.$items_row[$topics_col2];?>
xImran's solution still needed to be adapted to use your php variables. But other than that - "a" and "b" are just aliases, which are useful if the names of the tables involved are quite long. And using a.ID, etc in the select clause is optional unless the tables involved have commonly named columns. "INNER JOIN" and just "JOIN" are the same. That's why I said the logic presented by both solutions are the same.
ASKER
So ... from my original coding example, I would code this as:
SELECT $tablename1.ID, $tablename1.NAME, $tablename1.STATE, $tablename2.TOPIC_NAME , $tablename1.YEAR
Correct?
SELECT $tablename1.ID, $tablename1.NAME, $tablename1.STATE, $tablename2.TOPIC_NAME , $tablename1.YEAR
Correct?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Yes
Yes it should work. Have your tested it yet?
ASKER
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.
As always, thanks for the forums' experts help!
I have tested this script (using both methods) and it functions perfectly.
As always, thanks for the forums' experts help!
<?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
?>
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
As you can see by the thread of this topic, johanntagle's help was invaluable! Thank you.
http://sqlfiddle.com/?_escaped_fragment_=2/d7be9/1#!2/d7be9/1
simply use 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