See the albums and artists link in my signature. that article should give you a starting point to get most of your code and we can revise it from there.
if you have looked at the example in the article you should have an idea how to start.
try a query, even if just to get partial data from your tables. when you do a three table join, first do a join on two of the tables. when you get that working then add the third table. once you have the basics down then you can specify all of the columns needed.
i done with this cos but i have proplem that the output calculate download by row not sum
for example
Bryen adam have 3 song
1 song have 3 download time
2 song 2
3 song 4
i want the total in my code when see the output
9 but the outbout is 3 below my cod
Code:
$result=mysql_query("SELECT sing.Name, count( download ) AS songtotal
FROM sing
LEFT OUTER JOIN tap ON sing.id = tap.Category
LEFT OUTER JOIN song ON song.Category = tap.id
GROUP BY Name
ORDER BY Name");
while($row=mysql_fetch_object($result)){
and then try this code
but the proplem not sum all album and sort by singer have hightest download
i
Code:
SELECT sing.Name, sum(case when song.Category=tap.Category and tap.Category=sing.id then download else 0 end ) AS songtotal
FROM sing
LEFT OUTER JOIN tap ON sing.id = tap.Category
LEFT OUTER JOIN song ON song.Category = tap.id
GROUP BY Name
ORDER BY Name
Last edited by aymanstar; 12-19-2006 at 01:32 PM..
id=album id
Name = album name
Category =singer id that same in sing table
song tabel
(song in database)
id=song id
Name = song name
Category =album id that same in tap table
download=number of download and i want to sort the output of query 2 times frist 1 sort singer by song download 2nd one sort album by song download limit 10 desc
example
singer table
(sing in database)
id=4
Name=bryen adam
album tabel
(tap in database)
id=2
Name = her i am
Category =4
song tabel
(song in database)
id=1
Name = her i am
Category =2
download=55
id=2
Name = please forgive me
Category =2
download=55
i need the output
TOP SINGER
1 Tamer Hosni 176 download
2 bryn adam 100 download
TOP ALBUM
1 Enaya Bet7ebak 176 download
2 her i am 100 download
Example query
PHP Code:
#
# Table structure for table `sing`
#
CREATE TABLE `sing` (
`id` int(11) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`Description` text NOT NULL,
`photo` text NOT NULL,
`Category` varchar(255) NOT NULL default '0',
`Site` varchar(255) NOT NULL default '',
`fans` varchar(255) NOT NULL default '',
`Country` varchar(255) NOT NULL default '',
`Lyrics` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
SELECT sing.Name, sum(
CASE WHEN song.Category = tap.Category AND tap.Category = sing.id
THEN download
ELSE 0
END ) AS songtotal
FROM sing
LEFT OUTER JOIN tap ON sing.id = tap.Category
LEFT OUTER JOIN song ON song.Category = tap.id
GROUP BY Name
ORDER BY songtotal DESC
but the output
Name songtotal
Tamer Hosni 176
Bryan Adams 0
but i need the output will be
Name songtotal
Tamer Hosni 176
Bryan Adams 100
Last edited by aymanstar; 12-20-2006 at 01:15 PM..
please post sample data that will give you the output you are looking for. your sample data above does NOT match the output you are looking for so it is hard to tell why it isn't working.
secondly, are you trying to get your output directly in phpmyadmin (or some other GUI) or the mysql client? and NOT messing up perhaps with php or other code where your output is getting messed up?
Before you post, read our: 
