Who's the Top Three?

DivaX007

win98, localhost, php4+, register globals off, mysql

i have a site that allows you to rate individual photos.

i need to get the top three scores in the database and display them on index.php. what is the simplest way to accomplish this?
this is what i have in the db

post_id|numVotes|votes|rating

i'm obviously a newbie, so if you give me an ultra vague 'weedpacket' answer it's just going to confuse me. 😃

altexis

you can do this with a proper sql query:
SELECT * FROM mytable ORDER BY score DESC
where "mytable" is the table you use to store the scores and "score" is the field holding the scores... "DESC" at the end means in Descending order (first the big scores, then the smaller ones)

also lookup mysql_query() and mysql_fetch_array() in www.php.net

cschotch

if you give me an ultra vague 'weedpacket' answer it's just going to confuse me

😃 😃 😃 😃 😃 😃

DivaX007

Originally posted by altexis
you can do this with a proper sql query:
SELECT * FROM mytable ORDER BY score DESC
where "mytable" is the table you use to store the scores and "score" is the field holding the scores... "DESC" at the end means in Descending order (first the big scores, then the smaller ones)

also lookup mysql_query() and mysql_fetch_array() in www.php.net

i've looked up this and several things on www.php.net and in my humble newbie opinion it is not very 'newbie friendly--it's more for intermediate and up.'

i have no idea how to properly use:

mysql_query() and mysql_fetch_array()

to help me with this.

mikeatrpi

mysql_connect( $db_host, $db_user, $db_pw ) or die('Could not connect!');
mysql_select_db( $db_base ) or die('Can not select database!');
$query = "select * from mytable";
$result = mysql_query($query);
while($myrow = mysql_fetch_array($result)) {
     $myvariable = $myrow['mycolumnname'];
     echo $myvariable . '<br>';
}

cgraz

using mikeatrpi's code (slightly modified in the while loop)

mysql_connect( $db_host, $db_user, $db_pw ) or die('Could not connect!');
mysql_select_db( $db_base ) or die('Can not select database!');
$query = "select * from mytable";
$result = mysql_query($query);
while($myrow = mysql_fetch_array($result)) {
   echo "Field 1: " . $myrow['field1'] . "<br>\n";
   echo "Field 2: " . $myrow['field2'] . "<br>\n";
   echo "Field 3: " . $myrow['field3'] . "<br>\n";
   echo "Field 4: " . $myrow['field4'] . "<br><br>\n";

}

mysql_query executes (or sends to be executed) the query that is defined within parenthesis

mysql_query($query) executes $query (the select statement)

$result is now the variable that executed that query.

mysql_fetch_array takes all the results returned from that query and puts them into an array: in the code above, that array is $myrow.

So my row now contains an array. In the while loop above, I used associative array. HEre are the two ways you can gather the data

$myrow['0'] // note that numeric based index starts at 0, not 1
$myrow['2']
$myrow['3']
$myrow['4']

or using associative array (meaning field names not numbers)

$myrow['field_name1']
$myrow['field_name2']
$myrow['field_name3']
$myrow['field_name4']

