Two tables, sort by num rows; combine? Hard to be more elaborate in a thread title.

Discussion in 'MySQL' started by Infranight, Dec 25, 2011.

0
  1. #1
    I have two tables, for this example, I'm going to call them "quotes" and "likes"
    "quotes" contains multiple entries (each one like a famous quote or something of that nature).
    The site allows users to click a like button and as such, an entry is inserted into the "likes" table with the corresponding ID from the "quotes" table so it can traced back.
    I wish to write a query that sorts the content of the "quotes" table by the number of likes it has in the "likes" table - is that possible? If so, how?
    Many thanks and happy holidays (the second part applies even if the problem remains unresolved)
     
    Last edited: Dec 25, 2011
    Infranight, Dec 25, 2011 IP
  2. filegrasper

    filegrasper Active Member

    Messages:
    493
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    73
    #2
    i will workout on this after i came from sleep. so if the problem doesnt get solved later by anyone pm me i am happy to help you :)
     
    filegrasper, Dec 25, 2011 IP
  3. Basti

    Basti Active Member

    Messages:
    625
    Likes Received:
    6
    Best Answers:
    3
    Trophy Points:
    90
    #3
    Dont know how you store the likes, multiple same id rows or if a new like comes, updating the corresponding row.

    Something like this for the first case, selecting 10 rows ( LIMIT 10 )
    
SELECT a.id, a.title, a.description, COUNT(b.id) AS num_likes FROM table_quotes AS a, table_likes AS b WHERE a.id = b.id ORDER BY num_likes DESC LIMIT 10

while loop to spit out the data from the qote and num_likes
    PHP:
    Similar for the second case,
    
SELECT a.id, a.title, a.description, b.num_likes FROM table_quotes AS a, table_likes AS b WHERE a.id = b.id ORDER BY b.num_likes DESC LIMIT 10

while loop to spit out the data from the quote and num_likes
    PHP:
     
    Basti, Dec 26, 2011 IP