How to make data from table2 dependent on data from table1?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nanonano26
Forum Newbie
Posts: 5
Joined: Sun Sep 18, 2011 6:51 am

How to make data from table2 dependent on data from table1?

Post by Nanonano26 »

Hello everyone!

First of all, I am totally new to PHP, so I apologize for my coding mistakes.

I really need to figure out a way to do this:
After retrieving some data from a table called table1, I will store that data in a variable called employee. (This is easy enough.)
Furthermore I want to use that data which is now stored in the variable employee in an effort to retrieve some other data from another table (called table2) on the same database.
Said another way: The data-extraction which come out from table2, is dependent on the outcome of the data-extraction from table1.

Please explain the solution as simple as possible, since I am totally new to both PHP and mysql.

I've tried to show with code what I mean. I know it's not working, but hopefully you get the idea. Please correct me also if I have any other syntax flaws:

Code: Select all

<?PHP

//connect to database
mysql_connect(****, ******, ******) or die(mysql_error());
mysql_select_db(******) or die(mysql_error());

//select data from table1
$result1 = mysql_query('SELECT LastName FROM Table1 WHERE FirstName="John"');
$row = mysql_fetch_array($result1)
$employee = $row['LastName'];

//select data from table2, based on data from table1.
$result2 = mysql_query("SELECT wage FROM Table2 WHERE employee=$employee);
$row = mysql_fetch_array($result2)
$wage = $row['wage'];

mysql_close();

?>
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: How to make data from table2 dependent on data from tabl

Post by AbraCadaver »

You're missing the closing quote in the second query and you need to quote $employee.

But you can do it in one query (not tested):
[text]SELECT Table2.wage FROM Table2, Table1 WHERE Table2.employee=Table1.LastName AND Table1.FirstName='John'[/text]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Nanonano26
Forum Newbie
Posts: 5
Joined: Sun Sep 18, 2011 6:51 am

Re: How to make data from table2 dependent on data from tabl

Post by Nanonano26 »

Awesome AbraCadaver! That one line version worked very well. Thank you!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to make data from table2 dependent on data from tabl

Post by VladSun »

Though AbraCadaver's query is absolutely OK, try using the JOIN keyword, because this query is a pure JOIN query:

Code: Select all

SELECT 
    Table2.wage 
FROM 
    Table2
INNER JOIN 
    Table1 ON Table2.employee = Table1.LastName 
WHERE
    Table1.FirstName = 'John'
Also, adding indexes for Table1.LastName and Table1.FirstName columns would be a good idea (though using integer IDs as PK and join columns would be much better).
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How to make data from table2 dependent on data from tabl

Post by VladSun »

binlieu wrote:my suggest is OUTER JOIN
Huh, why outter join? Looking at the OP code it should be an INNER join...
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply