I think i have built my rating system wrong.

[Jermizzle]

Im going to bare all bcz i have been working on this problem for too long.

I have created a DB in mySQL which stores records of drinks. I have created a form which allows me to enter records. This works fine.

To rate the drinks i have displayed the DB in PHP with dropdown menus to the right so the user can rate the drink.

This is my MySQL table

id int unsigned NOT NULL auto_increment
doc varchar(255)
rating int(11)

In the rating column are dropdown menus which has these options 1 - Poor; 2 - Average; 3 - Excellent

Here is the coding >>>

<?php 
$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable to connect".mysql_error());

mysql_select_db($db) or die ("ERROR: Unable to connect to DB".mysql_error());

$query = "SELECT * FROM menu";

$result = mysql_query($query) or die ("ERROR:Unable to run query".mysql_error());

$count = "SELECT COUNT(*) FROM menu";

$count_result = mysql_query($count) or die ("ERROR: Unable to $count".mysql_error());

#for ($i=1;$i<$count_result;$i++){
#}

#if (mysql_fetch_row($result) > 0 ){
	echo "<form action=formprocess4.php method=POST name=rating>";
	echo "<table border=1 cellpadding=3 cellspacing=3>";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1><b>ID</td>";
		echo "<td><font face=Verdana size=-1><b>Document Title</td>";
		echo "<td><font face=Verdana size=-1><b>Rating</b></td>";
	echo "</tr>";
		while ($row=mysql_fetch_assoc($result)) {
			echo "<tr>";
				echo "<td><font face=Verna size=-1>".$row['doc_id']."</td>";
				echo "<td><font face=Verdana size=-1>".$row['doc']."</td>";

			/*
			$rating1= ' '; $rating2= ' '; $rating3= ' ';
			switch($row['rating'])
			{ 
				case 1: $rating1=' 1'; break;
				case 2: $rating2=' 2'; break;
				case 3: $rating3=' 3'; break;
			}
			*/

			echo "<td><select name=rates><option value= ' '></option><option value=1 	name=' $row[doc]'rw>1 - Poor</option><option value=2 name=' $row[doc]'rw>2 - Average<option value=3 name=' $row[doc]'rw>3 - Excellent</option></select>";				
			echo "</td>";
		echo "</tr>";
		#echo "<br>";
		}
echo "</table>";
echo "<br>";
echo "<input type=submit name=submit value=submit>";
echo "</form>";
#}

echo "<a href=http://localhost/brainstorm/phpFreaks/entryform.php><font face=Verdana size=-1>Back To Form</a>";
?>
</body>
</html>

I think i have done this wrong bcz there is not way to identify were the dropdown menu is postioned on the table. I have currently named the dropdown menus $row['doc']rw. --- I do not know what to name the dropdown menus so that they can be linked to the id or doc name of the document being updated. And also my update query is not working >> this is shown in the code below.

This is the coding i have placed in the table which is supposed to display the ratings.

$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable to connect".mysql_error());

mysql_select_db($db) or die ("ERROR:Unable to connect to DB".mysql_error());

$query = "SELECT * FROM menu";

$result = mysql_query($query) or die ("ERROR:Unable to run query".mysql_error());

$row1 = mysql_fetch_assoc($result);

$doc1 = "$row1[doc];

$update_table = "UPDATE menu SET rating = '$_POST[$doc]' WHERE doc = '$row1[doc]'";

$update_result = mysql_query($update_table) or die ("ERROR: $update_table".mysql_error());

$count = "SELECT COUNT(*) FROM menu";

$count_result = mysql_query($count) or die ("ERROR: Unable to $count".mysql_error());

echo "<h3><font face=Verdana>Table</h3>";

#if (mysql_fetch_row($result) > 0 ){
	echo "<table border=1 cellpadding=3 cellspacing=3>";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1>ID</td></font>";
		echo "<td><font face=Verdana size=-1>Document Title</td></font>";
		echo "<td><font face=Verdana size=-1>Rating</td></font>";
		while ($row=mysql_fetch_assoc($result)){
			echo "<tr>";
				echo "<td><font face=Verdana size=-1>".$row['doc_id']."</td>";
				echo "<td><font face=Verdana size=-1>".$row['doc']."</td>";
				echo "<td><font face=Verdana size=-1>".$row['rating']."</td>";
			echo"</tr>";
		}
	echo "</table>";
#}
echo "<br><a href=http://localhost/brainstorm/phpFreaks/entryform.php><font face=Verdana size=-1>Back To Form</a><br></font>";
?>

What i think i need to do is name the dropdown with a number so i could use that as the position in the table. But i am not sure how to get the database to register the number from the dropdown menu.

Can someone help me please. Thanks

Jermz

