SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Sacramento
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Query for dynamically 'checked' checkboxes...?

    While generating a checkbox for every 'club' in my *clubs* database (abridged structure below), I want to...

    => Dynamically mark as 'checked' ONLY those checkboxes corresponding with each 'club' of which a given 'member' is already a member?

    I.e., where there are 4 clubs (e.g., Sierra, Boy Scouts, Chess, 4H), but Bill is a member of only 2 (e.g., Sierra, Chess) -- generate all 4 checkboxes, but mark as 'checked' ONLY the 2 of which Bill is a member (e.g., Sierra, Chess).


    Code:
    Database tables:
    
    • members (fields: mID, email, fName, lName, etc.)
    • clubs (fields: cID, cName, cOffice, cDescript, etc.)
    • memberships (lookup table fields: mID, cID)
    I fear I'll have to do a "nested loop" (to test for Bill's membership in each club) which will -- I presume -- slow the iteration down.

    ONLY 2 Questions:

    1. Can I do it with JUST one SELECT -- perhaps a "LEFT JOIN" across 3 tables (using the 'loopup table' join from my member's 'email' to his 'membership' status in each 'club' -- while allowing nulls, so I can still generate the checkboxes for the 'clubs' of which he is NOT (yet) a member?? Using what syntax?

    2. Why does a "nested loop" fail (i.e., per the code below, SELECTing for 'membership' during each iteration of my 'clubs' array loop)?

    Result -- it generates one of the checkboxes (appropriately checked/not checked), then this error: "Warning: mysql_fetch_array(): 2 is not a valid MySQL result resource in [path]"?

    Is it an "empty array" problem? And, furthermore, do I NEED to use 'mysql_fetch_array' to get at the 'mID' (from the last SELECT) when I know there's only one 'row'?

    PHP Code:
    $email $_REQUEST['email'];

    [do 
    the database connection stuffthen]

    $sql "SELECT cID, cName " .
           
    "FROM clubs " .
           
    "ORDER BY cName";

    $rs = @mysql_unbuffered_query($sql);
    if (!
    $rs) {
      die(
    $query_err '<BR />' mysql_error());
    }

    // here's where it apparently FAILS, after one iteration...
    // again, the "error message" reads like so:
    // "Warning: mysql_fetch_array(): 2 is not a valid MySQL result resource in [path]"

    while ( $clubsAvail mysql_fetch_array($rs) ) {
      
    $cID $clubsAvail['cID'];
      
    $cName $clubsAvail['cName'];

    // show ONLY the clubs that have been given a name

      
    if ($cName != '') {

    // alternate the row colors for scannable layout

        
    if ($toggle_row_color == 0) {
          
    $row_color='honeydew';
          
    $toggle_row_color 1;
        } else {
          
    $row_color='white';
          
    $toggle_row_color 0;
        }

    // produce a result set ONLY if Bill is a member of THIS club

        
    $sql2 "SELECT email, memberships.mID, cID " .
                
    "FROM members, memberships " .
                
    "WHERE email = $email " .
                
    "AND members.mID = memberships.mID " .
                
    "AND cID = $cID";

        
    $rs2 = @mysql_unbuffered_query($sql2);
        if (!
    $rs2) {
          die( 
    $query_err '<br />' mysql_error() );
        }

        while ( 
    $member = @mysql_fetch_array($rs2) ) {
          
    $mID $member['mID'];
          
    // echo($mID);

          
    if ($mID != '') {
            
    $check_checkbox 'checked';
          }
        }

    // spit out the checkboxes, marking 'checked' IF $check_checkbox variable contains a value
    // of course, the <TABLE> tags are in the HTML surrounding this code!

        
    echo( "<TR STYLE='background: $row_color' width='100%' valign='top'>" .
              
    "<TD STYLE='padding: 8px 8px 8px 12px; border-width: 0px'>" .
              
    "<P><input type='checkbox' name='clubs[]' value='$cID' " .
              
    "$check_checkbox />&nbsp;<SPAN STYLE='font-size: 14pt'>$cName</a></SPAN><BR /></P></TD></TR>" );
      }


    Thanks so much for your insight!

    Barry Sindlinger
    "Thousands of Pianists Leave Music School &
    Enter Their Careers Every Year -- WITHOUT
    These Crucial Skills...! Learn HOW you can
    ESCAPE their fate, at...
    http://www.compleatpianist.com "
    Last edited by polyphonic; Jun 30, 2003 at 10:57.

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT *
      FROM clubs                  c
      LEFT OUTER JOIN memberships ms ON ms.cID =  c.cID
      LEFT OUTER JOIN members     m  ON  m.mID = ms.mID
    Then all you have to do is in your while loop test for a non-null member ID. If so, then set $checked = 'checked'.

    e.g.
    PHP Code:
    while( $rows )
     if( 
    $row'mID' ] )
       
    $checked 'checked';
     else
       
    $checked ''

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Sacramento
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this looks ALMOST right, BUT...

    1. This makes all my checkboxes 'checked';
    2. Nothing in your code has filtered the 'members.email' field on my actual member's '$email';
    3. And I've tried adding 'WHERE m.email = $email' -- but it doesn't work! 8-(

    What am I missing?!

    Quote Originally Posted by MattR
    Code:
    SELECT *
      FROM clubs                  c
      LEFT OUTER JOIN memberships ms ON ms.cID =  c.cID
      LEFT OUTER JOIN members     m  ON  m.mID = ms.mID
    Then all you have to do is in your while loop test for a non-null member ID. If so, then set $checked = 'checked'.

    e.g.
    PHP Code:
    while( $rows )
     if( 
    $row'mID' ] )
       
    $checked 'checked';
     else
       
    $checked ''

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Sacramento
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    May I solve my own problem?

    I revised MattR's query to:

    Code:
    "SELECT c.cID, c.cName, c.adminBlurb, ms.mID FROM clubs c LEFT OUTER JOIN memberships ms ON c.cID = ms.cID RIGHT OUTER JOIN members m ON m.mID = ms.mID WHERE m.email = '$email'";  [corrected field names on 7/1/2003]
    Voila! It worked.

    Any suggested improvements??

    Thanks, Matt! Barry
    Last edited by polyphonic; Jul 1, 2003 at 10:29.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, looks good.


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
  •