SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    too many darn results

    ok so im using this statement to select my information that groups admins with there members.
    PHP Code:
    SELECT AdminNameMemberName FROM membersadmins WHERE id_to_admin AdminName.id 
    what i would like this to do is display something like this
    Code:
    AdminName  <--displayed just once
    ------------------------------
    Member1	<--all members that belong to one admin
    Member2
    Member3
    unfortunatly its outputting the results like this
    Code:
    -----------------
    AdminName
    Member1
    AdminName
    Member2
    AdminName
    Member3
    -----------------
    all i really what this to do is display the Admins name once with a list of their members underneath.


    oh. and im also assuming that this should be done with my select statement. really this is going to be displayed on a php page. should i have two different select statements?


    thanks any help would be totally appreciated!!!!

  2. #2
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    um I think that it best with 2 queries, if you first select all the adminnames and then those results you put in a while loop that then selects all the members where adnimname = $adminname (from last sql query). Then it will loop through and display then in the right order? and use the order by adnimname in first query and order by member in second

    Sarah
    Regular user

  3. #3
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Sarah
    um I think that it best with 2 queries, if you first select all the adminnames and then those results you put in a while loop that then selects all the members where adnimname = $adminname (from last sql query). Then it will loop through and display then in the right order? and use the order by adnimname in first query and order by member in second

    Sarah

    hrm but what if the admin didnt have any members associcated with its name? wouldnt that leave holes in my page. like an admin name with no members underneath it?

  4. #4
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes but then in that case you would make sure the first sql query had a left join in it so you ONLY got adminnames would had members under them
    Regular user

  5. #5
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh and i figured ill put my php code up here too. mabey you could show me how it should look?


    PHP Code:
    <?php
    $mysql_id 
    mysql_connect('localhost''******''*******');
    mysql_select_db('f8klan'$mysql_id);


    $result = @mysql_query("SELECT fit_name, userid FROM fit_members, fit_admins WHERE fit_id_to_admin = fit_admins.id LIMIT 0,30");
        if (!
    $result) {
          echo(
    "<p>Error performing query: " mysql_error() . "</p>");
          exit();
        }  
        
    while ( 
    $row mysql_fetch_array($result) ) {
           
    $username $row["userid"];
          
    $fitmembers $row["fit_name"];
           echo (
    "$username<br> $fitmembers<br><br>");      
        }    
    ?>
    and here is how its outputting it on the page.


    Code:
    sporkit
    membername1
    
    sporkit
    f8(oo)someguy
    
    sporkit
    omg a name goes here
    
    admin_megaman
    adsfmember
    
    admin_megaman
    memberd00d
    omg
    
    sporkit
    f8(oo)someguy
    
    sporkit
    omg a name goes here
    
    megaman
    adsf
    
    megaman
    and here is how i would like it to look.

    Code:
    sporkit
    omg
    f8(oo)someguy
    omg a name goes here
    
    megaman
    memberadsf
    asdfomgmember2
    thanks for the help so far

  6. #6
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    okay I will see how I do

    PHP Code:
    <?php
    $result 
    = @mysql_query("SELECT fit_admins FROM fit_members, fit_admins WHERE fit_id_to_admin = fit_admins.id order by fit_admins LIMIT 0,30") or die(mysql_error());
        
    while ( 
    $row mysql_fetch_array($result) ) {
           
    $fitadmins $row["fit_admin"];

    echo 
    "<b>$fitadmins</b><br>";

    $result2 = @mysql_query("SELECT fit_name, userid FROM fit_members, fit_admins WHERE fit_id_to_admin = fit_admins.id and fit_admins='$fitadmins' order by fit_name LIMIT 0,30") or die (mysql_error());

    while ( 
    $row2 mysql_fetch_array($result2) ) {
           
    $fitname $row2["fit_name"];
    echo 
    "$fitname<br>";
      }
    }    

    ?>

    probably not totally correct but you should get the drift? Off now but will be back on in thr morning (for me)

    Sarah
    Regular user

  7. #7
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    darrn so close on that one. i edited the code a bit so it would compile. heres what it looks like.

    PHP Code:
    <?php
    $mysql_id 
    mysql_connect('localhost''asdf''asdf');
    mysql_select_db('f8klan'$mysql_id);


    $result = @mysql_query("SELECT userid FROM fit_members, fit_admins WHERE fit_id_to_admin = fit_admins.id order by fit_admins.ID LIMIT 0,30" ) or die(mysql_error());

    while ( 
    $row mysql_fetch_array($result) ) {
           
    $fitadmins $row["userid"];

    echo 
    "<b>$fitadmins</b><br>";

    $result2 = @mysql_query("SELECT fit_name FROM fit_members, fit_admins WHERE fit_id_to_admin = fit_admins.id and fit_admins.userid='$fitadmins'  order by fit_name LIMIT 0,30" ) or die (mysql_error());

    while ( 
    $row2 mysql_fetch_array($result2) ) {
           
    $fitname $row2["fit_name"];
    echo 
    "$fitname<br>";
      }
    }    

    ?>
    thats vary close, but now its outputting this.

    Code:
    sporkit
    f8(oo)someguy
    omg
    omg a name goes here
    
    sporkit
    f8(oo)someguy
    omg
    omg a name goes here
    
    sporkit
    f8(oo)someguy
    omg
    omg a name goes here
    
    megaman
    adsfqwre
    asdf
    
    megaman
    adsfqwer
    asdf
    its outputting the entire list to as many members associated with one admin. any suggestions.

    thanks for the help so far.

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see the point of having 2 queries really.

    Just use your original query, sorting by the admin name for easier looping, and also by member names for aesthetic reasons.
    Code:
    SELECT AdminName, MemberName
    FROM members, admins
    WHERE id_to_admin = AdminName.id
    ORDER BY AdminName, MemberName
    Then using this query, simply get the AdminName and while it's still the same for the next record, continue outputting the MemberName. If it's a different AdminName, print a separator and the AdminName and then go on with the MemberNames until you again get to a different Admin.

    Make sense?

  9. #9
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by redemption
    I don't see the point of having 2 queries really.

    Just use your original query, sorting by the admin name for easier looping, and also by member names for aesthetic reasons.
    Code:
    SELECT AdminName, MemberName
    FROM members, admins
    WHERE id_to_admin = AdminName.id
    ORDER BY AdminName, MemberName
    Then using this query, simply get the AdminName and while it's still the same for the next record, continue outputting the MemberName. If it's a different AdminName, print a separator and the AdminName and then go on with the MemberNames until you again get to a different Admin.

    Make sense?

    heh. sorry that did what was going wrong before. it outputed this on the screen.
    PHP Code:
    sporkit
    f8
    (oo)someguy
    sporkit
    omg
    sporkit
    omg a name goes here
    megaman
    adsf
    megaman
    asdf 
    listing each admin more than once. if i was gona thing of an easy example then its kinda like i have a catagore with subtopics. i want to display the catagore and list the subtopics underneath them. now from what i understand in the Kevin Yank book. I need to put my subcatagoris in a different table so they can be listed in the correct place. heck why dont i just show you the tables im pulling it from.

    Code:
    fit_members
    id
    fit_name
    fit_info
    fit_thoughts
    fit_id_to_admin
    and the other table looks like

    Code:
    fit_admins
    ID
    userid
    password
    fullname
    email
    notes
    i really only need to use a few catagories outta that last one for now. i think we almost had it piced together in my last post, but im not sure.

  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sporkit150
    heh. sorry that did what was going wrong before. it outputed this on the screen.
    PHP Code:
    sporkit
    f8
    (oo)someguy
    sporkit
    omg
    sporkit
    omg a name goes here
    megaman
    adsf
    megaman
    asdf 
    Yup sure of course that's what it does when you simply output the AdminName for each row. If you re-read my post, I'd suggested you suppress displaying the AdminName if it's the same as that in the previous row.

    Get what I mean?

  11. #11
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by redemption
    If you re-read my post, I'd suggested you suppress displaying the AdminName if it's the same as that in the previous row.

    Get what I mean?
    Ohhhhh! i get it now. heh the only problem is i dont know how i would "surpress" that. heh. is there like a place where i could read up on that code?

    i mean i wouldnt know what that looks like.



    thanks
    PHP Code:
    $result = @mysql_query("SELECT fit_admins.userid, fit_members.fit_name FROM fit_members, fit_admins WHERE fit_members.fit_id_to_admin = fit_admins.ID ORDER BY fit_admins.ID, fit_members.fit_name" ) or die(mysql_error());

    while ( 
    $row mysql_fetch_array($result) ) {
          
    $fitadmins $row["userid"];
          
    $fitname $row["fit_name"];
          echo (
    "<b>$fitadmins</b><br>$fitname<br>"); 

  12. #12
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just don't display it. Something like that:
    PHP Code:
    $fitadmins '';
    while ( 
    $row mysql_fetch_array($result) ) {
          if ( 
    $fitadmins != $row['userid'] ) {
              
    // different admin
              
    $fitadmins $row["userid"];
              
    $fitname $row["fit_name"];
              echo 
    "<b>$fitadmins</b><br>$fitname<br>";
          else {
              
    // still the same admin
              
    $fitname $row["fit_name"];
              echo 
    "$fitname<br>";
          } 

  13. #13
    SitePoint Addict sporkit's Avatar
    Join Date
    Jan 2003
    Location
    DeKalb, IL
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by redemption
    Just don't display it. Something like that:
    PHP Code:
    $fitadmins '';
    while ( 
    $row mysql_fetch_array($result) ) {
    if ( 
    $fitadmins != $row['userid'] ) {
    // different admin
    $fitadmins $row["userid"];
    $fitname $row["fit_name"];
    echo 
    "<b>$fitadmins</b><br>$fitname<br>";
    else {
    // still the same admin
    $fitname $row["fit_name"];
    echo 
    "$fitname<br>";

    rock on that finally got it working. thanks to the both of you for helping me out with this. now let me see if i understand this right.

    PHP Code:
    //declaired as blank so nothing will happen?
    $fitadmins ''
    //select the info from the database
    while ( $row mysql_fetch_array($result) ) {
    //if the admin name is not equal to userid then write it to the screen
    if ( $fitadmins != $row['userid'] ) { 
    //code for the echo
          
    $fitadmins $row["userid"];
          
    $fitname $row["fit_name"];
          echo (
    "<b>$fitadmins</b><br>$fitname<br>");}
    //if the admin name has already been posted go with the rest of the name in the database.
     
    else { 
    // still the same admin 
              
    $fitname $row["fit_name"]; 
              echo 
    "$fitname<br>"
      }   

    my "perception" of what i think is happening is in the comments. am i correct?

  14. #14
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    //declaired as blank so nothing will happen?
    $fitadmins ''
    Something like that, yes. It's there just so $fitadmins is initialized because in the first iteration, there wouldn't have been a $fitadmins otherwise.

    PHP Code:
    //if the admin name is not equal to userid then write it to the screen 
    More like if the admin name in the current record is not equal the the admin name of the previous record.

    PHP Code:
    //if the admin name has already been posted go with the rest of the name in the database 
    Yup.


Bookmarks

Posting Permissions

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