(the numbers were just to differenciate the field names. That would assume you had 4 fields in your database: field_name1, field_name2, field_name3, field_name4

Now the code above will execute the query, and while there are still results coming, it will echo your four fields. So if there are 2 results, the output would be

Field1: value
Field2: value
Field3: value
Field4: value

Where value is the value that that field contains. Each block is a seperate row in your database (cause I set it up that way by using two 's after the last field in the while loop.

I know this is confusing at first, but just play around with it. Once you get it, it's really easy.

Cgraz

DivaX007

cgraz and mikeatrpi:

i don't see how your methods will allow me to:

"i need to get the top three scores in the database and display them on index.php."

forgot to say in original post: along with the top three scores i need the title of the photo.

my table contains many many fields, but the relevant ones are perhaps :

post_id|pic_title|numVotes|votes|Rating

RedDragon

Well after connecting to the databse i would use the following code (im not sure about the content of your db fields:

numVotes seems to be the interesting field for the top three

<?php
$query = "SELECT numVotes FROM tablename ORDER BY numVotes DESC LIMIT 0,3";
$result = mysql_query($query);
$topthree = mysql_fetch_array($result);
for($x=0, $y=0;$x<sizeof($topthree);$x++) {
    $y++;
    echo "$y. $topthree[$x]";
}
?>

i have not tested this but it should work

DivaX007

Originally posted by RedDragon
Well after connecting to the databse i would use the following code (im not sure about the content of your db fields:

numVotes seems to be the interesting field for the top three
<?php
$query = "SELECT numVotes FROM tablename ORDER BY numVotes DESC LIMIT 0,3";
$result = mysql_query($query);
$topthree = mysql_fetch_array($result);
for($x=0, $y=0;$x<sizeof($topthree);$x++) {
 $y++;
 echo "$y. $topthree[$x]";
}
?>
i have not tested this but it should work [/B]

i'm sorry. i should have said i need the top three according to: Rating

i substituted Rating for numVotes in you example and got this error:

7
Notice: Undefined offset: 1 in C:...\htdocs\index.php on line 23
2.

line 23 is:
echo "$y. $topthree[$x]";

any ideas?

Weedpacket

$x is a red herring; it should be 0 (the 0th element in $topthree, being the 0th field in the result). The 0th element is the only field in $topthree, so there's no $topthree[1].

So to fix:

$query = "SELECT numVotes FROM tablename ORDER BY numVotes DESC LIMIT 0,3";
$result = mysql_query($query);
$count_rows = mysql_num_rows($result);
for($y=1; $y<=$count_rows;$y++) {
$topthree = mysql_fetch_array($result);
    echo "$y. $topthree[0]";
}

Where $y runs from 1 to $count_rows inclusive so that you get rankings "1,2,3" instead of "0,1,2".

DivaX007

Originally posted by Weedpacket
$x is a red herring; it should be 0 (the 0th element in $topthree, being the 0th field in the result). The 0th element is the only field in $topthree, so there's no $topthree[1].

So to fix:
$query = "SELECT numVotes FROM tablename ORDER BY numVotes DESC LIMIT 0,3";
$result = mysql_query($query);
$count_rows = mysql_num_rows($result);
for($y=1; $y<=$count_rows;$y++) {
$topthree = mysql_fetch_array($result);
 echo "$y. $topthree[0]";
}
Where $y runs from 1 to $count_rows inclusive so that you get rankings "1,2,3" instead of "0,1,2". [/B]

i substituted Rating for numVotes because:
"i need to get the top three highest by: Rating in the database and display them on index.php."

by top three, i mean the three highest according to: Rating
forgot to say in original post: along with getting the top three by:Rating i need to get the pic_title of the photo.

when i run the code, i get this:
1. 72. 63. 6
which is really:
1.7 2.6 3.6

these are the first three numVotes in the table.

my table contains many many fields, but the relevant ones are perhaps :

post_id|pic_title|numVotes|votes|Rating

Weedpacket

Then replace "numVotes" with "Rating, pic_title" in the query! I'd've thought that would have been blisteringly obvious. How much of your thinking do you want us to do for you?

DivaX007

Originally posted by Weedpacket
Then replace "numVotes" with "Rating, pic_title" in the query! I'd've thought that would have been blisteringly obvious. How much of your thinking do you want us to do for you?

"Then replace "numVotes" with "Rating, pic_title" in the query!"

i have done this. you should have noted from the previous post:
"i substituted Rating for numVotes because:
"i need to get the top three highest by: Rating in the database and display them on index.php."

if you don't want to assist someone who is new at this then don't.
showing your behind on a forum is not going to make you seem impressive. as a matter of fact, it makes you seem as though you are lacking in other areas...

Weedpacket

So why does the query still say "numVotes", and why do you say "these are the first three numVotes in the table"? In short you have not "done this".

It's not a matter of being "new" at something. It's a matter of actually paying attention to what you are doing. We're talking about a fundamental requirement of being able to program - intelligent thought. If you're unable to display it then I can't help you, no matter how much I might want to assist.

DivaX007

Originally posted by Weedpacket
So why does the query still say "numVotes", and why do you say "these are the first three numVotes in the table"? In short you have not "done this".

It's not a matter of being "new" at something. It's a matter of actually paying attention to what you are doing. We're talking about a fundamental requirement of being able to program - intelligent thought. If you're unable to display it then I can't help you, no matter how much I might want to assist.

"So why does the query still say "numVotes", and why do you say "these are the first three numVotes in the table"? In short you have not "done this".

i changed numVotes in my
ACTUAL CODE

"why do you say "these are the first three numVotes in the table"

because that's what it's still pulling from.

"In short you have not "done this".

wrong

you are not require to assist me. actually your 'help' is counterproductive. you are dismissed. goodbye.

Weedpacket

Well, if

[color=red]SELECT Ranking, pic_title FROM tablename ORDER BY Ranking DESC LIMIT 0,3[/color]

is still pulling numvotes and not rankings, then there is something seriously wrong with your database. Sorry I won't be able to help you fix it.

cgraz

Posted by DivaX007

i've looked up this and several things on www.php.net and in my humble newbie opinion it is not very 'newbie friendly--it's more for intermediate and up.'

i have no idea how to properly use:

mysql_query() and mysql_fetch_array()

I explained it to you as best I could in reply to those words:

i don't see how your methods will allow me to:

My "methods" were an explanation of how to use something that you will need in order to achive your goal. I agree with weedpacket; this forum is so that people can learn (including those who post responses), but by just handing out code to people they keep coming back as we've seen on this board.

If there was a misunderstanding or whatever, it's not my place to step in, but if you want somone to write the code for you, go to www.hotscripts.com under image galleries . You'll find plenty of photo gallery scripts that do what you want and more. If you want to learn, don't tell people "i want this. tell me how to do it"

I'm sure most of the people who respond to posts (including weedpacket) are more than willing to help, but it's frustrating when people demand things. If something is over your head, I'm sure someone will break it down for you (like i did in my post) for you to understand.

I'm sure you dont' like my post: but i'm going to answer every single question you could possibly have to complete this project. Hows that sound?

SELECT
ORDER BY

and, if my explanation wasn't sufficient:
mysql_query()
mysql_fetch_array()
(scroll down. the writing on the gray boxes are user-comments, and to my understanding ones that have been screened to ensure 100% accuracy. If you don't understand, scroll down and read those comments.

Now I understand those are from PHP.net and mysql.com, and may be hard to understand (we all started somewhere), so here are tutorials that will explain everything you need in depth, and much more.

MySQL/PHP Tutorials
http://www.webmasterbase.com/article/530/8
Creating a Photo Gallery - http://www.zend.com/zend/tut/photogallery.php
http://www.devshed.com/Server_Side/PHP/DB_Basics/page1.html
http://www.edevcafe.com/viewcat.php?id=40
http://phpdeveloper.org/tutorial.php?PHPSESSID=381bf946ef4a04bb05640c2bb0cf4466
http://www.phppatterns.com/index.php/link/category/1/
http://sqlcourse2.com/intro2.html

Read all that--if you run into any trouble or come across something you don't understand, atleast one person will answer your question and sit down and explain it for you. We all started out knowing nothing, I don't mind helping if you have any questions; but I don't think people should come to phpbuilder expecting to be spoonfed.

If you disagree that you're beeing spoonfed, then follow those links and you'll learn everything you need to accomplish your photo gallery script.

PHOTO GALLERY TUTORIALS
http://hotwired.lycos.com/webmonkey/01/27/index3a.html?tw=programming

PHPBUILDER posts pertaining to question (just to name a few)
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10221678&highlight=top+three
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10220460&highlight=top+three
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10220371&highlight=top+three
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10222826&highlight=image+gallery

TO DOWNLOAD
http://www.chezgreg.net/coppermine/
or like i mentioned earlier - hotscripts.com

That should get you going.

Cgraz

cgraz

Whoops, almost forgot.

www.google.com

here: already got you started
http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=image+gallery+script+php

Cgraz

Code_guy

I agree with weedpacket and cgraz. If you want someone to write code for you, you should hire someone.

This is something you might want to give some more thought to.
Since you find it difficult to learn by reading documentation, and are annoyed by people who explain to you how to do something rather than handing you the solution. If you’re going to be rude to someone who is trying to help you, I don't think there is much hope of you ever becoming a competent programmer.

Most arrogant quote i've seen on phpbuilder:

your 'help' is counterproductive. you are dismissed. goodbye.

I think you owe weedpacket an apology.

mikeatrpi

While wearing my asbestos suit, I inferred the following based on the collective help of the previous posters:

$query = "SELECT ranking, pic_title FROM tablename ORDER BY ranking DESC LIMIT 0,3";
$result = mysql_query($query);
$count_rows = mysql_num_rows($result);
for($y=1; $y<=$count_rows;$y++) {
$topthree = mysql_fetch_array($result);
    echo "<br>$y. $topthree[0] $topthree[1]";
    /*In the select query, ranking is the first item, hence it is 
$topthree[0], since the 0th index is the first element of an array.  

When weedpacket added pic_title to the query, it is accessible by 
the index of 1, the second index to the array.  You can also 
access the data by $topthree['ranking'] and $topthree['pic_title'] 
instead of 0, 1 respectively.*/
}