Complicated MYSQL select issue, please help

[shreder]

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!

[kromey]

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:

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.

[byron3@earthlink]

$data = mysql_fetch_array( $results );
foreach( $data as $K => $V ) {
    if ( $V != $MyID ) { return $K };
}
retrun False; 

[r937]

kromey, yes, you need that WHERE condition

you can also write it like this:

where 7 in (player1,player2)

[kromey]

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.

[shreder]

kromey, yes, you need that WHERE condition

you can also write it like this:

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!

[kromey]

Yes, you need to add that to mine:

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.

[r937]

thanks for the kind words, kromey

i'm going to guess that shreder does not want this --

SELECT CASE player1 WHEN 7 THEN 'player1' ELSE 'player2' END

but rather this --

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

[kromey]

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.

[shreder]

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!

[r937]

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?"

[shreder]

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!

[kromey]

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:

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:

$query = /*the above query*/;
$res = mysql_query($query);
$row = mysql_fetch_assoc($res); //could also use mysql_fetch_array if desired
echo $row['opponent'];

[shreder]

Thank you!
With your help i finally managed to get it working!

[Ernie1]

I'll save this thread.