PHP & MySQL Update Database Problems.

Question

I'm trying to change my rating system which only used one table before but now I'm changing it to use multiple tables and I really dont no how to update or insert a new rating into the database and was wondering how to do this using my MySQL tables structure?

Also how do I do this by adapting the new code to my current PHP code which I want to change which is listed below.

First let me explain what my tables do they hold the information when students rate there own teachers I listed what the tables will hold in the examples below to give you a better understanding of what I'm trying to do. Students are only allowed to rate there teachers once.

I provided the two MySQL tables that should be updated which are listed below.

My MySQL tables

CREATE TABLE teachers_grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade_id INT UNSIGNED NOT NULL,
teachers_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
letter_grade VARCHAR(2) NOT NULL,
grade_points FLOAT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);

What the database will hold.

teachers_grades

id  grade_id    teachers_id     student_id     date_created
1       3       2               32            2010-01-23 04:24:51
2       1       32              3              2010-01-23 12:13:58
3       2       32              103            2010-01-23 12:24:45

grades

id  letter_grade    points
1           A+      10
2           D       3
3           B       5

Here is the old PHP code.

// function to insert rating
function rate(){
    $text = strip_tags($_GET['rating']);
    $update = "update vote set counter = counter + 1, value = value + ".$_GET['rating']."";

    $result = mysql_query($update); 
    if(mysql_affected_rows() == 0){
        $insert = "insert into vote (counter,value) values ('1','".$_GET['rating']."')";
        $result = mysql_query($insert); 
    }
}

Old table.

 CREATE TABLE vote (
 `counter` int(8) NOT NULL default '0',
 `value` int(8) NOT NULL default '0'
 );

Answer 1

first , do mysql_escape_string to the parametrs when inserting like :

mysql_real_escape_string($_GET['rating']);

second

you need to get all parameters (from GET or POST) and insert it to the db , the teacher_id ....

now i only see the rating.

Answer 2

Your old table was bit confusing as it seems like it only rates 1 teacher or teachers as a whole.

Now it seems like your new design process requires you to: - store rating and averages of teachers - track historical ratings from students

rating table should look something like

Table: rating

rating_id student_id teacher_id grade_id date_created
 1         101         21         1      2010-01-23 04:24:51
 2         102         21         1      2010-01-23 04:26:51
 3         102         22         2      2010-01-23 04:28:51
 4         103         24         1      2010-01-23 04:44:51

Your code usage:

$rating_value = $_GET['rating']; // Remember to sanitize your inputs
$student_id = $_GET['student_id'];
$teacher_id = $_GET['teacher_id'];
rate_a_teacher($teacher_id, $student_id, $rating_value);

Your method:

function rate_a_teacher($teacher_id, $student_id, $rating_value)
{
    // Find the corrosponding to specified rating value
    $grade_id = find_grade_id($rating_value); //TODO

    $sql = "
        INSERT INTO rating
            ('student_id', 'teacher_id', 'grade_id', 'date_created')
        VALUE 
            ($student_id, $teacher_id, $grade_id, NOW);
    ";

    mysql_query($sql); 
}

I skipped implementation for find_grade_id() for you to fill it in yourself.

The purpose of splitting your calcualted values to individual records is so that you can start do interesting reports, like such:

Find average rating value of each teacher for the past 3 months:

SELECT teacher_id, (SUM(points)/COUNT(rating_id)) AS average_score
FROM rating
LEFT JOIN grades ON grades.id = rating.grade_id
WHERE date_created > SUBDATE(NOW(), INTERVAL 3 MONTH)
GROUP BY teacher_id