[rincewind456]

You can't give a name attribute to an <option> element, and you can't display doc_id as it doesn't exist, your db table structure says id.

Bearing that in mind what you can do is assign the id to the name attribute of the <select> element so that when you retrieve more than one record, each has it's own unique identifier.

then when you submit the form you end up with a $_POST whose key is the id value, and whose value is the rating submitted. it's then fairly simple to loop though them and submit them to the database.

I have changed your rating page code accordingly, I added all the \n's to the code so that when you look at the HTML source it doesn't come out in one long line.

<?php
$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable to connect".mysql_error());

mysql_select_db($db) or die ("ERROR: Unable to connect to DB".mysql_error());

$query = "SELECT * FROM menu";

$result = mysql_query($query) or die ("ERROR:Unable to run query".mysql_error());

$count = "SELECT COUNT(*) FROM menu";

$count_result = mysql_query($count) or die ("ERROR: Unable to $count".mysql_error());

#for ($i=1;$i<$count_result;$i++){
#}

#if (mysql_fetch_row($result) > 0 ){
	echo "<form action=formprocess4.php method=POST name=rating>";
	echo "<table border=1 cellpadding=3 cellspacing=3>\n";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1><b>ID</td>";
		echo "<td><font face=Verdana size=-1><b>Document Title</td>";
		echo "<td><font face=Verdana size=-1><b>Rating</b></td>";
	echo "</tr>\n";
		while ($row=mysql_fetch_assoc($result)) {
			echo "<tr>";
				echo "<td><font face=Verna size=-1>".$row['id']."</td>";
				echo "<td><font face=Verdana size=-1>".$row['doc']."</td>";

			/*
			$rating1= ' '; $rating2= ' '; $rating3= ' ';
			switch($row['rating'])
			{
				case 1: $rating1=' 1'; break;
				case 2: $rating2=' 2'; break;
				case 3: $rating3=' 3'; break;
			}
			*/

			echo "<td><select name=".$row['id']."><option value= ''>				</option>\n
			<option value=1>1 - Poor</option>\n
			<option value=2>2 - Average</option>\n
			<option value=3>3 - Excellent</option>\n
			</select>\n";
			echo "</td>";
		echo "</tr>\n";
		#echo "<br>";
		}
echo "</table>\n";
echo "<br>";
echo "<input type=submit name=submit value=submit>";
echo "</form>";
#}

echo "<a href=http://localhost/brainstorm/phpFreaks/entryform.php><font face=Verdana size=-1>Back To Form</a>";
?>
</body>
</html>

Then looking at your process page, unless you add another field to your db table called number_of_votes or something similar I think you may have a problem, either you are going to have to have a different row in the database for each rating, not really feasible, or with your current code you are going to overwrite the existing rating every time a new one is submitted.

So assuming you add another field you could then do this when you process the ratings

$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable to connect".mysql_error());

mysql_select_db($db) or die ("ERROR: Unable to connect to DB".mysql_error());
function quote_smart($value)
{
	// Stripslashes
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	// Quote if not integer
	if (!is_numeric($value)) {
		$value = "'" . mysql_real_escape_string($value) . "'";
	}
	return $value;
}

foreach ($_POST as $id => $rating){
	if ($id == 'submit') {
		exit();
	} //exits when it gets to submit
	$update_table =sprintf("
	UPDATE menu
	SET rating = rating + %s
	, number_of_votes = number_of_votes + 1
	WHERE id = %s",
	quote_smart($rating),
	quote_smart($id));
	$result = mysql_query($update_table) or die(mysql_error("Query: ".$update_table." failed"));

}

You would then have to divide your rating by the number of votes to get an average rating.

[Jermizzle]

{
	// Stripslashes
	if (get_magic_quotes_gpc()) {
		$value = stripslashes($value);
	}
	// Quote if not integer
	if (!is_numeric($value)) {
		$value = "'" . mysql_real_escape_string($value) . "'";
	}
	return $value;
}

foreach ($_POST as $id => $rating){
	if ($id == 'submit') {
		exit();
	} //exits when it gets to submit
	$update_table =sprintf("
	UPDATE menu
	SET rating = rating + %s
	, number_of_votes = number_of_votes + 1
	WHERE id = %s",
	quote_smart($rating),
	quote_smart($id));
	$result = mysql_query($update_table) or die(mysql_error("Query: ".$update_table." failed"));

}

Can someone please explain this coding to me. It have no idea what is going on.
For example where does the %s value come from?

Thanks

[rincewind456]

As I wrote I suppose I can!

This

function quote_smart($value) 
{ 
    // Stripslashes 
    if (get_magic_quotes_gpc()) { 
        $value = stripslashes($value); 
    } 
    // Quote if not integer 
    if (!is_numeric($value)) { 
        $value = "'" . mysql_real_escape_string($value) . "'"; 
    } 
    return $value; 
} 

