mysql query & php - how to achieve this?

Okay, so I have a table, with 20 fields named q1, q2, q3 all the way to q20

These fields contain answers, which is a number out of 10.

I want to basically choose 10 questions at random, and do some maths and the php to display it on page.

Here is what I have so far:

$selector = "10218";
/* Work out how many rows to divide by */
$nr = mysql_query("SELECT * FROM `reviews` WHERE `selector` = '" . $selector . "'");
$nr = mysql_num_rows($nr);
/* Get the total sum of answers for question 1  */
$q1 = mysql_query("SELECT sum(`q1`) AS `sum` FROM `reviews` WHERE `selector` = '" . $selector. "'");
$q1 = mysql_fetch_array($q1);
$q1 = $q1['sum'] / $nr;
echo "q1 avg is " . round($q1);

Basically, we want to do this for 10 questions - at random.

We don't want to have to write this code out 20 times, as it is quite strenuous and we want to do some sort of "array" to contain the questions in, and then a for each loop to loop 10 times with a random question?

We want to e开发者_开发问答nd up with:

q1 avg is 8
q2 avg is 4
q9 avg is 7
q4 avg is 8
q14 avg is 4
q18 avg is 6
q12 avg is 3
q7 avg is 10
q20 avg is 8
q13 avg is 9

How can we go about doing this? If you didn't understand let me know and I'll try rephrase it.

How about this:

// ...
$query = 'SELECT';
for ( $i = 1; $i <= 20; $i ++ ) {
    $query .= ' SUM(`q' . $i . '`) AS `sum' . $i . '`';
}
$query .= 'FROM `reviews` WHERE `selector` = "' . $selector . '"';

$q1 = mysql_query( $query );
$q1 = mysql_fetch_array( $q1 );
foreach ( array_rand( $q1, 10 ) as $col => $sum ) {
    echo $col . ' is ' . $sum . '<br />';
}

By the way, the sum is not the average :)

I don't think what you are trying to do is very clear.

I think you can get the average response as follows:

SELECT AVG(q1) FROM `reviews` WHERE `selector` = $selector;

As suggested in the above comments, you may be better with a a database that looks like this:

create table reviews (
selector ???,
question integer not null, /* e.g. 1, 2, 3, up to 20 */
answer integer not null);

Then you could do something like

SELECT question, avg(answer)
FROM reviews
WHERE selector = $selector
GROUP BY selector
ORDER BY rand()
LIMIT 10;

Though I am not sure of the role of 'selector', and suspect it is bogus.