CODEWITHSUNDEEP
phpmysqlsqlpdo
Martinovska
Martinovska

Reputation: 89

mysql query two row from two tables

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?

Upvotes: 0

Views: 158

Answers (2)

Ravi
Ravi

Reputation: 31397

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.

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30819

You need to use JOIN, e.g.: 

SELECT f.*, u.*
FROM friends f JOIN users u ON f.user_id = u.id
WHERE f.id_user = <your_id>
ORDER BY id DESC LIMIT <your_limit>;

Upvotes: 2

Related Questions