Is a function for sanitizing all input to your database you should always use [man]mysql_real_escape_string/man in one form or other before any user input goes into the database.

The second part

foreach ($_POST as $id => $rating){ 
    if ($id == 'submit') { 
        exit(); 
    } //exits when it gets to submit 
    $update_table =sprintf(" 
    UPDATE menu 
    SET rating = rating + %s 
    , number_of_votes = number_of_votes + 1 
    WHERE id = %s", 
    quote_smart($rating), 
    quote_smart($id)); 
    $result = mysql_query($update_table) or die(mysql_error("Query: ".$update_table." failed")); 

}

is building your queries depending on the results from the previous page, for each result set it loops through the $_POST variable and gets an id, rating pair. These are generated from the previous page, the id is the id retrieved from the database that relates to the particular drink in your rating table, and the rating is whatever the user has rated for that drink.

The [man]sprintf/man function is used with wildcards, there are 11 different ones %s is just one of them. What it does is add the values specified after the , in the order that they are placed within the query. So the 1st %s is filled by the quote_smart($rating) value and the 2nd %s by the value in quote_smart($id).

It then continues by running the [man]mysql_query/man for each result in the $_POST variable, if there is only one rating it does one query if there are 200 ratings it does 200 queries.

[Jermizzle]

Thank you v.much Rincewind456. Most appreciated.

[Jermizzle]

I have one more problem. I have used the coding above on another rating system, but i keep on getting an error:

ERROR:Unable To Run UPDATE doc_data SET tech_rating = tech_rating + 'selected', num_of_votes = num_of_votes + 1 WHERE doc_id = 1 Truncated incorrect DOUBLE value: 'selected'

I do not know why this is coming up because i have used the exact same coding, but i have only changed the variables which it is supposed to use.

I have more than one page which rates documents in the table so there will be several rows including Technical Rating and Financial Rating.

Here is the coding for the pages.

this is for the techincal form

$host = "localhost";
$user = "root";
$pass = "mysql";
$db = "brainstorm";

$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable To Connect".mysql_error());

mysql_select_db($db) or die ("ERROR:Unable To Connect To DB".mysql_error());

$query = "SELECT * FROM doc_data ORDER BY session ";

$result = mysql_query($query) or die ("ERROR:Unable To Run Query".mysql_error());

echo "<h3><font face=Verdana>Department: Technical</h3>";

#if (mysql_num_rows($result) > 0) {
	echo "<form name=ratings method=POST action=btable.php>";
	echo "<table border=1 cellpadding=3 cellspacing=3>";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1><b>Session</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Title of Brainstorm Idea</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Inventor Name</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Rating</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Status</b></font></td>";
		echo "</tr>";
	while ($row=mysql_fetch_assoc($result)){
		echo "<tr>";
		echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
		echo "<td><font face=Verdana size=-1><a href=http://www.SHTC.com/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
		echo "<td><font face=Verdana size=-1>".$row['inventor_name']."</td>";
		echo "<td><select name=".$row['doc_id']."> <option value=  selected></option>\n 
			<option value=1>1 - Poor</option>\n
			<option value=2>2 - Fair</option>\n
			<option value=3>3 - Average</option>\n
			<option value=4>4 - Good</option>\n 
			<option value=5>5 - Excellent</option>\n
			</select>\n</td>";
				if ($row['tech_rating'] > 0){
					echo "<td><p align=center><img src=reddot.gif name=already_rated height=10px width=10px></td>";
				} else {
					echo "<td><p align=center><img src=bluedot.gif name=need_rating height=10px width=10px></td>";
				}
		echo "</tr>\n";
	}
echo "</table>\n";
echo "<br>";
echo "<input type=submit name=submit value=submit><br><br>";
echo "</form>";
echo "<font face=Verdana size=-1><a href=http://localhost/brainstorm/index.php>Back to Form</a>";

this is the form for the finance page.

$host = "localhost";
$user = "root";
$pass = "mysql";
$db = "brainstorm";

$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable To Connect".mysql_error());

mysql_select_db($db) or die ("ERROR:Unable To Connect To DB".mysql_error());

$query = "SELECT * FROM doc_data ORDER BY session ";

$result = mysql_query($query) or die ("ERROR:Unable To Run Query".mysql_error());

echo "<h3><font face=Verdana>Department: Finance</h3>";

