[SOLVED] Creating A Leader Board

[sintax63]

I have a little web site which I want to create a leader board for.

I have two tables: "users" and "lists"

 

users

id (auto increment)

date

login

password

first

last

email

 

lists

id (auto increment)

date

country

beer

rating

user

 

the "user" field in the 'lists' table matches up to the "id" field in the 'users' table.

 

What I am trying to do is calculate how many entries each user has in the 'lists' table and show the top 10 users (by their login). I am sure something like this is possible but having some trouble getting it started.

 

Any help or suggestions?

 

Thanks in advance!

[obsidian]

Try a query like this:

SELECT u.id, first, last, emal COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
ORDER BY posts LIMIT 10

[sintax63]

$top10 = mysql_result(mysql_query("
SELECT u.id, first, last, email COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
ORDER BY posts LIMIT 10
"),0);

echo $top10;

 

That isn't working... getting a:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource

 

I'm also not sure where the 'AS posts' is coming from as I don't have that in my tables.

[obsidian]

"AS posts" simply is aliasing the result of the count with the name "posts." This will cause the result count column to be called "posts." I think the error may be coming from the fact that I'm trying to order by an aggregate column, which MySQL doesn't like. Try this instead. You should have the following columns returned: 'id', 'first', 'last', 'email', 'posts'

SELECT id, first, last, email, posts
FROM
  (SELECT u.id, first, last, emal COUNT(l.id) AS posts
   FROM users u, lists l
   WHERE u.id = l.user)
ORDER BY posts DESC LIMIT 10

 

Also, you ought to clean up your error checking a tad, too. Assuming you have the above query in a $sql variable, change your code to this:

<?php
$res = mysql_query($sql);
if ($res === FALSE)
{
  die(mysql_error());
}

while ($row = mysql_fetch_assoc($sql))
{
  echo "{$row['first']} {$row['last']} => {$row['posts']} posts<br />\n";
}
?>

[sintax63]

obsidian - thanks for helping me out!

 

What I am running now returns the following:

Query was empty

 

<?php

include("connect.php");

$query="SELECT id, first, last, email, posts 
FROM 
   (SELECT u.id, first, last, emal COUNT(l.id) AS posts 
    FROM users u, lists l 
    WHERE u.id = l.user) 
ORDER BY posts DESC LIMIT 10";

$res = mysql_query($sql);

if ($res === FALSE)
{
  die(mysql_error());
}

while ($row = mysql_fetch_assoc($sql))
{
  echo "{$row['first']} {$row['last']} => {$row['posts']} posts<br />\n";
}

mysql_close();

?>

[obsidian]

You're assigning your query string to a variable named $query. Then, you are calling mysql_query() on a $sql variable that does not exist. That was what I meant by my comment above:

Assuming you have the above query in a $sql variable, change your code to this:

 

You either need to change your mysql_query() call to reference your $query variable, or else you need to change the name of your $query variable to $sql.

[sintax63]

duh - I don't know what I was thinking. 

 

That did the trick as far as getting the string recognized. Now have this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(l.id) AS posts FROM users u, lists l WHERE u.id = l.user) ORDER' at line 3

 

[obsidian]

Wow, I'm having trouble with my keyboard today... here is the corrected SQL:

 

Try this first...

SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
ORDER BY posts LIMIT 10

 

If that doesn't work, try this...

SELECT id, first, last, email, posts
FROM
  (SELECT u.id, first, last, email, COUNT(l.id) AS posts
   FROM users u, lists l
   WHERE u.id = l.user)
ORDER BY posts DESC LIMIT 10

[sintax63]

I put each error under the appropriate chunk of code...

 

SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
ORDER BY posts LIMIT 10

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

SELECT id, first, last, email, posts
FROM
  (SELECT u.id, first, last, email, COUNT(l.id) AS posts
   FROM users u, lists l
   WHERE u.id = l.user)
ORDER BY posts DESC LIMIT 10

 

Every derived table must have its own alias

 

[obsidian]

I put each error under the appropriate chunk of code...

 

SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
ORDER BY posts LIMIT 10

 

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

I am so out of it. Seriously, I apologize for how clunky this has been:

SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
GROUP BY u.id, first, last, email
ORDER BY posts LIMIT 10

[sintax63]

Getting a standard "line 17" error now... could be something with my implementation of your code though.  :-\

 

<?php

include("connect.php");

$top10="SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
GROUP BY u.id, first, last, email
ORDER BY posts LIMIT 10";

$res = mysql_query($top10);
if ($res === FALSE)
{
  die(mysql_error());
}

while ($row = mysql_fetch_assoc($sql))
{
  echo "{$row['first']} {$row['last']} => {$row['posts']} posts<br />\n";
}

mysql_close();

?>

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in index.php on line 17

 

[sintax63]

Scratch that - there was an error in my code. 

 

Seem to work great and I just have to reverse the order and pick away at the info I want displayed.

Thank you so much!

 

For the archive - the working code:

 

<?php

include("../modules/connect.php");

$top10="SELECT u.id, first, last, email, COUNT(l.id) AS posts
FROM users u, lists l
WHERE u.id = l.user
GROUP BY u.id, first, last, email
ORDER BY posts LIMIT 10";

$res = mysql_query($top10);
if ($res === FALSE)
{
  die(mysql_error());
}

while ($row = mysql_fetch_assoc($res))
{
  echo "{$row['first']} {$row['last']} => {$row['posts']} posts<br />\n";
}

mysql_close();

?>