Review system

[nvening]

Ive echoed it back and i get "SELECT AVG(rating) FROM restreview WHERE id=1" which seems to be correct? (the page id is indeed 1) - erm...?

EDIT: ran it on phpmyadmin and it says You have to choose at least one column to display so something is wrong, ill try and work it out! As far as i can tell its suggesting that the rating collum does not exist, but it does, spelled the same, and it has three values where the id is 1.

[DougMcDonald]

Ah yes, you'll need to select a column alias,

If you run that query in something like Management Studio, you will return a column with no title,

re-write your query like this:

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

This will give your column the name avg_rating and should sort it (obviously you can call it anything you liike)

Then to reference the column, you'll obviously need to change to

echo $data['avg_rating']; 

or whatever you've called it, hope it helps

[nvening]

ah, thats brilliant thanks! ive added a round in there too to bring it to an int

-stay tuned for my next problem

[DougMcDonald]

No problem, glad to hear that sorted things out for you.

Interestingly, I looked at this because I am creating a similar system myself as a rating thing (same kind of thing as the ratings thing on amazon)

I notice you commented on the way of selecting a value from 1-5 without having radio buttons or a select/option list.

I'm looking into this myself, wondering if I can work a way of using images to represent radion buttons, will let you know if I come accross anything useful.

[nvening]

ok, im now trying to setup the written review output using basically the same code as before:

//Query
$query_rev = "SELECT review as review_dat FROM restreview WHERE id=$id ";

//Execute the query
$queryrow2 = mysql_query($query_rev);

//get first associated row from dataset returned
$data2 = mysql_fetch_assoc($queryrow2);

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

however authough there are 3 reviews, only the first is displaid, is there an obvious reason for this? I basicly dont understand why it needs the mysql_fetch_assoc part, thats why its not obvious to me!

thanks!

EDIT: im now realising this is going to be more complicated than i thought - i need to come up with a way for it to display all the reviews, with formatting inbetween each and ideally the rating gived for each review - im guessing each review is going to need its own auto_increment id but atm i cant figure out exactly how to do it - hopefully i can figure it out when ive got a while to think clearly but hmmmmmmm

[Budding]

//Query
$query_rev = "SELECT review as review_dat FROM restreview WHERE id=$id ";

//Execute the query
$queryrow2 = mysql_query($query_rev);

//echo the rating field, from the rows just returned
while($data2 = mysql_fetch_assoc($queryrow2)) {
    echo $data2['review_dat'];
    echo '<br />';
} 

Try replacing the code in your previous post with the code above.

[DougMcDonald]

The mysql_fetch_assoc code fetches the first row in the dataset, so all you need logically is a loop to ensure you go through all the rows in the data.

I use something like this:

//Query
$query_rev = "SELECT review as review_dat FROM restreview WHERE id=$id ";

//Execute the query
$queryrow2 = mysql_query($query_rev);

//get first associated row from dataset returned
$data2 = mysql_fetch_assoc($queryrow2);

//setup a loop which will continue until the data is empty
while ($data2[review_dat] != 0) {
//echo the rating field, from the row you just returned
echo $data2['review_dat'];
//Insert something here in terms of html formatting maybe, like in buddings example with a <br/>
//What you do depends on what visual effect you are after

//Call the fetch assoc function again, this returns the next row in sequence
$data2 = mysql_fetch_assoc($queryrow2);
} 

This should sort you out.

A couple of side points,

#1 - I would pick more meaningful names than data2 etc, try to make them something that is obvious as to what they refer. Maybe $review and $review_query in this case.

#2 - You don't need a column alias in this example. When using functions like SUM(), AVG(), COUNT() etc in SQL you need a column alias, but if you are just returning a field there is no need to (unless you want to rename the column to something more meaninful, as per above example)

[nvening]

Ill test it in a sec, but your officially the best person XD thanks so much!

[DougMcDonald]

Post in response to your edit,

I can post you something along those lines, formatting, results etc but some of it is dependent on your database structure.

Can you list the table(s) in question and what fields they have?

The auto increment function you are after is best implemented via a database field. If this is not in place, it would be most sensible to create one now.

[nvening]

The database can be constructed however you want to suite the system, it currently holds no information, ie its new

