CODEWITHSUNDEEP
phpmysql
Sam
Sam

Reputation: 29

INNER JOIN with another table gives one field twice?

I am very new to MySQL (sql in general) and I am trying to work something out. I have 2 tables, the first table contains a list of 'clients', the second table a list of 'users'. In the first table there are columns that contain the userID number of the 'contract manager' and the 'business development'. The ID is just a number, that references the ID number in the 'users' table. The users table then stores the First name, last name, etc of the user in question.

I am trying to run a query that will return the first and last names of both the contract manager, and the business development manager, and then use them in PHP.

I have the following query;

SELECT 
    * 
FROM
    clients
INNER JOIN
    tenantusers AS cm ON cm.tenusr_ID = clients.cli_ContractManager
INNER JOIN
    tenantusers AS bd ON bd.tenusr_ID = clients.cli_BusinessDevelopment
ORDER BY cli_Name;

Which in MySQL dashboard returns the correct results, however I can't use this in PHP because 

$t = $row['tenusr_FirstName'] . " " . $row['tenusr_LastName'];

Returns the last result of the query, and not the previous. And truth told, I have no idea how to get them. I've tried to use db.tenusr_FirstName and it throws errors.

Any help would be appreciated. 

+--------+----------+------------------+---------------+----------------------+-------------------------+
: cli_ID : cli_Name : cli_AddressLine1 : cli_Town      : cli_ContractManager  : cli_BusinessDevelopment :
+-------------------------------------------------------------------------------------------------------+
: 1      : ACME     : On a street      : Makeuptington : 1                    : 2                       :
+--------+----------+------------------+---------------+----------------------+-------------------------+

+-----------+-----------------+-----------------+--------------+
: tenusr_ID : tenusr_Username : tenusr_LastName : tenusr_Email :
+--------------------------------------------------------------+
: 1         : Sam             : Smithers        : [email protected]  :
+--------------------------------------------------------------+
: 2         : Tom             : Watson          : [email protected] :
+--------------------------------------------------------------+

I've added the tables - top is the clients table, second the users.

Upvotes: 1

Views: 65

Answers (2)

aynber
aynber

Reputation: 23011

You can't have columns with the same name in PHP. You'll want to SELECT only the columns you need, and alias the name columns with AS

SELECT 
    cm.tenusr_Username AS contract_FirstName, 
    cm.tenusr_LastName AS contract_LastName, 
    bd.tenusr_Username AS business_FirstName, 
    bd.tenusr_LastName AS business_LastName 
    ....

You can then use 

$t = $row['contract_FirstName'] . " " . $row['contract_LastName'];

Upvotes: 2

Loren
Loren

Reputation: 12701

The other answer is also right. I left clients.* in which will select all rows from clients if you need them, but you should omit it if you don't need that.

SELECT 
    clients.*,  cm.FirstName as CMFirstName, cm.LastName as CMLastName, bd.FirstName as BDFirstName, bd.LastName as BDLastName
FROM
    clients
INNER JOIN
    tenantusers AS cm ON cm.tenusr_ID = clients.cli_ContractManager
INNER JOIN
    tenantusers AS bd ON bd.tenusr_ID = clients.cli_BusinessDevelopment
ORDER BY cli_Name;

In PHP

$bd = $row['BDFirstName'] . " " . $row['BDLastName']; 
$cm = $row['CMFirstName'] . " " . $row['CMLastName'];

If not all users will have a contract manager and business development person you should change your INNER JOIN to a LEFT JOIN so it will include clients without one or the other or without both.

Upvotes: 1

Related Questions