使用OOP和PDO时，LEFT JOIN快捷方式会导致错误

Question

Here's my code:

Mysql table:

group

| id | name | permission |

users

| id | name | username | group |

$sql = 'SELECT * FROM `users` u LEFT JOIN `groups` g ON g.id = u.group WHERE u.name = ? OR u.id = ?;

Since I have columns named the same, when I try to set the names in php, it is giving me wrong data (from groups to be exact)

'ID'            => isset($user->data()->u.id) ? escape($user->data()->u.id) : NULL,
'NAME'          => isset($user->data()->u.name) ? escape($user->data()->u.name) : NULL,
'USERNAME'      => isset($user->data()->u.username) ? escape($user->data()->u.username) : NULL,

It is giving me error: Fatal error: Cannot use isset() on the result of an expression (you can use "null !== expression" instead) in C:\aaa\lang\en\common.lang.php on line 9

How can I write an SQL in a way where it doesn't interfier with my objects else where?

PS: I'm aware that I can rename my columns, but it will take me forever and I'm sure there's got to be a better way.

-Thanks

Answer 1

Sounds like you need to stop using SELECT * (always a good option IMO) and use column aliases where appropriate. For example...

SELECT u.id AS user_id, u.name AS user_name, u.username,
       g.id AS group_id, g.name AS group_name -- and so on

As for your error message, you are using the concatenation operator (.). That results in an expression which cannot be used in isset(). With the above aliases and assuming that $user->data() returns the results via PDO::FETCH_OBJ, you can use...

'ID' => isset($user->data()->user_id) ? escape($user->data()->user_id) : null,
// etc

If you really don't want to select each column, providing aliases where appropriate, the only other option I can think of would be to create a view, eg

CREATE VIEW users_and_groups AS
SELECT u.id AS user_id, u.name AS user_name, u.username,
       g.id AS group_id, g.name AS group_name -- and any other columns you need
FROM users u
LEFT JOIN groups g ON g.id = u.`group`

Then you can simply SELECT * FROM users_and_groups.