Logic Problem?

[tony_l]

I am building a site that will allow visitors to review and rate certain books. The page that is giving me problems is the page that displays the books and the ratings that the book has recieved (average rating).

Well I have played with this thing till I am blue in the face and I just know it is something simple.

Could someone look over the code below and tell me what I am doing?

<table width = '100%' border ='0' bgcolor='#FFFFFF'>

<tr>
	<td width = '30%'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>Book Title</b></font></td>
	<td width = '25%'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>Reviews/Rating</b></font></td>
	<td width = '40%'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>Description</b></font></td>
	<td width = '5%'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>Review?</b></font></td>
</tr>

");


######################################
## Execute queries to get the books ##
######################################

$loopQuery = "SELECT * FROM $TableName_10 WHERE book_status = '1'";
$loopResult = mysql_query($loopQuery, $Link1);

#####################
## Print Books Out ##
#####################

WHILE ($row = mysql_fetch_array($loopResult))
{

$reviewQuery = "SELECT rating FROM $TableName_11 WHERE book_id = '$book_id'";
$reviewResult = mysql_query($reviewQuery, $Link1);
$reviewArray = mysql_fetch_array($reviewResult);

//Figure out the rating.

$votes = mysql_num_rows($reviewResult);

IF ($votes > 0)
{
	$tally = 0;

for($i = 0; $i <= $votes; $i++)
{

$tally = $reviewArray[$i] + $tally;

}

$average = $tally / $votes;

}


PRINT("
<tr bgcolor = '#c8c4c2'>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>$row[book_title]</font></td>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>");

IF($votes <= 0)
{

Print("votes = $votes<br>tally = $tally<br>average = $average");

}

ELSEIF($average <= '1')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");

}

ELSEIF($average > '1' && $average <= '2')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

ELSEIF($average > '2' && $average <= '3')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");

}

ELSEIF($average > '3' && $average <= '4')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

ELSEIF($average > '4' && $average <= '5')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

PRINT("</font></td>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>$row[book_description]</font></td>

<td align ='center'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><a href ='http://www.spiderkeeper.com/RESOURCES/book_review.php?book_id=$row[book_id]'>
<img src='http://www.spiderkeeper.com/images/icons/chklst.gif' border = '0'></a></font>
</td>
</tr>
");

}

#####################################
## End and link to Add a book page ##
#####################################

PRINT("</table>");

[dopey]

It looks as though you are only fetching one row from the resut set of the ratings query:

$reviewResult = mysql_query($reviewQuery, $Link1);
$reviewArray = mysql_fetch_array($reviewResult);

$reviewArray is just one row from the result set i.e. not all of them hence your 'for' loop to calc the avg isn't working. You'll need something along the lines of:

while($row = mysql_fetch_array($reviewResult))
{
    $tally = $row[0] + $tally;
}
$average = $tally/$votes;

...although a far nicer way to do what you want would be no change your query:

$reviewQuery = "SELECT count(*),AVG(rating) FROM $TableName_11 WHERE book_id = '$book_id'";
$reviewResult = mysql_query($reviewQuery, $Link1);
list($votes,$average) = mysql_fetch_row($reviewResult)

Cheers,
Andy

[tony_l]

Ok, well I tried the Query that you suggested (I tried something similar before, but failed miserably LOL) It is still not doing it, and now I get an invalid result error 🙁 .

any suggestions? this should be simple to do, but of course with me it crashes and burns.

PS- When I run the query through PHPMYADMIN, it comes up with the correct result witht he vote count and the average in seperate columns- which is good. Why it is not doing it with the program I am not sure. I thought that it may be that the variables file(that I keep seperate from the rest) had the wrong table name in it, after double checking that like 10 times, it is still giving me an error.

Help 🙂

[tony_l]

Ok I found the problem with the invalid result error, but now it is not displaying the Average or Votes in the test table. The record it is looking at has one vote in it- the one I put in there. According to the IF statements IF it has more than 0 votes (which it does) it is supposed to display the image that corresponds with the result. So far it is now just going to the first IF statement and displaying my error checking (print VOTES, AVERAGE and TALLY).

Suggestions?

[tomhath]

Where did $book_id come from? Did you mean $row['book_id']?

$reviewQuery = "SELECT rating FROM $TableName_11 WHERE book_id = '$book_id'";

[tony_l]

Good catch! BUT- even after fixing that, it is still not working correctly. It now lists the average like it is supposed to, but it is not listing the votes ($votes from the list()).

[dopey]

Where are you echoing out $votes? The only place in your original code is in an 'if' statement that's only executed when $votes <=0...

Perhaps you could post an updated version of where you are now?

Also, it will be useful to add a mysql_error() call if the query fails e.g.

$reviewResult = mysql_query($reviewQuery, $Link1) or die(mysql_error($Link1));

Cheers,
Andy.

[tony_l]

ok, here is the revised code as it stands now. Since I changed it around to this latest version, it is now displaying a blank cell under the review/rating column. It was supposed to display "NOT REVIEWED" or the image that corresponds to the rating.

I hate to be annoying like this, it is just that I know this should be working now, but it is just not cooperating with me. LOL

Thanks to all those that are helping me on this.

######################################
## Execute queries to get the books ##
######################################

$loopQuery = "SELECT * FROM $TableName_10 WHERE book_status = '1'";
$loopResult = mysql_query($loopQuery, $Link1);

#####################
## Print Books Out ##
#####################

WHILE ($row = mysql_fetch_array($loopResult))
{

$reviewQuery="SELECT_count(*),AVG(rating)_FROM_book_reviews_WHERE_book_id_= '$row[book_id]'";
$reviewResult = mysql_query($reviewQuery,$Link1);
list($votes,$average) = mysql_fetch_row($reviewResult);


PRINT("
<tr bgcolor = '#c8c4c2'>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>$row[book_title]</font></td>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>");


IF($votes <= 0)
{

Print("votes = $votes<br>average = $average");
//Otherwise this will display "NOT REVIEWED"

}

ELSEIF($average <= '1')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");

}

ELSEIF($average > '1' && $average <= '2')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

ELSEIF($average > '2' && $average <= '3')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");

}

ELSEIF($average > '3' && $average <= '4')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

ELSEIF($average > '4' && $average <= '5')
{

PRINT("<img src='http://www.spiderkeeper.com/images/icons/3star2a.gif' border='0' height = '' width = ''>");


}

PRINT("</font></td>
	<td><font size='2' face='Verdana, Arial, Helvetica, sans-serif'>$row[book_description]</font></td>

<td align ='center'><font size='2' face='Verdana, Arial, Helvetica, sans-serif'><a href ='http://www.spiderkeeper.com/RESOURCES/book_review.php?book_id=$row[book_id]'>
<img src='http://www.spiderkeeper.com/images/icons/chklst.gif' border = '0'></a></font>
</td>
</tr>
");

}

#####################################
## End and link to Add a book page ##
#####################################

PRINT("</table>");

[tony_l]

Um nevermind, I told you it was something stupid and simple right? Well I figured out that it was my HTML that was off this time.

I had purposefully left the height = '' and width ='' because I did not know how big I wanted the images. I changed them tot he correct height and width and viola! Images appeared!

I fell like a putz now. I am sorry that I did not catch that before folks. Thanks for all of your help!