[RESOLVED] Averaging variables

[nathan2oo3]

Hi guys, I have created a rating system for my dvd review website. When a user is logged in they can post comments and ratings on a movie. I have a welcome page once a user is logged in which shows 5 dvd's they can choose from. I am going to add more DVD's and would like to be able to display just the most rated DVD's. Is there any way I can do this within PHP or SQL?

Thanks

[Pikachu2000]

Do you mean you want to display the ones with the highest number of ratings, or the ones that are rated the highest?

[anakadote]

If it's 'most rated':
select * from dvds order by ratings desc limit 5

For this query you'd need to increment the value of 'ratings' each time someone rated that particular dvd.

If it's 'highest rating':
select * from dvds order by rating desc limit 5

And for this query, store the average rating of the DVD in the 'rating' column

[toxic_brain]

@
I guess you have a rating table .

id dvd_id rating

1 10 3
2 10 2
3 10 4
4 11 2
5 12 5
6 12 5

query to get 'highest rated'

SELECT dvd_id, AVG(rating) FROM rating GROUP BY id ORDER BY AVG(rating) DESC LIMIT 5

query to get 'most rated'

SELECT dvd_id, COUNT(*) FROM rating GROUP BY id ORDER BY COUNT(*) DESC LIMIT 5

[niroshan]

hi toxic_brain,

SELECT dvd_id, AVG(rating) FROM rating GROUP BY id ORDER BY AVG(rating) DESC LIMIT 5

SELECT dvd_id, COUNT() FROM rating GROUP BY id ORDER BY COUNT() DESC LIMIT 5

I think it should be GROUP BY dvd_id.🙂

Otherwise same DVD will be repeated.

Regards,
niroshan

[toxic_brain]

@: correct.thanks.

[nathan2oo3]

This is the code I have so far.. but at the moment it just gives me the dvd's still with 'Array' as text under each film where the rating should be... any ideas...

				$dbQuery2="SELECT dvdref, AVG(rating) FROM rating GROUP BY dvdref ORDER BY AVG(rating) DESC LIMIT 5";					
				$result2 = mysql_query($dbQuery2,$db);	
				$rating = mysql_fetch_row($result2) 
				or die( "$dbQuery2\n" . mysql_error() ) ;

				$dbQuery1="SELECT * FROM dvd order by title";
				$result1 = mysql_query($dbQuery1,$db);	
				while($dbRow=mysql_fetch_array($result1))
					{	
					$dvdref=$dbRow["dvdref"];  
					$dvdLink=$dbRow["dvdLink"]; 
					$title=$dbRow["title"];

			<?php echo"$title";?>
			<br>
			<?php echo"$rating";?>
			</form>
			</td>


			<?php
				}
			?>

[toxic_brain]

u need only single query

<?
$dbQuery1="SELECT dvd.*, AVG(rating) as rating FROM rating  LEFT JOIN dvd ON dvd.id=rating.dvdref GROUP BY rating.dvdref ORDER BY AVG(rating) DESC LIMIT 5";
					$result1 = mysql_query($dbQuery1,$db);	
					while($dbRow=mysql_fetch_array($result1))
						{	
						$dvdref=$dbRow["dvdref"];  

						$dvdLink=$dbRow["dvdLink"]; 
						$title=$dbRow["title"];
						$rating=$dbRow["rating"];  

<?php echo"$title";?>
				<br>
				<?php echo"$rating";?>
				</form>
				</td>


			<?php
				}
			?>

[nathan2oo3]

Got it workin.. kind of...lol. I can get it to return the average value now of each movie however I cant get it to limit the number of movies I am returning and put them in descending order so I have highest rated movies first...

					$dbQuery1="SELECT * FROM dvd order by title";
					$result1 = mysql_query($dbQuery1,$db);	
					while($dbRow=mysql_fetch_array($result1))
						{	
						$dvdref=$dbRow["dvdref"];  

						$dvdLink=$dbRow["dvdLink"]; 
						$title=$dbRow["title"];

				$dbQuery2="SELECT dvdref, AVG(rating) FROM rating where dvdref='$dvdref' DESC LIMIT 5";					
				$result2 = mysql_query($dbQuery2,$db);	
				$rating = mysql_fetch_array($result2) 
				or die( "$dbQuery2\n" . mysql_error() ) ;


<?php echo"$rating[1]";?>

[nathan2oo3]

The error I get is..

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in N:\home\scie\d07cn\COM570\loginCentre.php on line 130
SELECT dvdref, AVG(rating) FROM rating where dvdref='25' ORDER DESC LIMIT 5 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 5' at line 1

[bradgrafelman]

That's not the right syntax for an "ORDER BY" statement. See this page in the MySQL manual for an example of how to use an ORDER BY clause.

[nathan2oo3]

Well...its showing me the average so im happy enough with that although can anyone tell me how I can get this value to display in 2 decimal places rather than 4 which it is doing at the moment?

Last question I promise.lol😃

[laserlight]

nathan2oo3 wrote:

Well...its showing me the average so im happy enough with that although can anyone tell me how I can get this value to display in 2 decimal places rather than 4 which it is doing at the moment?

One option is to use [man]round/man. [man]sprintf/man and friends are another option.

[nathan2oo3]

Thanks very much guys, that sorted my problem 🙂 😃

[laserlight]

Good to hear that 🙂
Remember to mark this thread as resolved (if it is) using the thread tools.