Sam Sam - 18 days ago 5
MySQL Question

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 : make@it.com :
+--------------------------------------------------------------+
: 2 : Tom : Watson : tom@fake.net :
+--------------------------------------------------------------+


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

Answer

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']; 
Comments