So your previous post is not the best option to display the reviews how the need to be?? (havnt read it through properly yet, was just finishing off a film)

Thanks

EDIT: btw, your code before displays nothing

[Budding]

My code (post #22) or Doug's code (post #23)??

[DougMcDonald]

I think he's probably talking mine.

If the code is displaying nothing, it suggests that the loop exit condition is being met first time round.

I took a small guess on this one as the way I would normally do it would be to have a 'reviewid' column or something similar, using auto increment.

This way, each of your reviews will have a unique id.

Assuming you add an id column for your reviews and that it is populated, I would re-write the code as below:

//Query
$query_review = "SELECT reviewid, review FROM restreview WHERE id=$id ";

//Execute the query
$review_dataset = mysql_query($query_review);

//get first associated row from dataset returned
$review_row = mysql_fetch_assoc($review_dataset);

//setup a loop which will continue until the data is empty
while ($review_row[reviewid] != 0) {
//echo the rating field, from the row you just returned
echo $review_row['review'];
//Insert something here in terms of html formatting maybe, like in buddings example with a <br/>
//What you do depends on what visual effect you are after

//Call the fetch assoc function again, this returns the next row in sequence
$review_row = mysql_fetch_assoc($review_dataset);
} 

Pretty sure that would work, if not, I would suggest again echoing back the query and checking its validity, then echoing something like "Into loop" inside the loop statement, which will verify whether or not you are getting into the loop succesfully.

[nvening]

erm, this is weird, its saying that the last line of the whole page (ie not the script) contants an unexpected $end - full error: "
Parse error: syntax error, unexpected $end in /home/gkelly/public_html/stives/product.php on line 324"

the last line was <html>, tried deleting that, which left </body> but same error - removing the new code fixes the error, so theres obviously a problem.

EDIT: there appears to be an open { without a closing }, when the } is added, i assume after the echo, it just repeats the first review over and over - and i did add the review id auto_increment collumn to the table, thanks

EDIT: Ah, ok its working now, realised that the } was supposed to go right at the end, thanks

[DougMcDonald]

Yeah as you discovered, the last } should go after the fetch_assoc which will allow the records to continue processing.

( I'm pretty sure that's where i put it in my code )

Logically speaking, what you should be doing is this:

open loop {

display what you want from the row

move to the next row

end loop }

[nvening]

hi again, im having trouble with the review submission form, is there any reason why this shouldnt work?

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

    $name_rev=$_POST['name'];
    $email_rev=$_POST['email'];
    $rating_rev=$_POST['rating'];
    $review_rev=$_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_review = "INSERT INTO restreview VALUES ('$id','$name_rev','$email_rev','$rating_rev','$review_rev')";
    


    mysql_close();
}
?>
<h1>Your Details:</h1>

<form action="product.php?id=<? echo $id; ?>" method="post">
<span class="det">Name:</span> <input style="margin-left: 5px;" type="text" name="name"><br/>
<span class="det">Email:</span> <input style="margin-left: 7px;" type="text" name="email"> <span class="det">(will not be displayed)</span><br/>

<span class="det">Rating:</span> <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/>
<h1>Review:</h1>
<textarea rows="5" cols="50" name="review">
Enter your review here
</textarea>
<input type="hidden" name="validation" value="1"/>
<p style="margin-left: 50px;" >
<input type="submit" value="Submit" >
<input type="reset" value="Reset">
</p>
</form>

Also how to i get the table to keep any line breaks in the review?



thanks!!

[DougMcDonald]

I'd need a bit more info to resolve this one, what isn't working?

Are you getting errors? or is it just not doing anything?

A couple of pointers though.

#1 - I'm not sure I'd do the database connection like this (otherwise you will have to write it out a lot of times. I would suggest using includes and a function call (give me a shout if you'd like some info on how to achieve this))

#2 - The $id variable you are trying to echo has not been set at any point,

#3 - You could probably do with some error handling around the insert statement

#4 - Have you checked the db to see if anything is getting inserted?

If you could give a bit more info about what you are trying to achieve here I can certainly give you some code snippets which should help.
It looks like you are trying to post a review to a product, and I presume the $id variable is supposed to be the id of the product. Can you post the full code from the page so I can take a look at what you are trying to do?

Thanks