Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help joining tables with group by [was: MYsql help]

    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 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
    Last edited by guelphdad; 12-16-2006 at 06:13 PM. Reason: provide proper thread title

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #3
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thx for your replay

    can you make an php example

    this my data base

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

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #5
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok what the query i will write??

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #7
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


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

  • #8
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #10
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1. singer table
      (sing in database)

      • id=singer id
        Name=singer name

    1. album tabel
      (tap in database)
      • id=album id
        Name = album name
        Category =singer id that same in sing table

    1. 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
    1. singer table
      (sing in database)
      • id=4
        Name=bryen adam

    1. album tabel
      (tap in database)
      • id=2
        Name = her i am
        Category =4

    1. 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` (
      `
    idint(11NOT NULL auto_increment,
      `
    Namevarchar(255NOT NULL default '',
      `
    Descriptiontext NOT NULL,
      `
    phototext NOT NULL,
      `
    Categoryvarchar(255NOT NULL default '0',
      `
    Sitevarchar(255NOT NULL default '',
      `
    fansvarchar(255NOT NULL default '',
      `
    Countryvarchar(255NOT NULL default '',
      `
    Lyricsvarchar(255NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=;

    #
    # Dumping data for table `sing`
    #

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

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

    #
    # Table structure for table `song`
    #

    CREATE TABLE `song` (
      `
    idint(10NOT NULL auto_increment,
      `
    Namevarchar(255NOT NULL default '',
      `
    mp3varchar(255NOT NULL default '',
      `
    rmvarchar(255NOT NULL default '',
      `
    video2varchar(255NOT NULL default '',
      `
    videovarchar(255NOT NULL default '',
      `
    remixvarchar(255NOT NULL default '',
      `
    downloadvarchar(255NOT NULL default '',
      `
    Categoryvarchar(255NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=;

    #
    # Dumping data for table `song`
    #

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

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

    #
    # Table structure for table `tap`
    #

    CREATE TABLE `tap` (
      `
    idint(10NOT NULL auto_increment,
      `
    Namevarchar(255NOT NULL default '',
      `
    addbyvarchar(255NOT NULL default '',
      `
    photovarchar(255NOT NULL default '',
      `
    Categoryvarchar(255NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=;

    #
    # Dumping data for table `tap`
    #

    INSERT INTO `tapVALUES (1'Enaya Bet7ebak''Wolfman''''1');
    INSERT INTO `tapVALUES (2'Her I am''''''4'); 
    Last edited by aymanstar; 12-20-2006 at 05:22 AM.

  • #11
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    I believe you want SUM and not COUNT.

  • #12
    New Coder
    Join Date
    Dec 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i use sum allready

    PHP Code:
    SELECT sing.Namesum
    CASE  
    WHEN song.Category tap.Category AND tap.Category sing.id
    THEN download
    ELSE 
    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.

  • #13
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •