leanchristmas Posted December 31, 2013 Share Posted December 31, 2013 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: 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 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. Option to submit and save grades into database, as I said it should be saved only for selected ID. 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! Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/ Share on other sites More sharing options...
Ch0cu3r Posted December 31, 2013 Share Posted December 31, 2013 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463419 Share on other sites More sharing options...
Barand Posted December 31, 2013 Share Posted December 31, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463487 Share on other sites More sharing options...
leanchristmas Posted January 1, 2014 Author Share Posted January 1, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463550 Share on other sites More sharing options...
leanchristmas Posted January 5, 2014 Author Share Posted January 5, 2014 @ 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. Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463910 Share on other sites More sharing options...
Barand Posted January 5, 2014 Share Posted January 5, 2014 (edited) Lines 64 - 107 in my example.php file show how to produce a report from the ratings data Edited January 5, 2014 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463912 Share on other sites More sharing options...
leanchristmas Posted January 5, 2014 Author Share Posted January 5, 2014 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? Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463918 Share on other sites More sharing options...
Barand Posted January 5, 2014 Share Posted January 5, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463920 Share on other sites More sharing options...
leanchristmas Posted January 5, 2014 Author Share Posted January 5, 2014 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? Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463922 Share on other sites More sharing options...
Barand Posted January 5, 2014 Share Posted January 5, 2014 If you are going to split then Form Update Report exampleForm.php exampleReport.php exampleUpdate.php Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1463945 Share on other sites More sharing options...
leanchristmas Posted January 6, 2014 Author Share Posted January 6, 2014 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? Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464112 Share on other sites More sharing options...
Barand Posted January 6, 2014 Share Posted January 6, 2014 Do the people giving the grades have to Log in, so their userid is available to the script? Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464118 Share on other sites More sharing options...
leanchristmas Posted January 6, 2014 Author Share Posted January 6, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464123 Share on other sites More sharing options...
Barand Posted January 6, 2014 Share Posted January 6, 2014 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> Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464135 Share on other sites More sharing options...
leanchristmas Posted January 6, 2014 Author Share Posted January 6, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464141 Share on other sites More sharing options...
Barand Posted January 6, 2014 Share Posted January 6, 2014 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> Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464146 Share on other sites More sharing options...
leanchristmas Posted January 6, 2014 Author Share Posted January 6, 2014 Great, I really appreciate your help. Thank you very much once more. Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464153 Share on other sites More sharing options...
leanchristmas Posted January 12, 2014 Author Share Posted January 12, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1464964 Share on other sites More sharing options...
leanchristmas Posted January 19, 2014 Author Share Posted January 19, 2014 Any chance that someone can help me with this ? Thanks in advance. Quote Link to comment https://forums.phpfreaks.com/topic/285001-phpmysql-rating-table/#findComment-1465797 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.