SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Linking two tables

    Ok, hey I have:

    Users table:

    UserID, Rank, and other user details

    Platoon_members:

    UserID, and other details.




    I have this statement:
    Code:
    $query = "SELECT * FROM `platoon_members` WHERE `platoon_id` = '{$row[id]}' ORDER BY `rank` ASC ;";
    But I need to ORDER BY the rank feild in the users table.
    There is no Rank Field in the platoon_members table.

    if you dont understand me, ill explain in more detail
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How's this?

    Code sql:
    SELECT *
      FROM platoon_members m
    INNER
      JOIN users u
        ON u.UserID = m.UserID
     WHERE m.platoon_id = {$row['id']}
    ORDER
        BY u.rank ASC;

  3. #3
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to check, is this right

    Code:
     $query = "SELECT * FROM `platoon_members` m INNER JOIN 'users' u ON u.UserID = m.UserID WHERE `m.platoon_id` = '{$row[id]}' ORDER BY `u.rank` ASC ";
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  4. #4
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone?
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, that's not right, ben, you messed it up

    try SJH's query without adding any punctuation marks to it or flattening it out into one long unreadable line

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK tried that:

    Code sql:
    			$query = 
    "
    SELECT * FROM platoon_members m
    INNER
    JOIN users u
    ON u.UserID = m.UserID
    WHERE m.platoon_id = {$row['id']}
    ORDER
    BY u.rank ASC";



    And it produces:

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 62

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 66
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  7. #7
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, I also tried it without the " " and it gives me

    Parse error: syntax error, unexpected T_STRING in /home/benanddale/domains/nsgclan.com/public_html/includes/Regiment.php on line 49
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    step 1 - develop a working query using something like phpmyadmin, mysql workbench, heidisql, or another similar tool. Don't stop until you have a query that produces the desired result set.

    step 2 - using the working sql query you created, create a string in php, substituting any variables needed. echo the text to the screen.

    step 3 - make sure it matches the working sql query you created

    step 4 - now you can actually query the database from within php.

  9. #9
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Im a nub at mysql my mate done the coding, but hes away for a while. Can I not just edit the code somehow to make this work?
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  10. #10
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone?? Please..
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    did you test the query outside of php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Erm.. I dunno.


    Is this code correct though?:

    $query =
    SELECT * FROM platoon_members m
    INNER
    JOIN users u
    ON u.UserID = m.UserID
    WHERE m.platoon_id = {$row['id']}
    ORDER
    BY u.rank ASC;
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    except for the php parts, it looks okay

    the sql statement that you want to test starts with SELECT and has an actual id number instead of {$row['id']}

    what are you going to test it in? phpmyadmin?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah.
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  15. #15
    SitePoint Enthusiast Ben Wilkins's Avatar
    Join Date
    Mar 2006
    Location
    England, United Kingdom
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally figured out the problem.

    The userid field was called uid in platoon_members not UserID
    NSGclan.com | RECRUITING NOW! | Multi-gaming clan

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •