MY SQL / PHP - MERGE TWO ARRAYS AND FIND AVERAGE FOR A USER_ID

[karlchad79]

Before I begin I'm using a version of MySql earlier than v4, so I have tried to solve this issue without using UNION in mysql, via PHP and arrays.

 

I have three tables:

 

user_details:

 

user_id_____ |______ firstname

________________________________

____61______|_______Karl_______

____62______|_______Mark_______

____63______|_______Dave_______

 

 

ratings_student:

 

def_user_id__|______ rating

________________________________

____61______|________2_______

____61______|________3_______

____62______|________5_______

____62______|________4_______

____63______|________2_______

____63______|________5_______

 

and ratings_teacher:

 

def_user_id__ |______ rating

________________________________

____61______|________4_______

____61______|________5_______

____62______|________1_______

____62______|________2_______

____63______|________3_______

____63______|________5_______

 

and I need to get the average score from the two tables listed for each user and list highest first by their firstname and score i.e karl = 20, Mark = 10, Dave = 5.

 

I know how to do this using UNION via SQL but because of the SQL version being earlier than 4 this doesn't work, so I have attempted to use two arrays. My current code is below but I would be soooo grateful if someone could show me the correct code, it's DAY3 and its DRIVING ME MAD 

 

require_once("includes/config.php");

$db_connection = mysql_connect ($DBHost, $DBUser, $DBPass) OR die (mysql_error()); 

$db_select = mysql_select_db ($DBName) or die (mysql_error());

 

$query = "SELECT def_user_id, COUNT(*) AS count_rows, SUM(rating) as sum_rating  FROM ratings_student GROUP BY def_user_id";

$query2 = "SELECT def_user_id, COUNT(*) AS count_rows2, SUM(rating) as sum_rating2  FROM ratings_teacher GROUP BY def_user_id";

 

$rs=mysql_query($query);

$rs2=mysql_query($query2);

$counter = mysql_num_rows($rs);

 

while($row = mysql_fetch_array($rs)){

$def_user_id = $row["def_user_id"];

$count_rows = $row["count_rows"];

$sum_rating = $row["sum_rating"];

 

$array[]=array($def_user_id,$count_rows,$sum_rating);

 

}

 

while($row2 = mysql_fetch_array($rs2)){

 

$def_user_id2 = $row2["def_user_id"];

$count_rows2 = $row["count_rows2"];

$sum_rating2 = $row2["sum_rating2"];

 

$array2[]=array($def_user_id2,$count_rows2,$sum_rating2);

}

 

[Barand]

Can you explain how these are arrived at from your data

 

karl = 20, Mark = 10, Dave = 5.

[Barand]

So you probably don't have subqueries either. Using temporary tables then

 

<?php
mysql_connect('localhost');
mysql_select_db('test');

mysql_query ("CREATE TEMPORARY TABLE tmp_stud
                SELECT def_user_id as user_id, SUM(rating) as tot, COUNT(*) as cnt
                FROM ratings_student
                GROUP BY def_user_id");
mysql_query ("CREATE TEMPORARY TABLE tmp_teach
                SELECT def_user_id as user_id, SUM(rating) as tot, COUNT(*) as cnt 
                FROM ratings_teacher
                GROUP BY def_user_id");
$res = mysql_query ("SELECT u.user_id, u.firstname, 
                    (s.tot + t.tot) / (s.cnt + t.cnt) as average,
                    (s.tot + t.tot) as score
                    FROM user_details u
                    JOIN tmp_stud as s USING (user_id)
                    JOIN tmp_teach as t USING (user_id)
                    ORDER BY score DESC");
while (list($id, $nm, $av, $scr) = mysql_fetch_row($res))
{
    echo "$id $nm $av $scr<br/>";
}
?>

-->
63 Dave 3.7500 15
61 Karl 3.5000 14
62 Mark 3.0000 12