Php/MySql rating table

[leanchristmas]

Hello to everybody great community,

I'm a beginner and I'm only familiar with basics of HTML, CSS and PHP. 

I'm working on small project and I could really use some help. So far I made web site with possibility to enter values into MySql table and to show this values(read it from table) on another page, As I sad would like to do something little more complex (at least is copmlex for me). What I want to do is something like very simple rating system:

1. Choose ID via "select-choice" or similar line. So if I choose ID=JohnDoe, then all the grades I submit should be submitted only for this ID. I know how to make selection choice but I don't know how to connect it to save it to database/table. Only offered ID's should be available to select
2. After ID is selected (JohnDoe) I would like 4 various grades options, each one is graded 1 to 5. Example: hair grade(1-5), clothing grade(1-5), education grade (1-5) etc.
3. Option to submit and save grades into database, as I said it should be saved only for selected ID.
4. On the new web page show results from table. Results should be presented separately for each ID. And in the last column average grade it should be shown.

I have attached screenshot so you can see how I have pictured it. The main problem for me is how to submit grades to table and how to make sure grades are submited onyl to selected ID.

Sorry for my English, hopefully I managed to explain problem.

 

Many thanks in advance!

[Ch0cu3r]

You would setup your user dropdown menu like this

$result = mysql_query('SELECT ID, student_name FROM students');

echo '<select name="student_id">';
// populate the dropdown with usernames, assigning the ID as their value
while($result = mysql_fetch_assoc($result))
{
    echo '<option value="'.$row['ID'].'>'.$row['student_name'].'</a>';
}
echo '</select>';

When the form is submitted you'd get the student id from $_POST['student_id'] variable. You'd use this variable in your queries, eg when adding grades

$student_id      = intval($_POST['student_id']);     // the student input
$hair_grade      = intval($_POST['hair_grade']);     // the hair grade input
$clothing_grade  = intval($_POST['clothing_grade']);  // the clothing grade input
$education_grade = intval($_POST['education_grade']); // the education grade input

