Weird MYSQL results using PDO::FETCH_ASSOC without column alias

219
September 02, 2017, at 00:49 AM

I have a simple left join query.

SELECT e.employee_id as employee
, e.badge_id as badge
, e.first_nm as first
, e.last_nm as last
, e.work_phone as work_ph
, e.mobile_phone as mobile_ph
, e.manager_id as man_id
, e.title_id as titl_id
, e.username as user
, e.start_dt as start
, m.employee_id as memp_id
, m.last_nm as m_last
, m.first_nm as m_first
, t.title_nm as titl_nm
FROM employee e
left join employee m
on e.manager_id = m.employee_id
left join title t
on e.title_id = t.title_id
WHERE e.employee_id = 1

If I use column aliases as I have done above, the query works fine. If I do not use aliases, however, some values do not get returned. For example, the following returns a space if I do not give the column an alias.

e.first_nm as first      //returns "Robert"
e.first_nm               //returns ""
e.first_nm as first_nm   //returns "" (alias matches column name)

In this same query,

e.middle_nm              //will return "P" 

regardless of whether it has an alias or not. I'm baffled.

I have given my tables aliases and I have used the table alias in the column names so there shouldn't be any ambiguous column names.

Any thoughts would be appreciated. Thanks,

Rob

Answer 1

You have two columns with same name as first_nm and problably the PDO donĀ“t know what return to your code then return simple "". Although they are in diferent tables when came to a record they have the same name... you see the problem?

Answer 2

You have two columns with same name as first_nm and mysql knows how to return them all right, and then return simple "first_nm" for both. And then PDO have to assign them to array members, making field names as array keys. There is only one way, like this

$row['first_nm'] = first col;
$row['first_nm'] = second col;

If you try to see into $row, how many entries you will find?

So, you either have to use FETCH_ROW or give your fields distinct names. It's neither mysql nor PDO to blame - it's just how the things work.

Rent Charter Buses Company
READ ALSO
How to make Login in UWP using PHP and MYSQL

How to make Login in UWP using PHP and MYSQL

I want to make login and registration in my UWP app using PHP and MySQL

255
Increase execution time for MySql delete query

Increase execution time for MySql delete query

I need to remove duplicate rows in table by some value and leave only 1i use this query

260
multi level login does not go to the assigned menu

multi level login does not go to the assigned menu

I want to make multi level user login menu, but it doesn't go the assigned menu according to different user levelwhat did i do wrong?`

245