Page 1 of 1

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

Posted: Sun Sep 18, 2011 7:13 am
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();

?>

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

Posted: Sun Sep 18, 2011 8:25 am
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]

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

Posted: Sun Sep 18, 2011 10:16 am
by Nanonano26
Awesome AbraCadaver! That one line version worked very well. Thank you!

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

Posted: Sun Sep 18, 2011 1:59 pm
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).

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

Posted: Thu Oct 13, 2011 3:05 pm
by VladSun
binlieu wrote:my suggest is OUTER JOIN
Huh, why outter join? Looking at the OP code it should be an INNER join...