mysql_query("INSERT INTO grades SET hair = $hair_grade
                                    clothing = $clothing_grade
                                    education = $education_grade
                                    student_id = $student_id");

If you are updating/deleting a students grades you would reference the student id in a WHERE clause. Example query for updating a a students grade

mysql_query("UPDATE grades SET hair = $hair_grade
                               clothing = $clothing_grade
                               education = $education_grade
            WHERE student_id = $student_id");

Edited December 31, 2013 by ignace
Added missing parentheses

[Barand]

I'd take a different approach from Ch0cu3r, normalizing the ratings so there was a separate table with a rating on each row. This makes it easier to add new new attributes to be rated

 

The tables

+----------------------+                                +----------------------+
|     person           |                                |    attribute         |
+-----+----------------+                                +-----+----------------+
| id  |   name         |                                | id  |  description   |
+-----+----------------+                                +-----+----------------+
|  1  | John Doe       |                                |  1  | Hair           |
|  2  | Jane Brown     |                                |  2  | Clothing       |
+-----+----------------+                                |  3  | Education      |
    |                                                   |  4  | etc            |
    |                                                   +-----+----------------+    
    +--------------------------+                           |
                               |      +--------------------+
                               |      |
                            +----------------------+
                            |     rating           |
                            +------+------+--------+
                            |persId|attrId| rating |
                            +------+------+--------+
                            |  1   |  1   |   2    |
                            |  1   |  2   |   4    |
                            |  1   |  3   |   4    |
                            |  1   |  4   |   3    |
                            |  2   |  1   |   5    |
                            |  2   |  2   |   4    |
                            |  2   |  3   |   4    |
                            |  2   |  4   |   5    |
                            +------+------+--------+

$sql = "CREATE TABLE person (
    persId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO person (name) VALUES
        ('John Doe'),
        ('Jane Brown'),
        ('Harry Potter')";
$db->query($sql);

$sql = "CREATE TABLE attribute (
    attrId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO attribute (description) VALUES
        ('Hair'),
        ('Clothes'),
        ('Education'),
        ('Other'),
        ('Etc')";
$db->query($sql);


$sql = "CREATE TABLE rating (
    ratingId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    persId INT,
    attrId INT,
    rating TINYINT
    )";
$db->query($sql);

For the rating dropdowns I'd give them names like "rating[attributeId]" so it's simple to loop through the post data to add the ratings for each attribute

//
// define the options for the rating dropdowns
//
$ratings = "<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>";

//
// use attribute table to build the rating selections
// and alsoto store the headings for the report
//
$sql = "SELECT attrId, description
    FROM attribute
    ORDER BY attrId";
$res = $db->query($sql);
$ratingSelections = '';
$attrs = array();
while (list($id, $des) = $res->fetch_row()) {
    $ratingSelections .= "<div class='descrip'>$des</div>
            <div class='rating'><select name='rating[$id]'>$ratings</select></div>
            <div style='clear:both'></div>";
    $attrs[$id] = $des; // store heads for table
}

and to process them

if (isset($_POST['persid'])) {
    // id of person being rated
    $persid = intval($_POST['persid']);
    
    // insert a row for each attribute into rating table
    $data = array();
    foreach ($_POST['rating'] as $attrid => $rating) {
        $data[] = sprintf("(%d, %d, %d)", $persid, intval($attrid), intval($rating));
    }
    $sql = "INSERT INTO rating (persId, attrId, rating) VALUES " . join(',', $data);
    $db->query($sql);
}

which give an insert query like

INSERT INTO rating (persId, attrId, rating) VALUES (1, 1, 4),(1, 2, 2),(1, 3, 2),(1, 4, 4),(1, 5, 5)

Complete example attached

example.php

[leanchristmas]

Thank you very much, both of you.

I think I'll go with Barand's solution, but I really appreciate your effort Ch0cu3r.

So I'll try to do it as Barand suggested, assuming I'll get stuck at some point I will ask for help again.

Once more, thank you very much both of you.

[leanchristmas]

@ Barand, 

sorry to bother you again.

I tried to do it your way but I'm little confused, I'm really beginner with a big B, so please have patience.

I'll try to post what I have done so far. 

This is my .php file used to connect to db, it is not viewed in browser:
 

<?php

$server = "localhost";
$username = "xyz";
$password = ""123;



//mysql server connection
if (!$spoj=@mySQL_connect($server, $username, $password))
{
die ("<b>Connection to DB error</b><br>");
}
else{
echo "<b>Connected successfully, right DB.<b>" ;
}
//select DB
if (!mySQL_select_db("xyz", $spoj))
{
die ("<b>Connected successfully.</b><br>");
}
else{
echo "<b>Connection error</b><br>" ;
}

$sql = "CREATE TABLE person (
    persId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO person (name) VALUES
        ('John Doe'),
        ('Jane Brown'),
        ('Harry Potter')";
$db->query($sql);

$sql = "CREATE TABLE attribute (
    attrId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO attribute (description) VALUES
        ('Hair'),
        ('Clothes'),
        ('Education'),
        ('Other'),
        ('Etc')";
$db->query($sql);


$sql = "CREATE TABLE rating (
    ratingId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    persId INT,
    attrId INT,
    rating TINYINT
    )";
$db->query($sql);

?>

This is file that I want to use for selecting grades, of course it is viewed in browser. This is biggest problem for me, can't really understand how to connect choosing User ID and selecting it from DB, also how to connect ratings with DB: 

    <?php
include "spoj.php";
$sql = "CREATE TABLE person (
    persId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO person (name) VALUES
        ('John Doe'),
        ('Jane Brown'),
        ('Harry Potter')";
$db->query($sql);

$sql = "CREATE TABLE attribute (
    attrId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(50)
    )";
$db->query($sql);
$sql = "INSERT INTO attribute (description) VALUES
        ('Hair'),
        ('Clothes'),
        ('Education'),
        ('Other'),
        ('Etc')";
$db->query($sql);


$sql = "CREATE TABLE rating (
    ratingId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    persId INT,
    attrId INT,
    rating TINYINT
    )";
$db->query($sql);
//
// define the options for the rating dropdowns
//
$ratings = "<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>";

//
// use attribute table to build the rating selections
// and alsoto store the headings for the report
//
$sql = "SELECT attrId, description
    FROM attribute
    ORDER BY attrId";
$res = $db->query($sql);
$ratingSelections = '';
$attrs = array();
while (list($id, $des) = $res->fetch_row()) {
    $ratingSelections .= "<div class='descrip'>$des</div>
            <div class='rating'><select name='rating[$id]'>$ratings</select></div>
            <div style='clear:both'></div>";
    $attrs[$id] = $des; // store heads for table
}
?>

