Dear Adam,
I have faced the same problem a while ago and I didn't receive any kind of
answer regarding doing this with MySQL in any discussion list at all. I had
to create a function using PHP to do the hard job and the ranking position
was created on the fly (using your example, I wouln't need table
album_ranks). Yes... I agree with you. It's extremely ineffcient but at
least it works (my DB has got more than 20.000 entries). The idea is
something like this:
send query to MySQL to get * from table album_votes ordered by votes
(descending order);
Create a var to hold the ranking position starting at 1
($ranking_position=1);
loop to circle thru the resulting array until you find the album_id that you
want, incrementing $ranking_position by 1 before the end of the loop. When
you find the correct album_id just break the loop and $ranking_position will
be equal to the ranking position of the album that you want.
I believe this is a little bit different from what you are doing, right?
Well... It seems to be the only way to do it. Sad but true. :///
PLEASE ANY OF YOU CORRECT IF I'M WRONG! This will help me a lot also. :)))
BR,
Fábio Ottolini
-----Original Message-----
From: Adam W [mailto:[EMAIL PROTECTED]]
Sent: Saturday, March 03, 2001 2:18 PM
To: [EMAIL PROTECTED]
Subject: Index / Rank in table
Hi
I'm designing a site where people can vote for their favourite albums using
PHP3 to query the database.
It's the first database I've 'designed', and I'm sure that there are
improvements I could make to the way I have organised the data.
For example, I'm looking for a way to make the 'album_ranks' table
superfluous... e.g. when I query the database and return the 'album_id',
'artist', 'title' and 'year' fields from the 'album_details' table, I would
like to be able to automatically return a 'rank' based on the 'album_id'
position in the 'album_votes' table. Is it possible to do this? Currently,
everytime an album is voted for, and the 'votes' column is updated in
'album_votes', I am repopulating the whole of the 'album_ranks' table on the
basis of the updated vote... considering that this table contains in excess
of 20,000 elements, this seems to me to be extremely inefficient.
I have read what I can on 'indexes' (I'm sure that the solution lies there
somewhere), but I'm still in a fog of ignorance on the subject. Could anyone
point me in the right direction????
Yours gratefully
Adam
[ example structure below... ]
SELECT * FROM album_details
-----------------------------------------------
album_id | artist | title | year
-----------------------------------------------
5 | the beatles | revolver | 1966
17 | nirvana | nevermind | 1991
etc etc...
SELECT * FROM album_votes
-----------------------------------------------
album_id | votes
-----------------------------------------------
5 | 16382
17 | 4732
etc etc...
SELECT * FROM album_ranks
-----------------------------------------------
album_id | rank
-----------------------------------------------
5 | 1
17 | 2
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
