I have a table on MySQL server that contains several fields (id, ListingID, Scores),being utilised via a php page.
The 'scores' column has the values stored as '1,2,3,4,5,6'.
When a rating is added, people select their rating (out of ten) for a particular listing and this is then stored as below:
ID - autoincrement field for a unique id for each rating.
ListingID - inserted as per the listing a rating is submitted against
Scores - there are 6 criteria to score on, for each criteria the score is submitted as'[score],' (score followed by a comma (except the last one). So if you rated the criteria as follows:
Criteria 1 = 7
Criteria 2 = 5
Criteria 3 = 3
Criteria 4 = 6
Criteria 5 = 9
Criteria 6 = 1
this would be entered into the column as 7,5,3,6,9,1
What I am having problems with is getting the scores back out as a total - I want to pull out all the scores for a listing, then provide an average score for each criteria.
So the final result is:
Criteria 1 = 5 (average score)
Criteria 2 = 3 (average score)
Criteria 3 = 4 (average score)
Criteria 4 = 9 (average score)
Criteria 5 = 10 (average score)
Criteria 6 = 5 (average score)
So if I had 2 ratings stored as below
I could pull these out and add each one to get a total as below
7+1 = 8
9+5 = 14
Then divide by the number of ratings to get a total average
I figure I need to do some sort of php explode into an array but am really stuck on getting this sorted out - I hope this makes sense and someone can help with this as it is driving me crazy!