[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Data from one MySQL table to be used in another table

Posted on 2013-01-06
11
Medium Priority
?
258 Views
Last Modified: 2013-01-07
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
?>

Open in new window

0
Comment
Question by:mar2195
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38748202
Learn about SQL joins.  Change your query to:

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

This way the db will already return the topic string instead of number.  Then just adjust your script to display $items_row[$topics_col1] instead of $items_row[$items_col3]
0
 
LVL 5

Expert Comment

by:Imran Saeed
ID: 38750077
Here is a reference from other forum.

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
0
 

Author Comment

by:mar2195
ID: 38750159
I would welcome input as to which of these solutions is recommended.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 24

Expert Comment

by:johanntagle
ID: 38750171
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.
0
 

Author Comment

by:mar2195
ID: 38753278
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:

<?php echo 'TOPIC: '.$items_row[$topics_col2];?>

Open in new window

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38753294
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.
0
 

Author Comment

by:mar2195
ID: 38753316
So ... from my original coding example, I would code this as:

SELECT $tablename1.ID, $tablename1.NAME, $tablename1.STATE, $tablename2.TOPIC_NAME , $tablename1.YEAR

Correct?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38753322
Yes
0
 
LVL 5

Expert Comment

by:Imran Saeed
ID: 38753323
Yes it should work. Have your tested it yet?
0
 

Author Comment

by:mar2195
ID: 38753365
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!


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

?>

Open in new window

0
 

Author Closing Comment

by:mar2195
ID: 38753369
As you can see by the thread of this topic, johanntagle's help was invaluable!  Thank you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question