SQL, PHP and arrays...?

[raaboo]

Hi,

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
7,9,5,8,6,4
1,5,6,9,8,7

I could pull these out and add each one to get a total as below
7+1 = 8
9+5 = 14
etc...

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!

[chrishea]

Your thinking is correct! If you have a field that is a comma-delimited string then explode it and that will create an array of the strings. Then just use a While loop to go through and add the fields together. As you go through them you'll also be counting how many so at the end you'll have the divisor as well.

[raaboo]

okay, so I can explode it and make it in to an array - my issue is that if there are several rows returned - how do I get each row in to an array and then go through adding all the array value 1's and the array value 2's etc...???

this is the bit I am stuck on now (at least I had the right start!)

My thinking is...get each row's results >>> then Explode each rows column result into an array, then add all array value 1, array value 2 etc.... - how would I achieve this? (I have practically no knowledge of exploding sql data into arrays)

[chrishea]

If you don't have a copy of the PHP documentation you need to get one. If you aren't sure how explode works, that will tell you.

You can only go through one row from the db at a time. After you process a criteria for a specific row, you should have the total for that criteria and the number of items. You could then add that to a global total for that criteria and a global count for the number of items for that Criteria. You do that for every criteria in every row. At the end, you will have two global counters for each criteria: the total amount and the total number of items. You divide one into the other and you have an average.

[raaboo]

Hi chrishea,

I should have made this a bit clearer really - I din't have access to the code at time of post, but do now:

include 'config.php';
$hotratingid = 48119; // variable for testing purposes
$ratesql="SELECT jsjrratings.ratings, jsjrcomments.pid FROM jsjrcomments INNER JOIN jsjrratings ON jsjrcomments.id = jsjrratings.reviewid WHERE (((jsjrcomments.pid)=$hotrateid))"; 
$db_selected = mysql_select_db($dbchoice,$conn);
$result = mysql_query($ratesql,$conn);

while($row=mysql_fetch_row($result))
{ 
$ratings = explode(",",$row[0]);
print_r ($ratings);
  }

This will output the following type of result:

Array ( [0] => 7 [1] => 3 [2] => 8 [3] => 1 [4] => 7 [5] => 2 ) 
Array ( [0] => 6 [1] => 2 [2] => na [3] => na [4] => 5 [5] => 9 ) 
Array ( [0] => 3 [1] => 4 [2] => na [3] => 8 [4] => 10 [5] => na )

the bit confusing me is how to get all the Array => [0] added together, then all the Array =>[1] added together and so on....

As you can see - not all the values will always be numeric so it causes a bit of a problem as I need to discount these from the overall average.

so in the above example - I want to get all the Array =>[1], add them togethr and divide by the total (7+6+3 = 16 / 3 = result), however for Array => [2] this would be 8 / 1 (as there are 2 'na' results).

It is really complicated to me and I know there is going to be some extremely easy and simple solution, but I have looked through documentation and cannot seem to find something that can be put together to make this work....

Any ideas how, or even if this is possible?

I have been thinking along the lines of using an 'if' statement to see if it is 'na', if not, add it to the total and add '1' to the average divisor, if it is 'na', do nothing and move on - but how would this be coded?

[diafol]

If you've only got 6 criteria, why not give each a field? You can then do a MySQL AVERAGE on each field, without all the exploding and looping. That's just bonkers.