Programming Forums > Web Development and Design > PHP

tAK · Mar 29th, 2007, 7:19 PM

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.

(Toggle Plain Text)

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

Styx · Mar 29th, 2007, 8:05 PM

hm, you can try something like this:

(Toggle Plain Text)

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

RGCaldas · Mar 29th, 2007, 8:08 PM

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/>';.
...

DaWei · Mar 29th, 2007, 8:10 PM

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

tAK · Mar 29th, 2007, 8:44 PM

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:

(Toggle Plain Text)

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

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

thanks agian to everyone.

/tAK

Styx · Mar 29th, 2007, 11:47 PM

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

but I'm glad it's working now!

tAK · Mar 30th, 2007, 12:01 AM

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

, 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.

Mar 29th, 2007, 7:19 PM	#1
tAK Programmer Join Date: Mar 2007 Posts: 33 Rep Power: 0	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. (Toggle Plain Text) <?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(); ?> <?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

Mar 29th, 2007, 8:05 PM	#2
Styx Programmer Join Date: Mar 2007 Posts: 39 Rep Power: 0	hm, you can try something like this: (Toggle Plain Text) 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 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

Mar 29th, 2007, 8:10 PM	#4
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	Two tables is not the same thing as two databases. What exactly did you mean? __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Mar 29th, 2007, 8:44 PM	#5
tAK Programmer Join Date: Mar 2007 Posts: 33 Rep Power: 0	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: (Toggle Plain Text) select g.GameName FROM members m,games g WHERE Username='uname' AND m.game1 = g.gameID; 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 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 thanks agian to everyone. /tAK Last edited by tAK; Mar 29th, 2007 at 9:15 PM. Reason: SOLVED !!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2007, 8:08 PM	#3
RGCaldas Newbie Join Date: Nov 2006 Posts: 5 Rep Power: 0	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/>';. ...

Mar 29th, 2007, 11:47 PM	#6
Styx Programmer Join Date: Mar 2007 Posts: 39 Rep Power: 0	hehe, I was wondering about that x_x sorry, I put it up kind of quick but I'm glad it's working now!

Mar 30th, 2007, 12:01 AM	#7
tAK Programmer Join Date: Mar 2007 Posts: 33 Rep Power: 0	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 , 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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Comparing characters from words	ReggaetonKing	Java	12	Apr 22nd, 2006 5:32 PM
Comparing Tuples and Lists	Dietrich	Python	4	Feb 5th, 2006 2:34 PM
Comparing doubles issue (I think...). Help!	glopal	C++	2	May 5th, 2005 10:48 AM
Comparing strings....	balltheheed	Java	3	Apr 8th, 2005 3:26 AM
Comparing Numbers	Dark Flare Knight	Java	4	Apr 5th, 2005 7:19 PM

Programming Forums > Web Development and Design > PHP

Comparing PrimaryKey to get a Value