Jump to content

Php/MySql rating table


leanchristmas

Recommended Posts

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!

post-166926-0-23128700-1388481938_thumb.png

Link to comment
Share on other sites

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 by ignace
Added missing parentheses
Link to comment
Share on other sites

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

Link to comment
Share on other sites

@ 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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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>


Link to comment
Share on other sites

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 by leanchristmas
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.