Trying to output Top 10 list from mysql db using php

[gengo]

I have a table that has a score field. Each resource has a unique id, and a user can rate something (only once) 1-5.

 

I am now trying to output a "top10" page - but I don't know how exactly to go about it.

 

I've tried:

 

$fetchtopten = mysql_query("SELECT * FROM tablename ORDER BY AVG(score) DESC LIMIT 10");

 

Assuming the above were correct (and I don't think it is), how would I output this?

 

Ideally, I'd like some sort of loop.

 

while ($fetchtopten has a value)

{

$i = 1;

echo "# $i";

echo (the title [which is a field name] of the number # scored resource);

$i++;

}

 

I just don't know if I am on the correct path, and how to progress any further.  All attempts at "Google U" have failed.

 

Thank you in advance for any and all help.

[Pikachu2000]

I'm assuming that the id of each item voted on is stored in the table, and each item also would get multiple votes, correct?

 

The query string should take the shape of this example

$query = "SELECT resource_id, AVG(score) AS score FROM table GROUP BY resource_id ORDER BY AVG(score) DESC LIMIT 10";

 

And you're on the right track for displaying the results.

$result = mysql_query($query);
while ($array = mysql_fetch_assoc($result)) {
     $i = 1;
     echo "# $i - {$array['resource_id']} - {$array['score']}<br>\n";
     $i++;
}

 

You'll need to substitute your own field names into the query string, etc. and note that I added no error handling at all in there, so you'll probably want to add that as well.

[Psycho]

I was writing this before Pikachu2000 posted, but I'll go ahead and post anyway since the code above to echo the results has an error (the value $i is getting redefined on each iteration of the loop as 1, so the number would not increase)

$query = "SELECT `title`, AVG(`score`) AS average
          FROM `table_name`
          GROUP BY `title`
          ORDER BY average DESC
          LIMIT 10";
$result = mysql_query($query) or die(mysql_error());

$place = 0;
while($row = mysql_fetch_assoc($result))
{
    $place++;
    echo "#{$place} {$row['title']}: {$row['average']}<br>\n";
}

[Pikachu2000]

Yeah, I sure missed that. That's what I get for copying and pasting . . .

[gengo]

Damn, I really hate that I made that "i" mistake!

 

Thanks for the help, I hope to try those tomorrow.

 

Just for clarification:

 

I can echo the "place" in a different div than the "title" and "average".

 

echo "<div id='left'>$ {$place}</div>";

echo "<div id='center'>{$row['title']}</div>";

echo "<div id='right'>{$row['average']}</div><br />\n";

 

Those aren't the div names, just for illustration.

 

Also, Pikachu2000 - a Stars fan?  Me too.

[Psycho]

Just for clarification:

 

I can echo the "place" in a different div than the "title" and "average".

 

You can echo it wherever you want.

[Pikachu2000]

Also, Pikachu2000 - a Stars fan?  Me too.

 

Yeah, although recently I've not been thrilled with them, LOL. The team needs a new owner, IMO.

[gengo]

Thank you both again for all of the help!  It worked perfectly.  I even used it to make a "Latest" page, as well as make a page listing a submissions by category type.

 

RE: Stars - yes, they definitely need a new owner.  I think they have done okay considering the circumstances.

 

Now I'm needing help with the specifics of using AJAX / JQuery to validate a registration form.  Basically, I want the form to indicate if a username has already been taken, or an email address has already been taken (one unique username, one unique email address, etc.) and then not let the person register with the incorrect credentials.

 

I have tried dozens of tutorials and I just cannot seem to get any of them to work correctly.  Some never reset (after trying a name I KNOW is taken, it gives expected error - the error never resets and every name after that is also "invalid"), and some let duplicate names register despite generating the error message.

 

This is my first time working with any of this.

[Psycho]

You need to start a new post as your current problem has nothing to do with this post. But, I will suggest that, at least at first, you do NOT use AJAX. Instead have the user submit the page and then do the validation. If there is an error, then you can redisplay the form with the error.

 

The reason is that even if you do the validation with AJAX you still need to validate again when the user submits the page. Let's a user enters a username and the AJAX returns that the name is available, but the user does not immediately submit the page to create the account. Instead the user leaves the page open for a while. During that time a different user comes in and creates a new account with that same username (it is still available since the first user did not complete the process). When the first user gets around to finally submitting the form, the username is no longer available.

 

So, ensure you have the validation on the PHP processing page and THEN later you can implement some AJAX to give the user some immediate feedback without having to submit the page.