Dreamweaver ratings and averages on a database

  • Travis Coats
  • Graduate
  • Graduate
  • Travis Coats
  • Posts: 107

Post 3+ Months Ago

I am not even going to lie to you from the start - I know nothing of php or mySQL, except for the basics of creating databases, tables and using Dreamweaver's built in tools. So...

I am trying to create a ratings system, and I have set up a working query on the site to show the top 5 rated songs in my library. It includes an insert record action to a database, and then a separate query to show the tracks in the database sorted by rating descending. The problem that I know I am about to encounter is - There will be multiple track ratings say "Track1....rating 5" and then another Track1....rating 4." I don't know how to create a query to do an average. The hard thing would be to create a table for each track---but no no no, that doesn't even begin to sound right. Any suggestions. I know the advanced query window offers custom SQL commands, but does anyone know what I would need to do to find a tracks average rating?

The table would look something like this:

Track Rating

Track1 5
Track1 4
Track2 2
Track1 3

I would need to find some way for the query to "smartly" know that for everytime track=track1, that I need an average for all records containing the track1 in the track column.

Working copy
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Travis Coats
  • Graduate
  • Graduate
  • Travis Coats
  • Posts: 107

Post 3+ Months Ago

Wow I figured this out on my own....!? Heheheheh. For any other dreamweaver novices out there who might want to emulate or create something similar here the code I used to get it right.

SELECT track, avg( rating ) as avg_rating
FROM `trackRatings`
GROUP BY track

Of course for your purposes it would be this:

SELECT column, avg( othercolumn ) as avg_othercolumn
FROM `table`
GROUP BY column

Make sure you change the dynamic text of your repeating region from it's name to avg_name. Dreamweaver won't update inserts for advanced queries, so I had to play with the php. Not too bad... :)

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 60 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.