Review system

[nvening]

Hi,

Im currently working on a website which in part dynamically acesses its content from a mysql database using a php script.

However i also want to add a review system to each dynamic page so that visitors can write a review and give a star rating about the content of the page.

Furthermore i would like previous users reviews/ratings to be displayed on the dynamic page.

So far i have the normal content system covered but im having some trouble with the review system.

The easiest way i can think to do it is by using a form to add the review to a seperate table in the database, creating a new row for each review, using the id of the page to match up the reviews with the pages.

However im really not sure if this is the correct way of approaching this and also how to intergrate a star rating in with this.

I have found a useful article concerning the rating system here but cannot work out a way of submitting that, along with the written review, to the table.

Furthermore i am worried about users not being able to save simple formatting such as new paragraphs in the review, if using forms??

Well i think thats it XD, any help/ guideing (doing it all for me ) would be much appreciated, thanks!!

[yamangman]

Table set up sounds good enough to me, but of course there are many many ways it could be done, though can't see why the rating would be a stumbling block? Remember that ASCII chr13 is newline if you want to remember breaking characters.

[nvening]

Well simply because im not too sure how to adapt the rating system to create a value which i can then submit - by the looks of the code from the example i linked to all it can do is link to another page once you make your selection - obviously its gunna need a bit of extra coding but im not sure what to do lol - if anyone can point me in the right direction on how i can adapt it, that would be great

I could i suppose just have a drop down menu with 1-5 written, but that wouldnt be very pretty would it?

Also i take it by the second part of your post all i need to do is select the correct character encoding to enable reviews to have new parargraphs etc?? - or did i get that wrong?

Thanks!!

[nvening]

erm, sorry for the probably stupid question but how do i stop this form from submitting when the page is loaded?? Also is there a way to have a little javascript popup (the grey ones where you have to press ok XD) saying submisson sucessful or whatever??

I only learnt php today

EDIT: also how do i display the average of a variable?

Ive tried echo avg($rating); as suggested but it just throws me an error??

<form action="<?
$username = "******";
$password = "******";
$database = "******";

$rating=$_POST['rating'];
$review=$_POST['review'];

$dbh=mysql_connect ("localhost", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("gkelly_siteopening");

$query = "INSERT INTO restreview VALUES ('$id', '$rating','$review')";

mysql_query($query);

mysql_close();
?>" method="post">
Rating: 
<select name="rating">
<option value="1">1</option>
<option value="2">2</option>
<option value="3" selected="selected">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<br/>
Review: <input type="text" name="review"><br>
<input type="Submit">
</form>

Thanks!!

[Budding]

I can see that you did indeed just recently learn PHP.

PHP is a server side script, and is therefore processed before HTML, which means that the PHP code in the action clause of your form is executed before the form is actually created, which would be why it appears as if your form is submitted before the page is loaded. What you could do, is create a hidden field in your form, which would be submitted to the page once you submit the form, and then enclose your PHP code in an if statement checking whether that POST variable has been submitted. Furthermore, I strongly recommend you separate your PHP from your HTML. So, your code could become something like:

<?
if(isset($_POST['validation'])) {
	$username = "******";
	$password = "******";
	$database = "******";

	$rating=$_POST['rating'];
	$review=$_POST['review'];

	$dbh=mysql_connect ("localhost", "$username", "$password") or die ('I cannot connect to the database because: ' . mysql_error());
	mysql_select_db ("gkelly_siteopening");

	$query = "INSERT INTO restreview VALUES ('$id', '$rating','$review')";

	mysql_query($query);

	mysql_close();
}
?>
<form action="<!-- the name and extension of this page -->" method="post">
Rating: 
<select name="rating">
<option value="1">1</option>
<option value="2">2</option>
<option value="3" selected="selected">3</option>
<option value="4">4</option>
<option value="5">5</option>
</select>
<br/>
Review: <input type="text" name="review"><br>
<input type="hidden" name="validation" value="1"/>
<input type="Submit">
</form>

Replace <!-- the name and extension of this page --> with the name and extension of the same page, unless you want to use some javascript and do a document.form.submit() or something.
Also, you might want to set a cookie to the user so that he/she cannot submit two reviews or something and fix the ratings.

In order to display the average from the table of ratings in your database with the same id (I'm assuming id is the id of the article the review is about), the easiest method would be to calculate the average using SQL. So, you might want to delete the mysql_close() statement from above and add something like this, assuming that "ratings" is the field in your table containing the ratings and "id" is the field in your table containing the id of the content:

<?
$query_avg = "SELECT AVG(ratings) FROM restreview WHERE id=".$id;
$average = $mysql_query($query);
mysql_close();

echo 'This is the average rating: '.$average;
?>

Btw, I haven't checked what I wrote above, so there could be some really silly mistakes.

[nvening]

Cool, the form works now, but is there any way to prevent the post data from creating a new entry when its reloaded?

The other code you gave me didnt work, i located a few errors and modified it as below, however i still cant get it to work, it brings up the error: "Fatal error: Call to undefined function: () in /home/gkelly/public_html/stives/product.php on line 179"

Any ideas? - sorry, i learn as i go along

<?
$username = "******";
$password = "******";
$database = "gkelly_siteopening";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query_avg = "SELECT AVG(ratings) FROM restreview WHERE id="$id;
$average = $mysql_query($query_avg);
mysql_close();

echo 'This is the average rating: '.$average;
?>

Thanks!

[Budding]

To prevent the post data from doing anything when the form is reloaded, just add an unset($_POST); statement in the if(isset()).

You need a . between the id=" and $id. Either that or move the " to the very end of the line before the semicolon.

What exactly is on line 179? Chances are there are just a pair of brackets at a place that shouldn't have any brackets.

[nvening]

Ok, after a bit of research ive got this

<?
$username = "888888";
$password = "888888";
$database = "gkelly_siteopening";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM restreview WHERE id='$id'";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$rating=mysql_result($result,$i,"rating");

$i++;
}

$total_ratings = ($rating);
$total_votes = 7;
$average = $total_ratings / $total_votes;
print("The Average Rating Is: $average");
?>

However i need to find out how to add a + after each rating in the $rating variable and how to find out the number of ratings in the table for that article id for the $total_votes variable.

Would that work?

Thanks!!

[nvening]

Should this calculate the number of rows where the id matches the page id?

$query2="SELECT COUNT(*) FROM restreview WHERE id='$id'";

because instead of getting an int when i try and print the query i get " Resource id #" and then the answer?

[Budding]

Why are you just not just using the SQL AVG function? That would make things much easier.

[nvening]

cause i didnt see your post - ill see if i can get that working

Thanks

[nvening]

Ok, so this is causing an error on the line $average = $mysql_query($query_avg); which is the same error as before

<?
$username = "gkelly_neil";
$password = "hampster";
$database = "gkelly_siteopening";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query_avg = "SELECT AVG(ratings) FROM restreview WHERE id=$id";
$average = $mysql_query($query_avg);
mysql_close();

echo 'This is the average rating: '.$average;
?>

[nvening]

Ok, with this im no longer getting an error but its displaying "Resource id #4 " instead?

<?
$username = "******";
$password = "******";
$database = "gkelly_siteopening";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query_avg = "SELECT AVG(rating) FROM restreview WHERE id=$id ";
$rating = mysql_query($query_avg);

echo $rating;

mysql_close();
?>

[DougMcDonald]

The reason you are getting the resource id#4 returned is because you are echoing the returned dataset of your query, not the variable within.

Try using something like this instead:

//Query
$query_avg = "SELECT AVG(rating) FROM restreview WHERE id=$id ";

//Execute the query
$queryrow = mysql_query($query_avg);

//get first associated row from dataset returned
$data = mysql_fetch_assoc($queryrow);

//echo the rating field, from the row you just returned
echo $data['rating']; 

[nvening]

hmmm, im getting nothing displayed from this? - is there something wrong with the script or do you think something is wrong else where?

[DougMcDonald]

Some useful error checking is to print your variables to page,

After you construct the query variable, echo it back and ensure the SQL is valid, you can paste it into phpMyAdmin to check that if you need to.

Also i notice the only paramter you are passing to the query is the $id variable. Is this being set correctly? returning nothing suggests you are not returning any rows with the query.