SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
-
Jun 7, 2007, 10:37 #1
- 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!
-
Jun 7, 2007, 10:52 #2
- 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
Edit:
The WHERE condition you need is probably:
WHERE player1=7 OR player2=7
But again, I've not tested this.
-
Jun 7, 2007, 10:54 #3
- 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.
-
Jun 7, 2007, 11:05 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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)
-
Jun 7, 2007, 11:07 #5
- 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.
-
Jun 7, 2007, 11:15 #6
- Join Date
- Sep 2006
- Posts
- 53
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jun 7, 2007, 11:17 #7
-
Jun 7, 2007, 11:17 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
Code:SELECT CASE player1 WHEN 7 THEN player2 ELSE player1 END
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
-
Jun 7, 2007, 11:21 #9
-
Jun 7, 2007, 11:32 #10
- 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!
-
Jun 7, 2007, 11:34 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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?"
-
Jun 7, 2007, 11:38 #12
- 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!
-
Jun 7, 2007, 11:40 #13
- Join Date
- Sep 2006
- Location
- Fairbanks, AK
- Posts
- 1,621
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
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'];
-
Jun 7, 2007, 14:04 #14
- 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!
-
Jun 7, 2007, 15:11 #15
I'll save this thread.
my mobile portal
ghiris.ro
Bookmarks