SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complicated MYSQL select issue, please help

    Hello.

    My id is 7.
    I have a row with 2 fields, one is named player1 and the other is player2
    One of those fields has the my id value in it (7).
    Now i want to get the column name of the one that does not contain 7 as its value, either player1 or player2...

    How can i do that?

    Thank you!

  2. #2
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Best to ask in the MySQL forum. I'll take a stab (untested), but if it doesn't work for you you'll have to ask there:
    Code sql:
    SELECT CASE player1 WHEN 7 THEN 'player1' ELSE 'player2' END
    FROM table1
    You'll probably need a WHERE condition on this, too. Again, ask in the MySQL forum if this doesn't work for you.

    Edit:


    The WHERE condition you need is probably:
    WHERE player1=7 OR player2=7
    But again, I've not tested this.
    PHP questions? RTFM
    MySQL questions? RTFM

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $data mysql_fetch_array$results );
    foreach( 
    $data as $K => $V ) {
        if ( 
    $V != $MyID ) { return $K };
    }
    retrun False
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    kromey, yes, you need that WHERE condition

    you can also write it like this:
    Code:
    where 7 in (player1,player2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooh, but with the addition of the WHERE clause my statement actually works? ROCK ON!!

    I have you, Rudy, to thank for teaching me about CASE. Well, really, teaching me most everything I know about SQL (beyond the basic SELECT, INSERT, and DELETE, of course). You and the MySQL manual.
    PHP questions? RTFM
    MySQL questions? RTFM

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    kromey, yes, you need that WHERE condition

    you can also write it like this:
    Code:
    where 7 in (player1,player2)
    Is that the complete query or do i need to add that to kromey's?
    What will the results be for that query and what would be the complete query?

    Thank you!

  7. #7
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you need to add that to mine:
    Code sql:
    SELECT CASE player1 WHEN 7 THEN 'player1' ELSE 'player2' END
    FROM table1
    WHERE 7 IN (player1,player2)
    The query will return the string "player1" when 7 is in the player1 field, and "player2" otherwise.
    PHP questions? RTFM
    MySQL questions? RTFM

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words, kromey

    i'm going to guess that shreder does not want this --
    Code:
    SELECT CASE player1 WHEN 7 THEN 'player1' ELSE 'player2' END
    but rather this --
    Code:
    SELECT CASE player1 WHEN 7 THEN player2 ELSE player1 END
    can you see why?

    first, it returns an actual player number (instead of the name of the column), and second, it returns the one that does not contain 7, not the one that does

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

  9. #9
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, wouldn't that though return the value in the field? My understanding was that the OP wanted the name of the field, since he already knows the value.
    PHP questions? RTFM
    MySQL questions? RTFM

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you all!
    So will that work:
    $toid = mysql_fetch_array(mysql_query("SELECT CASE player1 WHEN $id THEN player2 ELSE player1 END;"));
    And what should i do to retrieve the value?
    $toid[0] ??

    I want to get the VALUE of the column that does not contain my own $id(7)...

    Thank you!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    shreder said he (she?) wanted the column name, but i have a rather strong hunch that there's more going on here

    imagine a table with rows containing two players, and you are player 7, and you want to run a query to pull out all the games where you are one of the two players

    i'm guessing what is desired is "who was the other player" and not "was i player1 or player2?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, you're almost there
    I have a row with 2 players, player1 and player2.
    On of them is me($id) and i want to get the other player's value, not mine...

    How would i do that?

    Thanx!

  13. #13
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by shreder View Post
    Thank you all!
    So will that work:
    $toid = mysql_fetch_array(mysql_query("SELECT CASE player1 WHEN $id THEN player2 ELSE player1 END;"));
    And what should i do to retrieve the value?
    $toid[0] ??

    I want to get the VALUE of the column that does not contain my own $id(7)...

    Thank you!
    Ah, so you want the other player (exactly as Rudy suspected in his last post - isn't he brilliant?).

    This then is what you'd want:
    Code sql:
    SELECT CASE player1 WHEN 7 THEN player2 ELSE player1 END AS opponent
    FROM table1
    WHERE 7 IN (player1,player2)
    Then in PHP you can retrieve this value via:
    Code php:
    $query = /*the above query*/;
    $res = mysql_query($query);
    $row = mysql_fetch_assoc($res); //could also use mysql_fetch_array if desired
    echo $row['opponent'];
    PHP questions? RTFM
    MySQL questions? RTFM

  14. #14
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!
    With your help i finally managed to get it working!

  15. #15
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll save this thread.

    my mobile portal
    ghiris.ro


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
  •