MYsql advanced querying

[marklarah]

They could make a tv show called that

 

Seriously, I have a linking system, which er...links to things.

There is the ability to rate links, but due to like spamming, and one vote per user, I have the votes in a separate table from the links. I know a link or a join will need to be used, but how to i make a query where it displays the links in the links table descending ordered by the most popular (highest votes).

 

---My query for displaying the rank of a link is---

$result2 = mysql_query("SELECT SUM(rating) FROM `late` WHERE `lid` = '$link'");

$row = mysql_fetch_array($result2);

echo ($row['SUM(rating)'] / $num);

Where $link equals the Link ID and $num equals the amount of rows counted in the votes table where the link ID equals $link. The rating is out of 10

 

---My query for displaying the links normally is---

$result = mysql_query("SELECT * FROM `blue` ORDER BY `date` DESC LIMIT 10");

 

---

 

So how would I join the two to display the links ordered by rank?

 

Help much appreciated

 

Thanks - Mark (ps, cookies to whoever helps

 

 

 

[GingerRobot]

It would help if you posted your database structure. What links the two tables?

[Daniel0]

Try this:

 

SELECT
  blue.*,
  (
    SELECT 
      SUM(l.rating)
      FROM late
        AS l
      WHERE l.lid = blue.id
  ) AS rating 
  FROM blue
  ORDER BY rating DESC
  LIMIT 10;