PHP queries across tables.

[dave1236]

How do I join two tables in a query.

Specifically, when someone logs into my site, a record in Table 'Login' is automatically created, capturing 'loginName'.

I have a separate table 'Member' which has the following fields, 'loginName' and 'accountName', among others

'accountName' is an admin feature, designed to allow the "boss" to see specific logins for a specific group. For example, the "boss" can see all the logins by 'groupx' only. And the members of the group cannot see aggregate login info.

In english, I want a query that does the following:

$sql = "SELECT * from Login WHERE loginName (from table 'Login") = accountName (from table 'Member')"

I then will present the list in table format.

Using this query, I get a blank page:
$sql = "SELECT * FROM Login
WHERE loginName='accountName'"

Thanks for your help!

[scriptmasterdel]

Quick solution, select the rows from both tables at once using a where caluse or create a join.

$sql = "SELECT * from Login as l, Member as m WHERE l.loginName = m.accountName"

... or ...

$sql = "SELECT * from Login as l LEFT JOIN Member as m on l.loginName = m.accountName "

I could have made a mistake i wrote it so fast, apart from that i hope i have helped.

Del

[dave1236]

Thanks for the assistance. I modified what you provided, using this query:

$sql = "SELECT * FROM Login,Member
WHERE Login.loginName = Member.accountName";

However, this returns all my login entries.

How do I modify this to make it so that the only entries being returned will correspond to those that match the loginName from my 'member' table.

In other words, a 'boss' logs in with 'boss' username. The boss is interested only in visitors that log in as 'mycompany' (which is a separate login).

So, here is what happens:

a user comes to the site...logs in as 'mycompany'. 'Mycompany' exists as a separate entry in table 'Member'. This login creates an entry in Login.

The boss, using a separate login, wants to see all logins for 'mycompany'. In table 'Member', I have a field called loginName (which is what all users validate against) and a field called accountName (which is set equal to loginName in table 'Login').

When a 'boss' logs in, I want the query to return only those entries that match his interest.

Any thoughts?

[dave1236]

I think I could accomplish this by naming a variable in my query, and assigning this value to a field in my database.

For example:

$id = Member.accountName

and a query like this:

$sql = "SELECT * FROM Login,Member WHERE Member.loginName='{$id}' AND loginName=Member.accountName";

Would this work?