#if (mysql_num_rows($result) > 0) {
	echo "<form name=ratings method=POST action=btable.php>";
	echo "<table border=1 cellpadding=3 cellspacing=3>\n";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1><b>Session</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Title of Brainstorm Idea</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Inventor Name</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Rating</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Status</b></font></td>";
		echo "</tr>\n";
	while ($row=mysql_fetch_assoc($result)){
		echo "<tr>";
		echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
		echo "<td><font face=Verdana size=-1><a href=http://www.SHTC.com/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
		echo "<td><font face=Verdana size=-1>".$row['inventor_name']."</td>";
		echo "<td><select name=".$row['doc_id']."> <option value= '' selected></option>\n 
			<option value=1>1 - Poor</option>\n
			<option value=2>2 - Fair</option>\n
			<option value=3>3 - Average</option>\n
			<option value=4>4 - Good</option>\n 
			<option value=5>5 - Excellent</option>\n
			</select>\n</td>";
				if ($row['fin_rating'] > 0){
					echo "<td><p align=center><img src=reddot.gif name=already_rated height=10px width=10px></td>";
				} else {
					echo "<td><p align=center><img src=bluedot.gif name=need_rating height=10px width=10px></td>";
				}
		echo "</tr>\n";
	}
echo "</table>\n";
echo "<br>";
echo "<input type=submit name=submit value=submit><br><br>";
echo "</form>";
echo "<font face=Verdana size=-1><a href=http://localhost/brainstorm/index.php>Back to Form</a>";

And here is the coding for the table which displays the data

$host = "localhost";
$user = "root";
$pass = "mysql";
$db = "brainstorm";

$connection = mysql_connect($host,$user,$pass) or die ("ERROR:Unable To Connect".mysql_error());

mysql_select_db($db) or die ("ERROR:Unable To Connect To DB".mysql_error());

$query = "SELECT * FROM doc_data ORDER BY session ";

$result = mysql_query($query) or die ("ERROR:Unable To Run Query".mysql_error());

echo "<table border=1 cellpadding=3 cellspacing=3>";
	echo "<tr>";
		echo "<td><font face=Verdana size=-1><b>Session</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Title of Brainstorm Idea</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Inventor Name</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Technical Rating</b></font></td>";
		echo "<td><font face=Verdana size=-1><b>Finance Rating</b></font></td>";
		echo "</tr>";
while ($row=mysql_fetch_assoc($result)){
		echo "<tr>";
			echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
			echo "<td><font face=Verdana size=-1><a href=http://pws.stonehouse.oilfield.slb.com/SHTC/SER/IP-1/August_2006_Brainstorm/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
			echo "<td><font face=Verdana size=-1>".$row['inventor_name']."</td>";
			echo "<td><font face=Verdana size=-1>".$row['tech_rating']."</td>";
			echo "<td><font face=Verdana size=-1>".$row['fin_rating']."</td>";
			echo "</tr>";
	}
echo "</table>";
echo "<br>";
echo "<font face=Verdana size=-1><a href=http://localhost/brainstorm/index.php>Back to Form</a>";

function quote_smart($value)
{
    // Stripslashes
    if (get_magic_quotes_gpc()) {
        $value = stripslashes($value);
    }
    // Quote if not integer
    if (!is_numeric($value)) {
        $value = "'" . mysql_real_escape_string($value) . "'";
    }
    return $value;
}

foreach ($_POST as $doc_id => $tech_rating){
    if ($doc_id == 'submit') {
        exit();
    } //exits when it gets to submit
    $update_table =sprintf("UPDATE doc_data SET tech_rating = tech_rating + %s, num_of_votes = num_of_votes + 1 WHERE doc_id = %s",
    quote_smart($tech_rating),
    quote_smart($doc_id));
    $result = mysql_query($update_table) or die("ERROR:Unable To Run $update_table".mysql_error());

}

foreach ($_POST as $doc_id => $fin_rating){
	if ($doc_id == 'submit'){
		exit();
	}//exits when it gets submit
	$update_table =sprintf("UPDATE doc_data SET fin_rating = fin_rating + %s, num_of_votes = num_of_votes + 1 WHERE doc_id = %s",
	quote_smart($fin_rating),
	quote_smart($doc_id));
	$result = mysql_query($update_table) or die ("ERROR:Unable To Run $update_table".mysql_error());

}

?>

Can anyone see what is wrong. I think the error is saying that the variable is being saved as text when it is supposed to be a number.

Thanks

[rincewind456]

ERROR:Unable To Run UPDATE doc_data SET tech_rating = tech_rating + 'selected', num_of_votes = num_of_votes + 1 WHERE doc_id = 1 Truncated incorrect DOUBLE value: 'selected'

From the looks of it your trying to add the word selected, which is the first option in your drop down box, to your integer field in the database.

You should have some error checking in your processing page, either forcing the user to vote, by displaying an error message when a rating isn't made or by changing the query building code to cope with an un-rated item.