mysql database, how to order a table considerig more fields?
Hy, i'm developping a android market. I have an android java client, a mysql db and in the middle php pages to perform query and return json file to the client that will parse it.

Now when my client ask for 10 applications the php page query the db that return the first 20 apps based on the id field. I also have a users table with users information (name, id ecc) and a Feedbak table that contain scores given by user to a specific app (es Users with id 1, app with id 3, score = 4). I don't know how to do this:

1) Application table, has a field named AVARAGE SCORE. So basically this field should contain a value that is the average of all the scores given to this app, so basically i should take all the rows from the feedback table related to the same app.........
ex( app 1, user 2, score 4)
  ( app 1, user 3, score 1)
  ( app 1, user 4, score 2)
  ( app 1, user 1, score 5)

and calculate scores sum (4+1+2+5)\number of raws(4) = 3, and put this value in the app field. But how can i say to mysql to calculate AVARAGE SCORE field value in that way. It's  something that i would like to be done once a day for example, and not every second.......for example at the end of each day mysql calcuate it again and refresh the value.......

2)Applications table has the above avarage scores based on user feedback, and other fields that are important to me like total download numbers, downloads in the last week, how many time users have been looking at the app details and so on........I would like to give a weight  to each of these fields........and generate an overall score..............so that  when an user ask for a 20 apps list.....mysql return a list based (ordered) on this value....


At the moment i don't have this GLOBAL SCORE field on my app table, so i think that the best solution would be to add it to the the table, so basically point 1 and point 2 of my question are exactly tha same thing........

How to calculate the value of a field using a customize function based on values taken from other tables or in the second case from the same one......and how to perform this operation justn once a day......

Tnx a lot

mysql database, how to order a table considerig more fields?
===

Hy, i'm developping a android market. I have an android java client, a mysql db and in the middle php pages to perform query and return json file to the client that will parse it.

Now when my client ask for 10 applications the php page query the db that return the first 20 apps based on the id field. I also have a users table with users information (name, id ecc) and a Feedbak table that contain scores given by user to a specific app (es Users with id 1, app with id 3, score = 4). I don't know how to do this:

1) Application table, has a field named AVARAGE SCORE. So basically this field should contain a value that is the average of all the scores given to this app, so basically i should take all the rows from the feedback table related to the same app.........
ex( app 1, user 2, score 4)
  ( app 1, user 3, score 1)
  ( app 1, user 4, score 2)
  ( app 1, user 1, score 5)

and calculate scores sum (4+1+2+5)\number of raws(4) = 3, and put this value in the app field. But how can i say to mysql to calculate AVARAGE SCORE field value in that way. It's  something that i would like to be done once a day for example, and not every second.......for example at the end of each day mysql calcuate it again and refresh the value.......

2)Applications table has the above avarage scores based on user feedback, and other fields that are important to me like total download numbers, downloads in the last week, how many time users have been looking at the app details and so on........I would like to give a weight  to each of these fields........and generate an overall score..............so that  when an user ask for a 20 apps list.....mysql return a list based (ordered) on this value....


At the moment i don't have this GLOBAL SCORE field on my app table, so i think that the best solution would be to add it to the the table, so basically point 1 and point 2 of my question are exactly tha same thing........

How to calculate the value of a field using a customize function based on values taken from other tables or in the second case from the same one......and how to perform this operation justn once a day......

Tnx a lot
