...

View Full Version : help joining tables with group by [was: MYsql help]



aymanstar
12-16-2006, 05:46 PM
I have 3 tabels

1 for singer
2- for album
3-for songs and this tabel have field with name download that count how many download by song

i want to make states with top 10 in every section
in songs i make this code


<?php
$result=mysql_query("SELECT * FROM song order by download desc limit 10");
while($row=mysql_fetch_object($result)){
echo $row[name];
}
?>

i want to make top 10 album by count the album songs download
and the same in singer

guelphdad
12-16-2006, 06:14 PM
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.

aymanstar
12-16-2006, 06:29 PM
thx for your replay

can you make an php example

this my data base



#
# 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=3 ;

# --------------------------------------------------------

#
# Table structure for table `song`
#

CREATE TABLE `song` (
`id` int(10) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`mp3` varchar(255) NOT NULL default '',
`rm` varchar(255) NOT NULL default '',
`video2` varchar(255) NOT NULL default '',
`video` varchar(255) NOT NULL default '',
`remix` varchar(255) NOT NULL default '',
`download` varchar(255) NOT NULL default '',
`Category` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

# --------------------------------------------------------

#
# Table structure for table `tap`
#

CREATE TABLE `tap` (
`id` int(10) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`addby` varchar(255) NOT NULL default '',
`photo` varchar(255) NOT NULL default '',
`Category` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

guelphdad
12-16-2006, 08:48 PM
before you make a php query, get an sql query working. all that php is used for is to format the output.

show what you have attempted as far as an sql query goes.

aymanstar
12-17-2006, 06:36 PM
ok what the query i will write??

guelphdad
12-17-2006, 11:28 PM
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.

aymanstar
12-19-2006, 12:43 PM
thanks about your help

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




$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)){

aymanstar
12-19-2006, 01:11 PM
and then try this code
but the proplem not sum all album and sort by singer have hightest download
i

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

guelphdad
12-20-2006, 12:40 AM
can you show the exact columns and rows for all three tables that you are trying to get for Bryan Adams? that might clarify your problem.

aymanstar
12-20-2006, 04:58 AM
singer table
(sing in database)


id=singer id
Name=singer name



album tabel
(tap in database)

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



#
# 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 ;

#
# Dumping data for table `sing`
#

INSERT INTO `sing` VALUES (1, 'Tamer Hosni', '', 'enayabetth2.jpg', '0', '', '', 'Egypt', '');
INSERT INTO `sing` VALUES (4, 'Bryan Adams', '', '', '0', '', '', 'Egypt', '');

# --------------------------------------------------------

#
# Table structure for table `song`
#

CREATE TABLE `song` (
`id` int(10) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`mp3` varchar(255) NOT NULL default '',
`rm` varchar(255) NOT NULL default '',
`video2` varchar(255) NOT NULL default '',
`video` varchar(255) NOT NULL default '',
`remix` varchar(255) NOT NULL default '',
`download` varchar(255) NOT NULL default '',
`Category` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

#
# Dumping data for table `song`
#

INSERT INTO `song` VALUES (1, 'Ray7 Balk', '', '', '', '', '', '176', '1');
INSERT INTO `song` VALUES (2, 'her i am', '', '', '', '', '', '55', '2');
INSERT INTO `song` VALUES (3, 'please forgive me', '', '', '', '', '', '45', '2');

# --------------------------------------------------------

#
# Table structure for table `tap`
#

CREATE TABLE `tap` (
`id` int(10) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`addby` varchar(255) NOT NULL default '',
`photo` varchar(255) NOT NULL default '',
`Category` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

#
# Dumping data for table `tap`
#

INSERT INTO `tap` VALUES (1, 'Enaya Bet7ebak', 'Wolfman', '', '1');
INSERT INTO `tap` VALUES (2, 'Her I am', '', '', '4');

guelphdad
12-20-2006, 06:14 AM
I believe you want SUM and not COUNT.

aymanstar
12-20-2006, 12:52 PM
i use sum allready



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

guelphdad
12-21-2006, 12:14 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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum