mysql从两个表中查询两行

Question

I have this Query to call information form two table.

DB::get("SELECT friends. * , (SELECT `login` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

So if user is on friends list show username , i would like to get username and avatar. Avatar row is avatar . I try with this.

DB::get("SELECT friends. * , (SELECT `login`, `*avatar*` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

And give me the error

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

where is the mistake?

Answer 1

First of all you should use Prepared Statement and Second, you can't write inline view, which has two columns

SELECT friends. * , (SELECT `login`, `*avatar*` FROM ..

instead you should use JOIN, which might be efficient than current approach and more readable.