Programming Forums - Comparing PrimaryKey to get a Value

Programming Forums (http://www.programmingforums.org/forumindex.php)

- PHP (http://www.programmingforums.org/forum29.html)

- - Comparing PrimaryKey to get a Value (http://www.programmingforums.org/showthread.php?t=12907)

Comparing PrimaryKey to get a Value

Hi guys,

I have this code that i have written, it reads values from a table called members, it gets these fields:
Username,Game1,Game2,Game3,Game4,Game5

the Game1-5 fields contain a only a number.
in another table, called games, i have 2 fields:
GameID (primary key), GameName
1, Solitaire
2, MahJong
3, Hearts
(^some values for the sake of demonstration)

At the moment, the script prints:
Game1 = 1
Game2 = 3

what i want it to do, is read the second database, and echo the GameName which matches that GameID:
Game1 = Solitaire
Game2 = Hearts

Here is the current php code: can someone please help me with some pointers on logic for this?. i can do single database read/writes, but i am lost on cross referencing data.

<?php session_start(); ?>

<?php

        error_reporting(E_ALL);

        $mysqli = new mysqli('localhost','root','password');

        $mysqli->select_db('GameDB');

        $Username = safe_output($_SESSION['username']);

        $Password = $_SESSION['password'];

        $ViewMyGames = $mysqli->query("SELECT Username,Game1,Game2,Game3,Game4,Game5 FROM members WHERE (Username='$Username' AND Password='$Password')");

        If (!$ViewMyGames) {

                Print "Unable to retrieve games list";

        } Else {

                While ($row = $ViewMyGames->fetch_assoc()) {

                        print "Games for ".$row['Username'] ." are:".'<br/>';

                        print "Game 1 = ".$row['Game1'] .'<br/>';

                        print "Game 2 = ".$row['Game2'] .'<br/>';

                        print "Game 3 = ".$row['Game3'] .'<br/>';

                        print "Game 4 = ".$row['Game4'] .'<br/>';

                        print "Game 5 = ".$row['Game5'] .'<br/>';

                }

        }

        $ViewMyGames->close();

?>

If you need any more info, just ask.
Regards
/tAK

hm, you can try something like this:

SELECT g.GameName FROM members m, games g

  WHERE m.Username = '$Username'

  AND m.Password = '$Password'

  AND m.Game1 = g.GameID

  AND m.Game2 = g.GameID

  AND m.Game3 = g.GameID

  AND m.Game4 = g.GameID

  AND m.Game5 = g.GameID

The problem is that the tables are created correctly there should be 3 tables, a member, a games, and another with games_member. Then it would be easier to do.

But from what you have you can make a second query collecting the names and then use it like this, fetching the results with mysql_results:

print "Games for ".mysql_results($results,id,'Username') ." are:".'<br/>';
print "Game 1 = ".mysql_results($results2,mysql_results($results,id,'Game1'),'GameName') .'<br/>';.
...

Two tables is not the same thing as two databases. What exactly did you mean?

ahh.. i see what he means..
i should have 2 tables (games and members), and a third table that references the fields in the other two.
that way, i can just query the third table for exactly what i wanted in the first place? is that correct?
I also see the idea that Styx is suggesting.. in that i can (or should be able to) do it all in the query. i may have indeed been aproaching this the wrong way, by attempting to collect all the data via multiple queries, then use PHP to sort it out.. when i could possibly have collected it all directly as i needed it..
will go back and keep proding at it, see what i come up with..

*EDIT*
Styx.. thanks for the info, you were correct !!
if i run:

select g.GameName FROM members m,games g

WHERE Username='uname'

AND m.game1 = g.gameID;

I get returned exactly what i want, the username AND game name.. unfortunately as soon as i add another AND statement, it fails and returns an empty character set.. so appending:
AND m.game2 = g.gameID
it fails.. any ideas?

**EDIT**
Consider the case closed guys... THANKYOU to all :D

i had a look over your info styx, noted that you were using AND statements, should have been OR, so this is the query that worked for me:
SELECT m.Username,g.GameName FROM members m,games g
WHERE Username='uname'
AND (m.game1 = g.gameID
OR m.game2 = g.gameID
OR m.game3 = g.gameID
OR m.game4 = g.gameID
OR m.game5 = g.gameID);
need to be 1 AND, and then the OR statements in brackets.. as i said, i poked and proded until i worked it all out :D

thanks agian to everyone.

/tAK

hehe, I was wondering about that x_x sorry, I put it up kind of quick

but I'm glad it's working now! ;)

Yeah.. its great, i actually went back through my code so far, and trimmed out a lot of useless crud that was over-working php (replacing it with better formatted SQL statements to get the job done).. things are like 1-2 seconds faster on load times now :D, i spose thats what happens when things get done the right way.

I reckon ill be asking more questions again soon. but thanks again for the help on this one.