<form method="POST" action="upis.php">
	  <Select ID        <br />
          
          <label for="select-choice"></label>
          <select id="selected" name="selected" class="select" value="test">
            <option value="persId">John Doe</option>
            <option value="persId">Jack Jonhson</option>
            <option value="persId">John Jackson</option>
          </select>
	
	<input type="submit" value="Upis" name="upis">
	<br>
</form>

This is .php file I want to use for submitting ratings, not viewed in a browser:

<?php
include "spoj.php";
if (isset($_POST['persid'])) {
    // id of person being rated
    $persid = intval($_POST['persid']);
    
    // insert a row for each attribute into rating table
    $data = array();
    foreach ($_POST['rating'] as $attrid => $rating) {
        $data[] = sprintf("(%d, %d, %d)", $persid, intval($attrid), intval($rating));
    }
    $sql = "INSERT INTO rating (persId, attrId, rating) VALUES " . join(',', $data);
    $db->query($sql);
}
?>

This is .php file used for presenting data from DB, of course viewed in browser. Big problem for me is how to present this data in a browser:

<?php
include "spoj.php";
$query="SELECT * FROM rating";
$q=mysql_query($query);
if (mysql_num_rows($q)==0)
	{
	echo "There is no data in table!";
	}
else 
	{
/// I know I need rows names etc, but not sure how to do it!
?>

I did not paste HTML code, assuming it is irrelevant. Connection with server and DB is OK, but I can't get tables inserted into DB.

I know this is not the best way to design whole process, but it's the best I could make. Hopefully you can help me. 

Thanks.

[Barand]

Lines 64 - 107 in my example.php file show how to produce a report from the ratings data

Edited January 5, 2014 by Barand

[leanchristmas]

Thank you Barand, I see that now.

I'm confused with code. In your first post you posted 4 blocks of code and attached example file. 

I'm not sure where to put which code, how to organize my files? 

How many of them should I have? Only two, one for inserting and one for reporting data?

[Barand]

The blocks of code in the post are extracts from example.php.

 

example.php is a complete application which gives an input form, updates the database and produces a report of the updated data

[leanchristmas]

OK, great. But I'm still not sure in which files to put this code?

How to organize everything? Keep it everything in one file?

[Barand]

If you are going to split then

• Form
• Update
• Report

exampleForm.php

exampleReport.php

exampleUpdate.php

[leanchristmas]

First of all thank you very much Barand, everything is working perfectly.

Also, apology because I did not explain precise everything in the beginning. 

There's one thing I would like to change. My idea was to grade students multiple times, so everyone can give grades via browser, so after 20 people give their grades we can see report total of 20 grade entries.

As I said, after multiple grading, I would like to see average grade for every student, for e.g. "Hair", and in the last column "Total average". As I could understand now is "Average" showing average of total grades, but grades for specific values are randomly presented?

[Barand]

Do the people giving the grades have to Log in, so their userid is available to the script?

[leanchristmas]

No they don't need to log in.

I attached table as an example. Marked with red are multiple grades, which should not be seen on the report, but should be calculated. Marked with black are average grades that should be presented in report.

Hopefully I explain it alright.

 

[Barand]

try this version of exampleReport.php

<?php

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);
//
// use attribute table to build the
// headings for the report
//
$sql = "SELECT attrId, description
    FROM attribute
    ORDER BY attrId";
$res = $db->query($sql);

$attrs = array();
while (list($id, $des) = $res->fetch_row()) {
    $attrs[$id] = $des;
}
  
//
// the ratings report
//
$sql = "SELECT p.persId, p.name, r.attrId, AVG(r.rating) as rating, av.avrate
FROM rating r
    INNER JOIN person p USING (persId)
    INNER JOIN
        (
        SELECT persid, AVG(rating) as avrate
        FROM rating
        GROUP BY persid
        ) as av USING (persid)
