Re: [PHP-DB] Ranking in MySQL
David, I have a MySQL table called images, each with a rating from 1-10. The column is a decimal(10,2). For simplicity's sake, let's say i have the following: CREATE TABLE images ( image_id int not null auto_increment, image_path varchar(255) not null, rating decimal(10,2) not null, primary key(image_id) ); What I'm looking to do is be able to pull from the database the ranked position each image is in. I.e. this image is ranked 70 out of 121 =SQL has no ranking method. However you can (1) select data/rating values out from the table, (2) count how many rows/ratings appear in the table, (3) sequence the result set by rating to rank them. =SELECT data from the table and have it sequenced according to the value in rating: SELECT ... ORDER BY rating DESC; =the DESC is because ranking usually means highest-first! =When the record/resultset hits PHP, you can use MYSQL_NUM_ROWS() to find the number of rows returned from the database - and answer the this image is ranked ... out of ??? question. =Now you can run a FOR loop, to extract each row from the result set (from 1 to 'num_rows'). Each cycle through the loop, the for loop counter will answer the this image is ranked ??? out of ... question. =Ok? =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Ranking in MySQL
I have a MySQL table called images, each with a rating from 1-10. The column is a decimal(10,2). For simplicity's sake, let's say i have the following: CREATE TABLE images ( image_id int not null auto_increment, image_path varchar(255) not null, rating decimal(10,2) not null, primary key(image_id) ); What I'm looking to do is be able to pull from the database the ranked position each image is in. I.e. this image is ranked 70 out of 121 Any suggestions on a MySQL query that could do this? Thanks, David Balatero [EMAIL PROTECTED]
Re: [PHP-DB] Ranking in MySQL
David, Your question is a little bit unclear. First, if it is rank, you do not need decimal, but integer (tinyint will do it). Then you need a little bit of statistics to rank the picture against the others, so, anytime each picture is ranked, you have to run the statistics and update the table (update images set rating=... where image_id=...). Then, in order to post the rank, just extract max(rating) (something like select max(rating) as ... from images) and then extract the rank of the image (select decimal from images where image_id=...) and you'll have what you need. Hope this helps and I did not write to many stupid things:) Mihai On Fri, 9 Aug 2002, David Balatero wrote: I have a MySQL table called images, each with a rating from 1-10. The column is a decimal(10,2). For simplicity's sake, let's say i have the following: CREATE TABLE images ( image_id int not null auto_increment, image_path varchar(255) not null, rating decimal(10,2) not null, primary key(image_id) ); What I'm looking to do is be able to pull from the database the ranked position each image is in. I.e. this image is ranked 70 out of 121 Any suggestions on a MySQL query that could do this? Thanks, David Balatero [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
