Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 05/09/2017 BIG Autumn SALE

    Use AUTUMN17 coupon code to buy our WordPress or Joomla! extensions with 25% discount. Hurry up! The offer is valid till 11 September.

  • 14/04/2017 Happy Easter

    Wish a Happy Easter and give 30% discount for all our WordPress and Joomla! extensions. Use EASTER17 coupon code. The offer is valid till 17 April.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

top ten songs
(1 viewing) (1) Guest
Go to bottomPage: 123
TOPIC: top ten songs
#56545
top ten songs 1 Year, 10 Months ago Karma: 0
Hi
I want to make a top ten songs with two tables but i dont know how to do it.
I have a album table , songs table and a ratings table,the common field is song_id for songs and ratings and album_id for songs and albums
I want to show, bandname and albumname (from albums)songname (from songs), and the averageof all ratings (from ratings), for example 4 ratings: 9+4+6+7/4=6.5
Ordering by ratings average.
Can you help me, thanks
The administrator has disabled public write access.
 
#56549
Re:top ten songs 1 Year, 10 Months ago Karma: 666
Hello,

SQL query will look something like this:

SELECT
S.title AS song_name,
A.title AS album_name,
AVG(R.rating) AS rating
FROM
songs S INNER JOIN ratings R
ON S.song_id = R.song_id
INNER JOIN albums A
ON S.album_id = A.album_id
GROUP BY
S.song_id

Regards,
ARI Soft
The administrator has disabled public write access.
 
#56551
Re:top ten songs 1 Year, 10 Months ago Karma: 0
thanks for your answer but i think it's more difficult than i thought.
i've looking the tables and the relations and fields are like this.
I don't know if it's clear enough, but i want to make two ratings lists one with songs and another with albums
I attach an excel file
Thank you again for your pacience

artists Albums songs ratings

Id artist_name Id name artist_id Id album_id name Id Album_id points type
1 kraftwerk 1 Computer world 1 450 1 pocket calculator 1 450 5 song
2 U2 2 man machine 1 450 1 pocket calculator 2 450 8 song
3 Madonna 3 like a virgin 3 120 3 noname 3 3 9 album


relations
artists albums albums songs songs ratings type=song
id artist_id Id album_id id album_id

albums ratings type=album
id album_id


result for song rating

artist album song average
kraftwerk computer world pocket calculator 6,5

result for albums rating

artist album average
madonna like a virgin 9
File Attachment:
File Name: ratings.zip
File Size: 7122
Last Edit: 2015/12/20 03:45 By rastemserie.
The administrator has disabled public write access.
 
#56558
Re:top ten songs 1 Year, 10 Months ago Karma: 666
Send SQL dump of your tables by email.

Regards,
ARI Soft
The administrator has disabled public write access.
 
#56560
Re:top ten songs 1 Year, 10 Months ago Karma: 0
i dont know how to do that
i can export the table structure to word, for ex.
The administrator has disabled public write access.
 
#56561
Re:top ten songs 1 Year, 10 Months ago Karma: 666
This video tutorial shows how to create an SQL dump with help of PHPMyAdmin tool which is usually available in hosting control panel (like cPanel).

Regards,
ARI Soft
The administrator has disabled public write access.
 
Go to topPage: 123