mysql table selection problem

[cogga28]

my problem is that i have two tables (test and result) both of which have a field called score, but both score fields hold different values one holds all the question answer scores (test), the other holds the score for the users answers(result) i need to be able to select them individually so i can add the scores together to get a percentage.

the format the scores are in the database is like 1,2,2,3,4,

plan is to:-

1. select score from test

2. load into a array

3. add values together

4. store as $tscore;

5. select score from result

6. load into array

7. add values together

8. store as $rScore

9. deduct $rScore from $tScore

10. multiply by 100

11. store value as $total
    [php]
    $qry = mysql_query(“SELECT Result., Test.
    FROM
    Result INNER JOIN Test
    ON Result.testID = Test.testID
    WHERE userID = ‘$_REQUEST[selectedUser]’;”);
    while($row = mysql_fetch_array($qry)){

    			$_SESSION['score'] = $row['score'];
    			$arrScore=(explode(',', $_SESSION['score'], -1));
    			$tScore =array_sum($arrScore);
    

[/php]
the code above is ok but i cant distinguish between the two fields how can i fix this??

[phphelp]

You can do steps 1-10 in one sql query. If your only goal in this code is to calculate this number for each record, here is how your query will look:

[php]$qry = mysql_query(“SELECT IF(sum(Test.score) > 0,(sum(Test.score) - sum(Result.score)) * 100 / sum(Test.score), 0) as Prc
FROM
Result INNER JOIN Test
ON Result.testID = Test.testID
WHERE userID = '”.$_REQUEST[‘selectedUser’]."’;");[/php]
The resulting number will be returned in the column named Prc

And if you want your query return 2 scores from different tables, you can use aliases:

SELECT Test.score as score1, Result.score as score2 FROM ...

[cogga28]

as i needed all of the values from the tables i used the alias thanks for the help i hate it when you get a mental block like that lol