Sql update within While loop

[sstalk]

Hi,

I'm working on a ratings and rankings system within my photo album using PHP & MySQL. The code works fine, but I just want to know if this is a poor SQL practice. Second thing, if the rating system gets popular, is this script going to suffer and waste a lot of processing to the DB?

Ok, here is how the process goes.

Each time a user voted for an album, it will INSERT or UPDATE to the "rating table". The table will have 5 fields, 'id', 'album id', 'new rated value', 'previous rating', 'current ranking' & 'previous ranked'.

So, after the query then will start a while loop with the limit of 10, and UPDATE the "rating table" within the loop. Because, I want to show the ranking in real time. And do a while loop each time when a user voted for an album. But I am just worried if this script get popular, and many users vote at a same time. is this script going to suffered?

Hope someones can share an idea or know about a great way or solution. Many thanks

[joelgreen]

Do you need detailed ratings (how many users rated 5, how many 4, etc) or just summary (100 users rated, average rating 4.5)?

[sstalk]

Hi Joelgreen, thanks for your replied.

This would be a good idea, do you have a good script of this? Maybe I will add this later.

Meantime, the one question I have about is if this scripts gets popular will it going to suffer and waste a lot of processing to the DB?

My algorithm goes like this.

1. When a user vote for an album;
2. The script UPDATE the rating table with the new rating values.
3. After that, it will do another query like SELECT * FROM table ORDER BY current_rating DESC.
4. Start UPDATE all the rows within the WHILE LOOP. So, it will UPDATE all the current ranking with a new ranking and previous ranking value.

My question is, will this waste a lot of processing to the DB? Because each time a user voted the album it will do a process like above. What if I have thousands of records, and there are many users voting for the album at a same time?

[joelgreen]

Lest say:
1. user voted for album AAA.
2. album record updated with new ranking
3. select 10 records to show as TOP 10 somewhere on the site

I do not understand why you need step 4. What does it do? Why do you need to update 10 records when user voted for 1 album only?

[joelgreen]

What if I have thousands of records, and there are many users voting for the album at a same time

That is why you need to update record in sql query, something like

update tablename
set tbl_votes = tbl_votes + 1,
tbl_points = tbl_points + $points,
tbl_rank = tbl_points / tbl_votes

will this waste a lot of processing to the DB

In order to minimize server/db load make sure appropriate indices added to the tables.

Big tables with no indices added may slow down server significantly

[sstalk]

Hi,

Actually, there is something more I want to show in the site instead of just Select 10 records to show as TOP 10.

The ranking output is like this.

+-------+--------------------------------------------+----------+----------+
¦ Rank ¦ Movement (Previous Ranking) ¦ Album ¦ Rated ¦
+-------+--------------------------------------------+----------+----------+
¦ 1 ¦ UP (2) ¦ AAA ¦ 4.9 ¦
+-------+--------------------------------------------+----------+----------+
¦ 2 ¦ DOWN (1) ¦ BBB ¦ 4.8 ¦
+-------+--------------------------------------------+----------+----------+
¦ 2 ¦ -- (2) ¦ CCC ¦ 4.8 ¦
+-------+--------------------------------------------+----------+----------+
¦ 3 ¦ DOWN (2) ¦ DDD ¦ 3.8 ¦
+-------+--------------------------------------------+----------+----------+

So, I needed step 4 to UPDATE all the rows within the WHILE LOOP. with all this previous ranking, and also the previous value if the Album has the same rated value. Let say if there is 2 album has the same rated value they have the same ranking.

[joelgreen]

I would update only one row each time a user votes, and would calculate those "UP/DOWN" in php just before displaying. I think PHP calculations would work faster than updating top 10 records in database, and database load would decrease.