1

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

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Tatarin
  • 1,238
  • 11
  • 28
  • 1
    Are you sure that's what line #9 of `common.lang.php` looks like? The error message doesn't seem to marry up with the code. What does `$user->data()` return? – Phil Feb 26 '14 at 04:52
  • You right Phil, I originally had u.id and u.name which triggered that error, I fixed it before I posted a question. Anyways, I put it back, just to make the source of the error a little more obvious. – Tatarin Feb 26 '14 at 04:59
  • You must not prefix the object properties with the table name. Just use the field name : `isset($user->data()->id)` – Sebastien C. Feb 26 '14 at 22:49

1 Answers1

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.

Phil
  • 157,677
  • 23
  • 242
  • 245
  • This would work fine, but I'm actually selecting all the columns. If I use what you've suggested I will have to write all of the columns. Is there a way where I can only overwrite columns with the same name, but the rest will be selected the standard way? Ie: SELECT u.id AS user_id, * FROM something like that? – Tatarin Feb 26 '14 at 13:43
  • @Timur I've added an alternative but I still think `SELECT *` is just lazy. You might also be interested in reading this - [Why is SELECT * considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Phil Feb 26 '14 at 22:45
  • Thanks for the tip, phil. I ended up using your first suggestion. – Tatarin Feb 27 '14 at 23:02