GROUP BY r.persid, r.attrid
ORDER BY p.name";
$res = $db->query($sql);
if ($res->num_rows == 0) {
    $report = "<h3>No ratings to report</h3>\n";
}
else {
    $report = "<h3>Ratings Report</h3>\n
        <table id='report' border='1'>\n
        <tr><th>Name</th><th>" 
        . join('</th><th>', $attrs)
        . "</th><th>Average</th></tr>\n";
        
    $currpers = 0;
    $currname = '';
    $currav = 0;
    $newData = array_fill_keys(array_keys($attrs),0);
    
    while (list($pid,$name,$aid,$rating,$av) = $res->fetch_row()) {
        // check if record is for new person
        if ($pid != $currpers) {
            if ($currpers) {
                // only output when we have data
                $report .= "<tr><td>$currname</td><td>"
                    . join('</td><td>', $rateData)
                    . "</td><td>$currav</td></tr>\n";
            }
            $currname = $name;
            $currpers = $pid;
            $currav = $av;
            // set data to new empty array for next person
            $rateData = $newData;
        }
        // store the attribute rating in the array
        $rateData[$aid] = $rating;
    }
    // output row for the last person
    $report .= "<tr><td>$currname</td><td>"
                    . join('</td><td>', $rateData)
                    . "</td><td>$currav</td></tr>\n";    
    $report .= "</table>\n";
}
?>
<html>
<head>
<title>Example ratings</title>
<style type='text/css'>

table#report {
    border-collapse: collapse;
}
td {
    text-align: center;
    width: 80px;
}
</style>
</head>
<body>
    <?php
        echo $report . '<br><hr>';
    ?>
<a href="exampleForm.php">Back to input form</a>
</body>
</html>

[leanchristmas]

Perfect, can't thank you enough.

Just one final little thing, how to put result to 2 decimal as was before? Now is 4 decimal, I tried to find it in a code but couldn't.

Many thanks

[Barand]

Rounded the results in the query to 2 places

<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);
//
// use attribute table to build the
// headings for the report
//
$sql = "SELECT attrId, description
    FROM attribute
    ORDER BY attrId";
$res = $db->query($sql);

$attrs = array();
while (list($id, $des) = $res->fetch_row()) {
    $attrs[$id] = $des;
}
  
//
// the ratings report
//
$sql = "SELECT p.persId
    , p.name
    , r.attrId
    , ROUND(AVG(r.rating), 2) as rating
    , ROUND(av.avrate, 2) as avrate
FROM rating r
    INNER JOIN person p USING (persId)
    INNER JOIN
        (
        SELECT persid, AVG(rating) as avrate
        FROM rating
        GROUP BY persid
        ) as av USING (persid)
GROUP BY r.persid, r.attrid
ORDER BY p.name";
$res = $db->query($sql);
if ($res->num_rows == 0) {
    $report = "<h3>No ratings to report</h3>\n";
}
else {
    $report = "<h3>Ratings Report</h3>\n
        <table id='report' border='1'>\n
        <tr><th>Name</th><th>" 
        . join('</th><th>', $attrs)
        . "</th><th>Average</th></tr>\n";
        
    $currpers = 0;
    $currname = '';
    $currav = 0;
    $newData = array_fill_keys(array_keys($attrs),0);
    
    while (list($pid,$name,$aid,$rating,$av) = $res->fetch_row()) {
        // check if record is for new person
        if ($pid != $currpers) {
            if ($currpers) {
                // only output when we have data
                $report .= "<tr><td>$currname</td><td>"
                    . join('</td><td>', $rateData)
                    . "</td><td>$currav</td></tr>\n";
            }
            $currname = $name;
            $currpers = $pid;
            $currav = $av;
            // set data to new empty array for next person
            $rateData = $newData;
        }
        // store the attribute rating in the array
        $rateData[$aid] = $rating;
    }
    // output row for the last person
    $report .= "<tr><td>$currname</td><td>"
                    . join('</td><td>', $rateData)
                    . "</td><td>$currav</td></tr>\n";    
    $report .= "</table>\n";
}
?>
<html>
<head>
<title>Example ratings</title>
<style type='text/css'>

table#report {
    border-collapse: collapse;
}
td {
    text-align: center;
    width: 80px;
}
</style>
</head>
<body>
    <?php
        echo $report . '<br><hr>';
    ?>
<a href="exampleForm.php">Back to input form</a>
</body>
</html>

[leanchristmas]

Great, I really appreciate your help.

Thank you very much once more.

[leanchristmas]

Once again I need help.

What I need is to change encoding to UTF-8. When I change encoding from UTF-8 without BOM to UTF-8 in Notepad++,  for exampleForm.php, after I submit grades it doesn't reload page again but shows empty page and url bar shows exampleUpdate.php .

Even if we ignore this, I can't change encoding. I tried via phpmyadmin and changed to utf-8 but attributes descriptions are still not showing properly.

Basicily I need to change to utf-8 because attributes description are containing some eastern European characters.

Edited January 12, 2014 by leanchristmas

[leanchristmas]

Any chance that someone can help me with this ?

Thanks in advance.