Get data from another table in mysql and quantity

Navigation
4

I have a lot of questions about MySQL when I have to use INNER JOIN to get data from other tables.

I have 3 tables:

MEDIA

id, user_id, title, description

COMMENTS

id, user_id, media_id, comment

MEDIA_LIKES

id, user_id, media_id

The MEDIA table is the main one, it contains id do post and id do usuário , then I have the comments table where the comments are posted in certain posts and then the like given in these posts as well.

My big question is how through a Query in MySQL I can get the data from the MEDIA table, the amount of comments and the likes data for given media_id ?

    
asked by anonymous 11.09.2016 / 01:32

2 answers

3

Your query will look like this: 

SELECT
  (SELECT COUNT(*) FROM comments WHERE media_id='214') as comentarios, 
  (SELECT COUNT(*) FROM media_likes WHERE media_id='214') as likes,
  a.*
from media a where a.id='214';

If you want to pull only 1 id now if you want to just take the where.

--- OBS pos edit --- where are you going to get your $ var

SS

    
11.09.2016 / 01:55
1

I do not know, but I think it would be this way: 

SELECT media.id, 
       media.user_id, 
       media.title, 
       media.description, 
       count(comments.id) quantidade_comentarios, 
       count(media_likes.id) quantidade_likes
FROM media_likes
INNER JOIN comments.user_id=media_likes.user_id AND comments.media_id=media_likes.media_id
INNER JOIN media.id=comments.media_id AND media.user_id=comments.user_id
WHERE media.id=10;
GROUP BY media.id, media.user_id, media.title, media.description

where WHERE media.id=10 instead of 10 the value you want to filter .

    
11.09.2016 / 01:55
Clone select jquery / javascript Error - Icon Font-Awesome